Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management

2. Excel 2010 Level 2 Unit 1Advanced Formatting, Formulas, and Data Management Chapter 4Summarizing and Consolidating Data

3. Summarizing and Consolidating Data Quick Links to Presentation Contents • Summarize Data in Multiple Worksheets Using Range Names and 3-D References • Summarize Data by Linking to Ranges in Other Worksheets or Workbooks • Summarize Data Using the Consolidate Feature • CHECKPOINT 1 • Create a PivotTable Report • Create a PivotChart • Summarize Data with Sparklines • CHECKPOINT 2

4. Summarize Data in Multiple Worksheets Using Range Names and 3-D References • A workbook that has been organized with data in separate worksheets can be summarized by creating formulas that reference cells in other worksheets. • A worksheet reference precedes acell reference and is separated from the cell reference with an exclamation point. • A formula that references the same cell in a range that extends over two or more worksheets is often called a 3-D reference.

5. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued • As an alternative, consider using range names to simplify formulas that summarize data in multiple worksheets. • A range name includes the worksheet reference by default; therefore, typing the range name in the formula automatically references the correct worksheet.

6. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued To sum multiple worksheets using range names: • Make the formula cell active. • Type =sum(. • Type the first range name. • Type a comma. • Type the second range name. • Type a comma. • Continue typing range names separated by commas until finished. • Type ). • Press Enter. range names

7. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued To modify a named range reference: • Click the Formulas tab. • Click the Name Manager button in the Defined Names group. • Click the range name to be modified. • Click the Edit button. continues on next slide… Edit button

8. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued • Click in the Refers to text box or click the collapse button. • Modify the range address(es) as required. • Click OK. • Click Close. Refers to text box

9. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued • A disadvantage to using range names applies when several worksheets need to be summarized, since you have to create the range name reference in each individual worksheet. • If several worksheets need to be summed, a more efficient method is to use a 3-D reference.

10. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued To use a 3-D reference formula: • Make the desired cell in the worksheet active. • Type =sum(. • Click the first sheet tab to be included. • Hold down the Shift key. • Click the last sheet tab to be included. • Select the desired range(s). continues on next slide… The three worksheets grouped in the 3-D reference.

11. Summarize Data in Multiple Worksheets Using Range Names and 3-D References…continued • Type ). • Press Enter. 3-D formula created using point-and-click approach.

12. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks • You can summarize data in one workbook by linking to a cell, range, or range name in another worksheet or workbook. • When data is linked, a change made in the source cell (the cell in which the original data is stored) is reflected in any other cell to which the source cell has been linked.

13. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks…continued To create a link to an external reference: • Open the source workbook. • Open the destination workbook. • Arrange the windows as desired. • Make the formula cell active in the destination workbook. • Type =. • Click to activate the source workbook. • Click the source cell. • Press Enter. source cell destination cell

14. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks…continued • Linking to a cell in another workbook incorporates external references and requires that a workbook name reference be added to a formula. For example, linking to cell A3 in a sheet named ProductA in a workbook named Sales would require that you enter =[Sales.xlsx]ProductA!A3 in the formula cell. • Notice the workbook reference is entered first in square brackets. • The workbook in which the external reference is added becomes the destination workbook. • The workbook containing the data that is linked to the destination workbook is called the source workbook.

15. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks…continued • When you link to an external reference, Excel includes the drive and folder names in the path to the source workbook. • If you move the source workbook or change the workbook name, the link will no longer work. security warning message

16. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks…continued To edit a link to an external reference: • Open the destination workbook. • Click the Data tab. • Click the Edit Links button in the Connections group. • Click the link. • At the Edit Links dialog box, click the Change Source button. • Navigate to the drive and/or folder. • Double-click the source workbook file name. • Click the Close button. Edit Links dialog box

17. Summarize Data by Linking to Ranges in Other Worksheets or Workbooks…continued To break a link to an external reference: • Open the destination workbook. • Click the Data tab. • Click the Edit Links button in the Connections group. • Click the link. • At the Edit Links dialog box, click the Break Link button. • At the Microsoft Excel message box, click the Break Links button. • Click the Close button. Break Links button

18. Summarize Data Using the Consolidate Feature To consolidate data: • Make the starting cell active. • Click the Data tab. • Click the Consolidate button in the Data Tools group. • If necessary, change the Function. • Enter the first range in the Reference text box. • Click the Add button. • Enter the next range. • Click the Add button. • Repeat steps 7 to 8 until all ranges have been added. continues on next slide… Reference text box

19. Summarize Data Using the Consolidate Feature…continued • If necessary, select the Top row and/or Left column check boxes. • If necessary, click the Create links to source data check box. • Click OK. Left column check box

20. CHECKPOINT 1 • A formula that references the same cell over two or more worksheets is often called this. • 3-D reference • 3-D worksheet • 3-D formula • 3-D cell • Using this key while clicking a sheet tab selects all worksheets from the first sheet tab through to the last. • Alt • Shift • Ctrl • Space bar Answer Answer Next Question Next Question • The Name Manager button is located in this tab. • Formulas • Home • Data • Insert • The Consolidate button is located in this tab. • Formulas • Home • Data • Insert Answer Answer Next Question Next Slide

21. Create a PivotTable Report • A PivotTable is an interactive table that organizes and summarizes data based on category labels you designate from row and column headings. • A numeric column you select is then grouped by the row and column category and the data summarized using a function such as Sum, Average, or Count.

22. Create a PivotTable Report…continued • Before creating a PivotTable, examine the source data and determine the following elements before you begin: • Which row and column headings will define how to group the data? • Which numeric field contains the values that should be grouped? • Which summary function will be applied to the values? • How do you want the layout of the table to be structured? • Do you want the ability to filter the report as a whole as well as by columns or rows? • Do you want the PivotTable to appearbeside the source data or in a new sheet? • How many reports do you want to extract from the PivotTable by filtering fields?

23. Create a PivotTable Report…continued To create a PivotTable: • Select the source range. • Click the Insert tab. • Click the PivotTable button in the Tables group. continues on next slide… PivotTable button

24. Create a PivotTable Report…continued • At the Create PivotTable dialog box, click OK. continues on next slide… Create PivotTable dialog box

25. Create a PivotTable Report…continued • Add fields as needed, using the PivotTable Field List pane. • Modify and/or format as required. PivotTable Field List pane

26. Create a PivotTable Report…continued • When the active cell is positioned inside a PivotTable, the contextual PivotTable Tools Options tab and PivotTable Tools Design tab become available. PivotTable Tools Design tab

27. Create a PivotTable Report…continued • Slicersare a new feature added to Excel 2010 that allow you to filter a PivotTable report or PivotChart without opening the Filter drop-down list. • When Slicers are added to a PivotTable or PivotChart, a Slicer pane containing all of the unique values for the specified field is added to the window.

28. Create a PivotTable Report…continued To add a slicer to a PivotTable report: • Make any cell with the PivotTable active. • Click the PivotTable Tools Options tab. • Click the Insert Slicer button in the Sort & Filter group. continues on next slide… Insert Slicer button

29. Create a PivotTable Report…continued • At the Insert Slicers dialog box, click the check box for the desired field. • Click OK. Insert Slicers dialog box

30. Create a PivotTable Report…continued To change the PivotTable summary function: • Make the values in the field cell active. • Click the PivotTable Tools Options tab. • Click the Field Settings button in the Active Field group. continues on next slide… Field Settings button

31. Create a PivotTable Report…continued • At the Value Field Settings dialog box, click the desired function. • Click OK. Value Field Settings dialog box

32. Create a PivotChart • A PivotChart displays the data from a PivotTable in chart form. • As with a PivotTable, you can filter the data to examine various scenarios between categories. • Excel displays the PivotChart Filter Pane when a PivotChart is active so that you can filter the data as needed.

33. Create a PivotChart…continued To create a PivotChart from a PivotTable: • Make a cell active within the PivotTable. • Click the PivotTable Tools Options tab. • Click the PivotChart button in the Tools group. continues on next slide… PivotChart button

34. Create a PivotChart…continued • At the Insert Chart dialog box, select the desired chart type. • Click OK. Insert Chart dialog box

35. Create a PivotChart…continued • Before you begin creating a PivotChart from scratch, examine the source data and determine the following elements: • Which row or column heading contains the labels that you want to display along the x axis? • Which row or column heading contains the labels that you want to display as legend fields? • Which numeric field contains the values that you want to graph in the chart?

36. Create a PivotChart…continued To create a PivotChart without an existing PivotTable: • Select the range containing the data for the chart. • Click the Insert tab. • Click the down-pointing arrow on the PivotTable button. • Click the PivotChart option. continues on next slide… PivotChart option

37. Create a PivotChart…continued • Click OK. • Add fields as needed in the PivotTable Field List pane to build the chart. • Modify and/or format as required. PivotTable Field List pane

38. Create a PivotChart…continued

39. Summarize Data with Sparklines • Sparklines are a new feature added to Excel 2010. • Sparklinesare miniature charts embedded into the background of a cell. The entire chart exists in a single cell. Line or Column Sparklines Win/Loss Sparklines

40. Summarize Data with Sparklines…continued To create Sparklines: • Select the empty range in which to insert Sparklines. • Click the Insert tab. • Click the Line, Column, or Win/Loss type in the Sparklines group. continues on next slide… Sparklines group

41. Summarize Data with Sparklines…continued • At the Create Sparklines dialog box, type the data range address, or drag to select the data range in the Data Range text box. • Click OK. Create Sparklines dialog box

42. Summarize Data with Sparklines…continued • Activate any Sparkline cell and the Sparkline Tools Design tab becomes visible. Sparkline Tools Design tab

43. Summarize Data with Sparklines…continued To customize Sparklines: • Click in any Sparklines cell. • Click the Sparkline Tools Design tab. • Change the chart type, show/hide points or markers, change the chart style, color, or marker color. Show group

44. CHECKPOINT 2 • This interactive table organizes and summarizes data based on category labels. • PivotChart • PivotTable • PivotData • PivotLabel • This new feature allows you to filter a PivotTable report or PivotChart. • Sparklines • Choppers • Dicers • Slicers Answer Answer Next Question Next Question • The PivotTable button is located in this tab. • Formulas • Data • Insert • Home • These miniature charts are embedded into the background of a cell. • Sparklines • Choppers • Dicers • Slicers Answer Answer Next Question Next Slide

45. Summarizing and Consolidating Data Summary of Presentation Concepts • Summarize data by creating formulas with range names that reference other worksheets • Modify the range assigned to a range name • Summarize data by creating 3-D formulas • Create formulas that link to cells in other worksheets or workbooks • Edit a link to a source workbook • Break a link to an external reference • Use the Consolidate feature to summarize data in multiple worksheets • Create, edit, and format a PivotTable • Filter a PivotTable using Slicers • Create and format a PivotChart • Create and format Sparklines