Excel 2013 Level 1 Unit 2 Enhancing the Display of Worksheets

2. Excel 2013Level 1 Unit 2Enhancing the Display of Worksheets Chapter 7Creating Charts and Inserting Formulas

3. Creating Charts and Inserting Formulas • Create a Chart • Change the Chart Design • CHECKPOINT 1 • Change the Chart Formatting • Delete a Chart • Write Formulas with Financial Functions • Write a Formula with the IF Logical Function • Write IF Formulas Containing Text • CHECKPOINT 2 Quick Links to Presentation Contents

4. Create a Chart To create a chart: • Select cells. • Click INSERT tab. • Click desired chart button. • Click desired chart style at drop-down gallery. Charts group chart styles

5. Create a Chart - continued continues on next slide…

6. Create a Chart - continued

7. Create a Chart - continued To create a recommended chart: • Select cells. • Click INSERT tab. • Click Recommended Charts button. • Click OK at Insert Chart dialog box. OR • Select cells. • Press Alt + F1. Insert Chart dialog box

8. Create a Chart - continued • When you create a chart, the chart is inserted in the same worksheet as the selected cells. chart

9. Create a Chart - continued To resize a chart: • Select chart. • Position mouse pointer on sizing handles for middle or corner of the border. • Hold down left mouse button. • Drag to desired size. Drag the sizing handle

10. Create a Chart - continued To move a chart: • Select chart. • Position mouse pointer on a border. • Hold down left mouse button. • Drag chart to desired position. Drag the chart

11. Create a Chart - continued • The cells you select to create the chart are linked to it. • If you need to change the data for a chart, edit the data in the desired cell and the corresponding section of the chart will be automatically updated. • If you add data to cells within the range of cells used for the chart, called the source data, the new data will be included in the chart.

12. Create a Chart - continued • When you insert a chart in a worksheet, three buttons display at the right side of the chart border: • Chart Elements • Chart Styles • Chart Filters Chart Elements Chart Styles Chart Filters

13. Create a Chart - continued To print a chart: • Select chart. • Click FILE tab. • Click Print option. • At Print backstage area, click Print button. Print button

14. Change the Chart Design • With options in the CHART TOOLS DESIGN tab, you can add chart elements, change the chart type, specify a different layout or style for the chart, and change the location of the chart so it displays in a separate worksheet. CHART TOOLS DESIGN tab

15. Change the Chart Design - continued To change a chart style at the Change Chart Type dialog box: • Make chart active. • Click CHART TOOLS DESIGN tab. • Click Change Chart Type button. • Click desired chart type. • Click desired chart style. • Click OK. Change Chart Type dialog box

16. Change the Chart Design - continued To switch rows and columns: • Make chart active. • Click CHART TOOLS DESIGN tab. • Click Switch Row/Column button. Switch Row/Column button

17. Change the Chart Design - continued To change the chart layout and style: • Make chart active. • Click Quick Layout button in Chart Layouts group and then click desired layout. • Click More button. • Click desired chart style. Chart Layouts group

18. Change the Chart Design - continued To change the chart location: • Make chart active. • Click CHART TOOLS DESIGN tab. • Click Move Chart button. • Click New Sheet option. • Click OK. New Sheet option

19. Change the Chart Design - continued To change the chart location back to the original sheet: • Make chart active. • Click Move Chart button. • At Move Chart dialog box, click down-pointing arrow at right side of Object inoption. • Select desired sheet. • Click OK. Object in option

20. CHECKPOINT 1 • Create a chart with buttons in the Charts group on this tab. • DATA • PAGE LAYOUT • HOME • INSERT • This tab contains options for changing the chart layout and style. • CHART TOOLS DESIGN • PAGE LAYOUT • INSERT • REVIEW Answer Answer Next Question Next Question • This type of chart shows trends and change over time at even intervals. • column • bar • line • pie • You can use this keyboard shortcut to create a default chart type. • F1 • F4 • F8 • F11 Answer Answer Next Question Next Slide

21. Change the Chart Formatting • Customize the formatting of a chart and chart elements with option on the CHART TOOLS FORMAT tab. CHART TOOLS FORMAT tab

22. Change the Chart Formatting - continued To select a chart element: • Make chart active. • Click CHART TOOLS FORMAT tab. • Click down-pointing arrow at right side of Chart Elements button. • At drop-down list, click desired element. Chart Elements button

23. Change the Chart Formatting - continued To insert a shape: • Make chart active. • Click CHART TOOLS FORMAT tab. • Click More button at right side of shapes. • Click desired shape at drop-down list. • Click or drag to create shape in chart. shape options

24. Change the Chart Formatting - continued To display a task pane: • Select chart or specific chart element. • Click CHART TOOLS FORMAT tab. • Click Format Selection button. task pane

25. Change the Chart Formatting - continued To change chart height and/or width measurements: • Make chart active. • Click CHART TOOLS FORMAT tab. • Insert desired height and/or width with Shape Height and/or Shape Width measurement boxes. Size group

26. Delete a Chart • Delete a chart created in Excel by clicking once in the chart to select it and then pressing the Delete key. • If you move a chart to a different worksheet in the workbook and then delete the chart, the chart is deleted but not the worksheet.

27. Write Formulas with Financial Functions • The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. • The PMT function contains the arguments Rate, Nper, Pv, Fv, and Type. PMT Function Arguments palette

28. Write Formulas with Financial Functions - continued • The FV function calculates the future value of a series of equal payments or an annuity. FVFunction Arguments palette

29. Write a Formula with the IF Logical Function • A question that can be answered with true or false is considered a logical test. • You can use the IF function to create a logical test that performs a particular action if the answer is true (condition met) and another action if the answer is false (condition not met). • For example, an IF function can be used to write a formula that calculates a salesperson’s bonus as 10% if the salesperson sells more than $99,999 worth of product and 0% if he or she does not sell more than $99,999 worth of product. That formula would look like this: =IF(sales>99999,sales*0.1,0).

30. Write a Formula with the IF Logical Function - continued To write an IF function in the Function Arguments palette: • Click FORMULAS tab. • Click Logical button. • Click IF at drop-down list. • Type three arguments in palette. IFFunction Arguments palette

31. Write IF Formulas Containing Text • If you write a formula with an IF function and you want text inserted in a cell rather than a value, you must insert quotation marks around the text. • For example, to write a formula with an IF function that inserts the word PASS in a cell if the average of the new employee quizzes is greater than 79 and inserts the word FAIL if the condition is not met, type =IF(E16>79,"PASS","FAIL").

32. CHECKPOINT 2 • Use this button in the Current Selection group to select a chart element. • Format Selection • Chart Elements • Align • Shape Effects • This function calculates the future value of a series of equal payments or an annuity. • MIN • MAX • PMT • FV Answer Answer Next Question Next Question • To change the chart height and/or width, use options in this group on the CHART TOOLS FORMAT tab. • Size • Arrange • Current Selection • Shape Styles • When writing an IF function, you must insert these around the text you want inserted in a cell. • periods • exclamation points • commas • quotation marks Answer Answer Next Question Next Slide

33. Creating Charts and Inserting Formulas • Create a chart with data in an Excel worksheet • Size, move, edit, format, and delete charts • Print a selected chart and print a worksheet containing a chart • Change a chart location • Insert, move, size, and delete chart elements and shapes • Write formulas with the PMT and FV financial functions • Write formulas with the IF logical function Summary of Presentation Concepts