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

2. Excel 2013 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 a cell 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 formula cell active. • Type =sum(. • Type first range name. • Type a comma. • Type 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 FORMULAS tab. • Click Name Manager button. • Click range name to be modified. • Click Edit button. continues on next slide… Edit button

8. Summarize Data in Multiple Worksheets Using Range Names and 3-D References -continued • Click in Refers to text box or click collapse button. • Modify 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 emerges when several worksheets need to be summarized, since the range name reference must be created 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 desired cell in worksheet active. • Type =sum(. • Click first sheet tab to be included. • Hold down Shift key. • Click last sheet tab to be included. • Select 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 source workbook. • Open destination workbook. • Arrange windows as desired. • Make formula cell active in destination workbook. • Type =. • Click to activate source workbook. • Click 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 destination workbook. • Click DATA tab. • Click Edit Links button. • Click link. • At Edit Links dialog box, click Change Source button. • Navigate to drive and/or folder. • Double-click source workbook file name. • Click Close button. Edit Links dialog box

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

18. Summarize Data Using the Consolidate Feature To consolidate data: • Make starting cell active. • Click DATA tab. • Click Consolidate button. • If necessary, change Function. • Enter first range in Reference text box. • Click Add button. • Enter next range. • Click 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 Top row and/or Left column check boxes. • If necessary, click 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 on this tab. • FORMULAS • HOME • DATA • INSERT • The Consolidate button is located on 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: • Which rows and columns will define how to format and group the data? • Which numeric field contains the values that should be grouped? • Which summary function will be applied to the values? For example, do you want to sum, average, or count? • Do you want to be able to filter the report as a whole, as well as by columns or rows? • Do you want the PivotTable to be beside 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 source range. • Click INSERT tab. • Click PivotTable button. continues on next slide… PivotTable button

24. Create a PivotTable Report - continued • At Create PivotTable dialog box, click OK. • Add fields as needed, using PivotTable Fields task pane. • Modify and/or format as required. PivotTable Fields task pane

25. Create a PivotTable Report - continued • When the active cell is positioned inside a PivotTable, the contextual PIVOTTABLE TOOLS ANALYZE and PIVOTTABLE TOOLS DESIGN tabs become available. PIVOTTABLE TOOLS DESIGN tab

26. Create a PivotTable Report - continued • Slicersallow you to filter a PivotTable report or PivotChart without opening the Filters list box. • 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. • Timelines is a new feature added to Excel 2013 that allows you to group and filter a PivotTable or PivotChart based on specific timeframes. • A date field you select adds a Timeline pane containing a timeline slicer that you can extend or shorten to instantly filter the data by the selected date range.

27. Create a PivotTable Report - continued To add a slicer to a PivotTable report: • Make any cell with PivotTable active. • Click PIVOTTABLE TOOLS ANALYZE tab. • Click Insert Slicer button. continues on next slide… Insert Slicer button

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

29. Create a PivotTable Report - continued To add a timeline to a PivotTable report: • Make any cell within PivotTable active. • Click PIVOTTABLE TOOLS ANALYZE tab. • Click Insert Timeline button. continues on next slide… Insert Timeline button

30. Create a PivotTable Report - continued • At Insert Slicers dialog box, click check box for desired field. • Click OK. • Select desired timeframe in Timeline pane. Timeline pane

31. Create a PivotTable Report - continued To change the PivotTable summary function: • Make values in field cell active. • Click PIVOTTABLE TOOLS ANALYZE tab. • Click Field Settings button. continues on next slide… Field Settings button

32. Create a PivotTable Report - continued • At Value Field Settings dialog box, click desired function. • Click OK. Value Field Settings dialog box

33. 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 Fields task pane when a PivotChart is active so that you can filter the data as needed.

34. Create a PivotChart - continued To create a PivotChart from a PivotTable: • Make a cell active within PivotTable. • Click PIVOTTABLE TOOLS ANALYZE tab. • Click PivotChart button. continues on next slide… PivotChart button

35. Create a PivotChart - continued • At Insert Chart dialog box, select desired chart type. • Click OK. Insert Chart dialog box

36. Create a PivotChart - continued • Before you begin creating a PivotChart from scratch, examine the source data and determine the following elements: • Which fields do you want to display along the x (horizontal) axis? In other words, how do you want to compare data when viewing the chart: by time period (such as months or years), by salesperson names, by department names, or by some other category? • Which fields do you want to display in the legend? In other words, how many data series (bars in a column chart) do you want to view in the chart: one for each region, product, salesperson, department, or some other category? • Which numeric field contains the values that you want to graph in the chart?

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

38. Create a PivotChart - continued • Click OK. • Add fields as needed in PivotChart Fields list pane to build chart. • Modify and/or format as required. PivotChart Fields list pane

39. Summarize Data with Sparklines • Sparklinesare miniature charts that are embedded into the background of a cell. An entire chart exists in a single cell. Line or Column Sparklines Win/Loss Sparklines

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

41. Summarize Data with Sparklines -continued • At Create Sparklines dialog box, type data range address, or drag to select data range in 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 SPARKLINE TOOLS DESIGN tab. • Change chart type, show/hide points or markers, change 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 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 on 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 and Timelines • Create and format a PivotChart • Create and format Sparklines