marquee16 presentation-excel s3
TRANSCRIPT
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
Skills© Paradigm Publishing, Inc. 4
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.
Skills© Paradigm Publishing, Inc. 5
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
Skills© Paradigm Publishing, Inc. 6
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
Skills© Paradigm Publishing, Inc. 7
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
Skills© Paradigm Publishing, Inc. 8
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
Skills© Paradigm Publishing, Inc. 9
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
Skills© Paradigm Publishing, Inc. 10
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
Skills© Paradigm Publishing, Inc. 11
Use Financial Functions…continued
Function Arguments dialog box
Skills© Paradigm Publishing, Inc. 12
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
Skills© Paradigm Publishing, Inc. 13
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
Skills© Paradigm Publishing, Inc. 14
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
Skills© Paradigm Publishing, Inc. 15
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
Skills© Paradigm Publishing, Inc. 16
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
Skills© Paradigm Publishing, Inc. 17
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
Skills© Paradigm Publishing, Inc. 18
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
Skills© Paradigm Publishing, Inc. 19
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
Skills© Paradigm Publishing, Inc. 20
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
Skills© Paradigm Publishing, Inc. 21
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
Skills© Paradigm Publishing, Inc. 22
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
Skills© Paradigm Publishing, Inc. 23
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
Skills© Paradigm Publishing, Inc. 24
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
Skills© Paradigm Publishing, Inc. 25
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
Skills© Paradigm Publishing, Inc. 26
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
Skills© Paradigm Publishing, Inc. 27
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
Skills© Paradigm Publishing, Inc. 28
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
Skills© Paradigm Publishing, Inc. 29
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
Skills© Paradigm Publishing, Inc. 30
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
Skills© Paradigm Publishing, Inc. 31
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
Skills© Paradigm Publishing, Inc. 32
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
Skills© Paradigm Publishing, Inc. 33
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
Skills© Paradigm Publishing, Inc. 34
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
Skills© Paradigm Publishing, Inc. 35
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
Skills© Paradigm Publishing, Inc. 36
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
Skills© Paradigm Publishing, Inc. 37
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
Skills© Paradigm Publishing, Inc. 38
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