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

1. Tutorial 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. Creating an Excel Table • Renaming an Excel table • Default names: Table1, Table2, Table3, etc. • Descriptive names make it easier to identify a table by its content • Formatting an Excel table • Use check boxes in Table Style Options group on Design tab to remove table elements or change table’s formatting

9. 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

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

11. Sorting Multiple Columns Using the Sort Dialog Box

12. 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

13. 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

14. 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

15. Using the Total Row to CalculateSummary Statistics

16. 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 • Click SubTotal on the Data ribbon

17. Inserting Subtotals • 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 • 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

18. Inserting Subtotals

19. 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

20. 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

21. Visual Overview

22. PivotTable and PivotChart

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

24. 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

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

26. 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

27. Adding Fields to a PivotTable

28. 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

29. Rearranging a PivotTable • Add, remove, and rearrange fields to change the PivotTable’s layout

30. Using a Slicer to Filter a PivotTable

31. 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

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

33. Creating a PivotChart • A PivotChart allows you to interactively add, remove, filter, and refresh data fields • A PivotChart is a graphical representation of the data in a PivotTable.

34. PivotChart