1 / 43

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

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

sugar
Download Presentation

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

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related