Chapter 5 : Working with Excel Tables, PivotTables, and PivotCharts

1. Chapter 5: Working with Excel Tables, PivotTables, and PivotCharts

2. Objectives • Sort data and filter data • Summarize an Excel table • Insert subtotals into a range of data • Outline buttons to show or hide details • Create and modify a PivotTable and PivotChart

3. Planning a Structured Range of Data • A collection of similar data can be structured in a range of columns and rows, representing fields and records, respectively • A structured range of data is commonly referred to as a list or table

4. Creating an Effective Structured Range of Data • Enter field names in top row of range • Use short, descriptive field names • Format field names to distinguish header row from data • Enter same kind of data for a field in each record • Separate data (including header row) from other information in the worksheet by at least one blank row and one blank column

5. Planning a Structured Range of Data • Freezing a row or column keeps headings visible as you work with data in a large worksheet

6. Save Time with Excel Table Features • Format quickly using a table style • Add new rows and columns that automatically expand the range • Add a Total row to calculate a summary function (SUM, AVERAGE, COUNT, MIN, MAX) • Enter a formula in a cell that is copied to all other cells in the column • Create formulas that reference cells in a table by using table and column names

7. Creating an Excel Table

8. Sorting Data • Sort data in ascending or descending order • Use the Sort A to Z button or the Sort Z to A button to sort data quickly with one sort field

9. Sorting Data • Use sort dialog box to sort multiple columns • Primary and secondary sort fields • Up to 64 sort fields possible

10. Sorting Using a Custom List • A custom list indicates sequence to order data • Four predefined custom sort lists • Two days-of-the-week custom lists • Two months-of-the-year custom lists • Can also create a custom list to sort records in a sequence you define

11. Filtering Data • Filtering data temporarily hides any records that do not meet specified criteria • After data is filtered, it can be sorted, copied, formatted, charted, and printed

12. Using the Total Row to CalculateSummary Statistics • You can calculate sum, average, count, maximum, and minimum on all columns in a table or on a filtered table in a Total row

13. Creating Subtotals (Introduction) • Subtotals can be created on columnar data • The data must be sorted for subtotals to be created • Column headers must also appear in the data • Subtotal command • Offers many kinds of summary information (counts, sums, averages, minimums, maximums) • Inserts a subtotal row into range for each group of data; adds grand total row below last row of data

14. Inserting Subtotals • Sort data so that records with the same value in a specified field are grouped together before usingSubtotal command • It cannot be used in an Excel table • First convert the Excel table to a range • Click SubTotal on the Data ribbon

15. Inserting Subtotals

16. Using the Subtotal Outline View • Control the level of detail with buttons • Level 3: Most detail • Level 2: Subtotals and grand total, but not individual records • Level 1: Only the grand total

17. Pivot Tables • Interactive table used to group and summarize either a range of data or an Excel table into a concise, tabular format for easier reporting and analysis • Dynamic organization; can be “pivoted” to examine data from various perspectives by rearranging its structure • Best used to analyze data that can be summarized in multiple ways • Pivot tables can be created from lists or external data sources

18. Analyzing Data with PivotTables • Provide ability to “pivot” the table (rearrange, hide, and display different category fields to provide alternative views of the data)

19. Analyzing Data with PivotTables • Summarize data into categories using functions (COUNT, SUM, AVERAGE, MAX, MIN) • Values fields contain summary data • Category fields group the values

20. Creating a PivotTable • Use PivotTable dialog box to select data to analyze and location of the PivotTable report

21. Creating a PivotTable • PivotTable Field List has two sections • Upper field list section displays names of each field; use check boxes to add fields to PivotTable • Lower layout section includes boxes for four areas in which you can place fields

22. Adding Fields to a PivotTable

23. Creating a PivotTable • Apply PivotTable styles by using a preset style or modifying its appearance • Formatting PivotTable values fields • Applying PivotTable styles does not change the numeric formatting

24. Refreshing a PivotTable • You cannot change data directly in the PivotTable • Instead, you must edit the Excel table, and then refresh, or update, the PivotTable to reflect the updated data

25. Grouping PivotTable Items • Grouping items combines dates or numeric items into larger groups so that the PivotTable can include the desired level of summarization

26. Filtering and Slicing a PivotTable • Filters can be applied to a PivotTable • PivotTable filters can be based on: • Field values • Row and column label groupings • PivotTable filters can be removed

27. Filtering and Slicing a PivotTable • Slicer—small window that contains a button for each item in a field • Slicer—helpful when filtering a PivotTable based on multiple tables • Slicers can be customized

28. Filtering and Slicing a PivotTable

29. Creating a Calculated Field • Custom calculation options: • % of Grand Total • % of Column Total • % of Row Total • % of Parent Row Total • Running Total • Rank Smallest to Largest • Rank Largest to Smallest

30. Creating a PivotChart • PivotChart—interactive graphical representation of PivotTable data • Changing the position of a field in the PivotTable or the PivotChart changes the corresponding object as well • Create a PivotChart: • Click in the PivotTable • Click PivotChart in the Tools group on the ANALYZE tab

31. Creating a PivotChart