Marquee16 presentation-excel s3

Skills© Paradigm Publishing, Inc. 1 Skills© Paradigm Publishing, Inc. 1

Marquee Series Microsoft Excel 2016

Skills© Paradigm Publishing, Inc. 2 Skills© Paradigm Publishing, Inc. 2

3.1Create Formulas with Absolute Addresses

3.2 Use Statistical Functions

3.3 Use Date Functions

3.4 Use Financial Functions

CHECKPOINT 1

3.5 Create and Use Range Names

3.6 Use the Logical IF Function

3.7 Create a Column Chart

3.8 Create a Hierarchy Chart

3.9 Create a Line Chart

3.9 Insert and Format Shapes

CHECKPOINT 2

3.10 Modify and Format Charts

3.11 Change Page Layout Options

3.12 Use Page Layout View

3.12 Insert Headers and Footers

CHECKPOINT 3

SECTION 3 SKILLSUsing Functions, Setting Print Options, and Adding Visual Elements

Skills© Paradigm Publishing, Inc. 3

Create Formulas with Absolute AddressesTo make a cell address absolute:1. Position the insertion point

just after the cell address or select the cell address in the Formula bar.

2. Press the F4 function key.OR3. Type a dollar symbol

immediately preceding the column letter and/or row number.

absolute cell address


Create Formulas with Absolute Addresses…continued

Example Action

=$A$12*.01 Neither the column nor the row will change.

=$A12*.01 The column will remain fixed at column A, but the row will change.

=A$12*.01 The column will change, but the row remains fixed at row 12.

=A12*.01 Both the column and row will change.


Use Statistical FunctionsTo use the AVERAGE function:1. Make the cell active.2. Click the AutoSum button

arrow in the Editing group on the Home tab.

3. Click Average from the drop-down list of functions.

4. Type or select the argument range.

5. Press the Enter key or click the Enter button.

AutoSum button arrow


Use Statistical Functions…continuedTo use the MAX, MIN, and COUNT functions:1. Make the cell active.2. Click the AutoSum button

arrow in the Editing group on the Home tab.

3. Click function from drop-down list.

4. Type or select the argument range.

5. Press the Enter key or click the Enter button.

MAX function


Use Statistical Functions…continuedTo select the argument range:1. Position the insertion point over

the top of the range.2. Click and hold down the left

mouse button.3. Drag down to the bottom of the

desired range.4. Release the left mouse button.5. Press the Enter key or click the

Enter button on the Formula bar.

argument range


Use Date FunctionsTo type a date function:1. Make the cell active.2. Type =now(), =today(), or

=time().3. Press the Enter key.

NOW function


Use Date Functions…continuedTo format the cell containing the date and/or time:1. Right-click in the cell.2. Click Format Cells at the shortcut

menu.3. If necessary, click the Number tab

in the Format Cells dialog box.4. If necessary, click the Date

category.5. Scroll down the list of formats in

the Type list box.6. Click format.7. Click OK.

Date category


Use Financial FunctionsTo use the PMT function:1. Make the cell active.2. Click the Formulas tab.3. Click the Financial button in

the Function Library group.4. Click the PMT function from

the drop-down list.5. Enter references in the

Function Arguments dialog box.

6. Click OK.

Financial button


Use Financial Functions…continued

Function Arguments dialog box


CHECKPOINT 11) Use this function to determine

the arithmetic mean.a. PMTb. AVERAGEc. COUNTd. NOW

3) Use this function to calculate a payment for a loan.a. PMTb. AVERAGEc. COUNTd. NOW

2) Use this function to return the current date and time formatted as a date and time.a. PMTb. AVERAGEc. COUNTd. NOW

4) Use this dialog box to find an Excel function.a. Find Functionb. Function Argumentsc. Insert Functiond. Format Cells

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer


Create and Use Range NamesTo create a range name:1. Select the cell or range.2. Click in the Name box.3. Type range name.4. Press the Enter key.

Name box


Create and Use Range Names…continuedTo move the active cell to a named cell or range:1. Click the down-pointing arrow

at the right of the Name box.2. Click range name in drop-down

list.

range name


Create and Use Range Names…continuedTo manage range names:1. Click the Formulas tab.2. Click the Name Manager

button in the Defined Names group.

3. Select range name.4. Edit or delete range name.5. Click Close.

Name Manager dialog box


Create and Use Range Names…continuedTo use a named cell or range in a formula or function:1. Type the formula and range

name in the active cell.2. Press the Enter key.

range name


Using the Logical IF FunctionTo use the IF function:1. Make the cell active.2. Click the Formulas tab.3. Click the Logical button in the

Function Library group.4. Click the IF function.5. Type the formula in the

Logical_test text box.6. Type the value or formula in

the Value_if_true text box.7. Type the value or formula in

the Value_if_false text box.8. Click OK.

Function Arguments dialog box


Create a Column ChartTo create a column chart:1. Select the range.2. Click the Insert tab.3. Click the Column button in

the Charts group.4. Click chart type.5. Move and/or resize the chart

as required.6. Apply design options.

Column button


Create a Column Chart…continuedTo move a chart:1. Make sure the chart is

selected and the Chart Tools Design tab appears.

2. Click the Move Chart button in the Location group.

3. At the Move Chart dialog box, click New sheet.

4. Type the tab label in the New sheet text box.

5. Click OK.

Move Chart button

Move Chart dialog box


Create a Column Chart…continuedTo change the chart layout:1. Make sure the chart is selected

and the Chart Tools Design tab displays.

2. Click layout button in the Chart Layouts group.

To change the chart style:3. Make sure that the chart is

selected and the Chart Tools Design tab displays.

4. Click the More button in the Chart Styles group.

5. Click style.

chart styles


Create a Column Chart…continuedTo create a recommended chart:1. Select the range.2. Click the Insert tab.3. Click the Recommended Charts

button in the Charts group.4. Select chart type in Insert Chart

dialog box.5. Click OK.

Recommended Charts button


Create a Hierarchy ChartTo create a hierarchy chart:1. Click the Insert tab. 2. Click the Insert Hierarchy Chart

button in the Charts group.3. Select chart type.4. With the chart selected, click

the Chart Elements button that displays at the right side of the chart to make desired changes.

Hierarchy Chart button


Create a Line ChartTo create a line chart:1. Select the range.2. Click the Insert tab.3. Click the Line button in the

Charts group.4. Click line type.5. Move and/or resize the

chart as required.6. Apply design options as

desired.

Line button


Insert and Format ShapesTo draw a shape:1. Click the Insert tab.2. Click the Shapes button in the

Illustrations group.3. Click shape.4. Drag to create the shape.5. Move, resize, or format shape as

required.

Shapes button


Insert and Format Shapes…continuedTo add text to a shape:1. Make sure the shape is

selected.2. Begin typing text.OR3. Right-click in the shape.4. Click Edit Text at the

shortcut menu.5. Type the text.6. Click outside the shape.

Insertion point


CHECKPOINT 21) This function returns one of two

values in a cell based on a true or false answer to a question.a. AVERAGEb. COUNTc. MAXd. IF

3) This type of chart shows trends and change over time at even intervals.a. columnb. piec. lined. bar

2) This should include at least a base level and one sub-level.a. columnb. hierarchyc. lined. bar

4) Draw a star using options in this button.a. Shapesb. Online Picturesc. Picturesd. SmartArt

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer


Modify and Format ChartsTo change the shape outline:1. Select the chart.2. Click the Chart Tools Format tab.3. Click the Shape Outline button

in the Shape Styles group.4. Click color box in color palette.

Shape Outline button


Modify and Format Charts…continuedTo change the type of chart:1. Select the chart.2. Click the Chart Tools Design tab.3. Click the Change Chart Type

button in the Type group.4. At the Change Chart Type dialog

box, click type in left pane and then click the variation in right pane.

5. Click OK.

Change Chart Type dialog box


Modify and Format Charts…continuedTo scale a worksheet:1. Click the File tab.2. Click Print.3. Click the scaling gallery in

the Settings category.4. Click Fit Sheet on One Page

at the drop-down list.5. Click the Print button.

Fit Sheet on One Page option


Change Page Layout OptionsTo change the margins:1. Click the Page Layout tab.2. Click the Margins button in the

Page Setup group.3. Click Custom Margins.4. Change the margin options in

the Margins tab of the Page Setup dialog box.

5. Click OK.

Margins tab


Change Page Layout Options…continuedTo center a worksheet on the page:1. Click the Page Layout tab.2. Click the Margins button in

the Page Setup group.3. Click Custom Margins.4. Change the center options in

the Margins tab of the Page Setup dialog box.

5. Click OK. Center on page section


Change Page Layout Options…continuedTo change the orientation:1. Click the Page Layout tab.2. Click the Orientation button

in the Page Setup group.3. Click orientation.

Orientation button


Use Page Layout ViewTo use the Page Layout view:1. Click the Page Layout button at the right side of the

Status bar near the Zoom slider bar.

Page Layout button


Insert Headers and FootersTo insert a header or footer:1. Switch to Page Layout view.2. Click Click to add header or Click to add footer.3. Insert the header and footer elements and/or type text in the

left, center, or right box.4. Click in the worksheet area to end the header or footer editing.

Header box


Insert Headers and Footers…continuedTo insert the date in a header or footer:1. Switch to Page Layout view.2. Click Click to add header or Click to add footer.3. Click the Current Date button in the Header & Footer Elements

group on the Design tab.4. Click in the worksheet area to end the header or footer editing.

Current Date button


Insert Headers and Footers…continuedTo insert the file name in a header or footer:1. Switch to Page Layout view.2. Click Click to add header or Click to add footer.3. Click the File Name button in the Header & Footer Elements

group on the Design tab.4. Click in the worksheet area to end the header or footer

editing.

file name code


Insert Headers and Footers…continuedTo change the header or footer margins:1. Click the Page Layout tab.2. Click the Margins button

in the Page Setup group.3. Click Custom Margins.4. Change the margin

options on the Margins tab of the Page Setup dialog box.

5. Click OK.header & footer margins


CHECKPOINT 31) This is the name of the process to

fit a worksheet to the specified number of pages.a. fittingb. sizingc. scalingd. sharing

3) This view allows you to view the worksheet along with the print settings.a. Normalb. Page Breakc. Print Layoutd. Page Layout

2) This is the default page orientation.a. portraitb. landscapec. normald. standard

4) This is the name for text that prints at the top of each worksheet.a. repeaterb. headerc. footerd. trailer

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer

Technology

Marquee16 presentation-excel s3