SECTION 3 SKILLS Using Functions, Setting Print Options, and Adding Visual Elements

1 / 43

# SECTION 3 SKILLS Using Functions, Setting Print Options, and Adding Visual Elements - PowerPoint PPT Presentation

SECTION 3 SKILLS Using Functions, Setting Print Options, and Adding Visual Elements. 3.1 Create 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 Logical Functions

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## SECTION 3 SKILLS Using Functions, Setting Print Options, and Adding Visual Elements

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

1. SECTION 3 SKILLSUsing Functions, Setting Print Options, and Adding Visual Elements 3.1 Create 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 Logical Functions 3.7 Create a Column Chart 3.8 Create a Pie Chart 3.9Create a Line Chart 3.9 Draw 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

2. Create Formulas with Absolute Addresses To make a cell address absolute: • Position the insertion point just after the cell address or select the cell address in the Formula bar. • Press F4. OR • Type a dollar symbol immediately preceding the column letter and/or row number. absolute cell address

3. Create Formulas with Absolute Addresses…continued

4. Use Statistical Functions To use the AVERAGE function: • Make the desired cell active. • Click the AutoSum button arrow in the Editing group on the HOME tab. • Click Average from the drop-down list of functions. • Type or select the argument range. • Press Enter or click the Enter button. AutoSum button arrow

5. Use Statistical Functions…continued To use the MAX, MIN, and COUNT functions: • Make the desired cell active. • Click the AutoSum button arrow in the Editing group on the HOME tab. • Click the desired function from the drop-down list. • Type or select the argument range. • Press Enter or click the Enter button. MAX function

6. Use Statistical Functions…continued To select the argument range: • Position the insertion point over the top of the desired range. • Hold down the left mouse button. • Drag down to the bottom of the desired range. • Release the left mouse button. • Press Enter or click the Enter button on the Formula bar. argument range

7. Use Date Functions To type a date function: • Make the desired cell active. • Type =NOW(), =TODAY(), or =TIME(). • Press Enter. NOW function

8. Use Date Functions…continued To format the cell containing the date and/or time: • Right-click the cell. • Click Format Cells at the shortcut menu. • If necessary, click the Number tab in the Format Cells dialog box. • If necessary, click the Date category. • Scroll down the list of formats in the Type list box. • Click the desired format. • Click OK. Date category

9. Use Financial Functions To use the PMT function: • Make the desired cell active. • Click the FORMULAS tab. • Click the Financial button in the Function Library group. • Click the PMTfunction from the drop-down list. • Enter references in the Function Arguments dialog box. • Click OK. Financial button

10. Use Financial Functions…continued Function Arguments dialog box

11. Use Financial Functions…continued To find an Excel function: • Make the desired cell active. • Click the Insert Function button on the Formula bar. • Type a description of the formula in the Insert Function dialog box. • Click the Go button. • Select the desired function. • Click OK. Insert Function button Insert Function dialog box

12. CHECKPOINT 1 • Use this function to determine the arithmetic mean. • PMT • AVERAGE • COUNT • NOW • Use this function to calculate a payment for a loan. • PMT • AVERAGE • COUNT • NOW Answer Answer Next Question Next Question • Use this function to return the current date and time formatted as a date and time. • PMT • AVERAGE • COUNT • NOW • Use this dialog box to find an Excel function. • Find Function • Function Arguments • Insert Function • Format Cells Answer Answer Next Question Next Slide

13. Create and Use Range Names To create a range name: • Select the desired cell(s). • Click in the Name box. • Type the desired range name. • Press Enter. Name box

14. Create and Use Range Names…continued To move the active cell to a named cell or range: • Click the down-pointing arrow at the right of the Name box. • Click the range name in the drop-down list. range name

15. Create and Use Range Names…continued To manage range names: • Click the FORMULAS tab. • Click the Name Manager button in the Defined Names group. • Select a range name. • Edit or delete the range name. • Click Close. Name Manager dialog box

16. Create and Use Range Names…continued To use a named cell or range in a formula or function: • Type the formula and range name in the active cell. • Press Enter. range name

17. Use Logical Functions To use the IF function: • Make the desired cell active. • Click the FORMULAS tab. • Click the Logical button in the Function Library group. • Click the IF function. • Type the formula in the Logical_test text box. • Type the value or formula in the Value_if_true text box. • Type the value or formula in the Value_if_falsetext box. • Click OK. Function Arguments dialog box

18. Create a Column Chart To create a column chart: • Select the cells. • Click the INSERT tab. • Click the Column button in the Charts group. • Click the desired chart type. • Move and/or resize the chart as required. • Apply design options as desired. Column button

19. Create a Column Chart…continued To move a chart: • Make sure the chart is selected and the CHART TOOLS DESIGN tab appears. • Click the Move Chart button in the Location group. • At the Move Chart dialog box, click New sheet. • Type the tab label in the New sheet text box. • Click OK. Move Chart button Move Chart dialog box

20. Create a Column Chart…continued To change the chart layout: • Make sure the chart is selected and the CHART TOOLS DESIGN tab displays. • Click the desired layout button in the Chart Layouts group. To change the chart style: • Make sure that the chart is selected and the CHART TOOLS DESIGN tab displays. • Click the More button in the Chart Styles group. • Click the desired style. Chart Layouts group chart styles

21. Create a Column Chart…continued To create a recommended chart: • Select the cells. • Click the INSERT tab. • Click the Recommended Charts button in the Charts group. • Select desired chart type in Insert Chart dialog box. • Click OK. Insert Chart dialog box

22. Create a Pie Chart To create a pie chart: • Select the cells. • Click the INSERT tab. • Click the Pie button in the Charts group. • Click the desired pie type. • Move and/or resize the chart as required. • Apply design options as desired. Pie button

23. Create a Pie Chart…continued To change the label options: • Make sure the chart is selected. • Click the CHART TOOLS DESIGN tab. • Click the Add Chart Elements button in the Chart Layouts group. • Click the More Data Labels Optionsat the side menu. • Click the desired options in the LABEL OPTIONS section of the Format Data Labels task pane. Add Chart Elements button Format Data Labels task pane

24. Create a Pie Chart…continued To change the number of decimal places: • Make sure the chart is selected. • Click the CHART TOOLS DESIGN tab. • Click the Add Chart Elements button in the Chart Layouts group. • Click the More Data Labels Options at the side menu. • Click the desired options in the NUMBER section of the Format Data Labels task pane. NUMBER options

25. Create a Pie Chart…continued To insert Sparklines: • Click the INSERT tab. • Click Line, Column, or Win/Loss in the Sparklines group. • At the Create Sparklines dialog box, select the data range that contains the values upon which you want to base the chart. • Select the cell in which to draw the chart. • Click OK. Sparkline column chart Create Sparklines dialog box

26. Create a Line Chart To create a line chart: • Select the cells. • Click the INSERT tab. • Click the Line button in the Charts group. • Click the desired line type. • Move and/or resize the chart as required. • Apply design options as desired. Line button

27. Draw Shapes To draw a shape: • Click the INSERT tab. • Click the Shapes button in the Illustrations group. • Click the desired shape. • Drag to create the shape. • Move, resize, or format shape as required. Shapes button

28. Draw Shapes…continued To add text to a shape: • Make sure the shape is selected. • Begin typing desired text. OR • Right-click the shape. • Click Edit Text at the shortcut menu. • Type the text. • Click outside the shape. shortcut menu

29. CHECKPOINT 2 • This function returns one of two values in a cell based on a true or false answer to a question. • AVERAGE • COUNT • MAX • IF • This type of chart shows trends and change over time at even intervals. • column • pie • line • bar Answer Answer Next Question Next Question • Use this type of chart to illustrate each data point’s size in proportion to the total of all items in the data source range. • column • pie • line • bar • You can draw a star using options in this button. • Shapes • Online Pictures • Pictures • SmartArt Answer Answer Next Question Next Slide

30. Modify and Format Charts To change the shape outline: • Select the chart. • Click the CHART TOOLS FORMAT tab. • Click the Shape Outline button in the Shape Styles group. • Click the desired color box in the color palette. Shape Outline button

31. Modify and Format Charts…continued To change the type of chart: • Select the chart. • Click the CHART TOOLS DESIGN tab. • Click the Change Chart Type button in the Type group. • At the Change Chart Type dialog box, click the desired type in the left pane and then click the variation in the right pane. • Click OK. Change Chart Type dialog box

32. Modify and Format Charts…continued To scale a worksheet: • Click the FILE tab. • Click Print. • Click the scaling gallery in the Settingscategory. • Click Fit Sheet on One Page at the drop-down list. • Click the Print button. Fit Sheet on One Page option

33. Change Page Layout Options To change the margins: • Click the PAGE LAYOUT tab. • Click the Margins button in the Page Setup group. • Click Custom Margins. • Change the margin options as desired in the Margins tab of the Page Setup dialog box. • Click OK. Margins tab

34. Change Page Layout Options…continued To center a worksheet on the page: • Click the PAGE LAYOUT tab. • Click the Margins button in the Page Setup group. • Click Custom Margins. • Change the center options as desired in the Margins tab of the Page Setup dialog box. • Click OK. Center on page section

35. Change Page Layout Options…continued To change the orientation: • Click the PAGE LAYOUT tab. • Click the Orientation button in the Page Setup group. • Click the desired orientation. Orientation button

36. Change Page Layout Options…continued To print column or row headings on multiple pages: • Click the PAGE LAYOUT tab. • Click the Print Titles button in the Page Setup group. • Select the columns or rows to repeat in the Sheet tab of the Page Setup dialog box. • Click OK. Sheet tab

37. Use Page Layout View To use the Page Layout view: • Click the Page Layout button located at the right side of the Status bar near the Zoom slider bar. Page Layout button

38. Insert Headers and Footers To insert a header or footer: • Switch to Page Layout view. • Click Click to add header or Click to add footer. • Insert the desired header and footer elements and/or type text in the left, center, or right box. • Click in the worksheet area to end the header or footer editing. Header box

39. Insert Headers and Footers…continued To insert the date in a header or footer: • Switch to Page Layout view. • Click Click to add header or Click to add footer. • Click the Current Date button in the Header & Footer Elements group on the HEADER & FOOTER TOOLS DESIGN tab. • Click in the worksheet area to end the header or footer editing. Current Date button

40. Insert Headers and Footers…continued To insert the file name in a header or footer: • Switch to Page Layout view. • Click Click to add header or Click to add footer. • Click the File Name button in the Header & Footer Elements group on the HEADER & FOOTER TOOLS DESIGN tab. • Click in the worksheet area to end the header or footer editing. file name code

41. Insert Headers and Footers…continued To change the header or footer margins: • Click the Page Layout tab. • Click the Margins button in the Page Setup group. • Click Custom Margins. • Change the margin options as desired in the Margins tab of the Page Setup dialog box. • Click OK. Header & Footer margins

42. CHECKPOINT 3 • This is the name of the process to fit a worksheet to the specified number of pages. • fitting • sizing • scaling • sharing • This view allows you to view the worksheet along with the print settings. • Normal • Page Break • Print Layout • Page Layout Answer Answer Next Question Next Question • This is the default orientation. • portrait • landscape • normal • standard • This is the name for text that prints at the top of each worksheet. • repeater • header • footer • trailer Answer Answer Next Question Next Slide