1. Business Advisory Services Excel 2007 Introduction to Pivot Tables

2. What Are Pivot Tables? A pivot table is an interactive worksheet which allows you to summarize and efficiently analyze large amounts of data Simplifies everyday data analysis Allows for complex analysis which would be practically impossible without pivot tables A pivot chart is the graphical representation of a pivot table Data for pivot table analysis must be structured in a database or list format Columns are considered fields and column headings are field names Each row is a record The data set must contain contiguous columns and rows - no blank rows or columns can exist 2

3. Topics Data set Overview Creating a Pivot Table Understanding the PivotTable Tools Ribbons Pivot Table Layout Development Modifying the Pivot Table Additional Pivot Table Capabilities Grouping Pivot Table Data Formatting Pivot Table Reports Advanced Topics 3

4. Data set Overview Natural disasters declared by FEMA since 1953 Downloaded as Excel file from http://www.data.gov/raw/1491# Over 37,000 rows (records) of data, indicating: Key data is easily identified No blank columns or rows Without pivot tables, this data cannot be easily analyzed 4

5. Creating the Pivot Table Name the Data Range Highlight data and enter name for the range in Name Range box (TIP although not required, try to define range with name) In this example the data has been named DisasterData Encompasses all columns and rows included in the data 5

6. Creating the Pivot Table (cont.) Place cursor in Data Range and select Insert > PivotTable QAT toolbar shortcut to create PivotTable is useful here PivotCharts is a chart generated rather than a PivotTable. These are less common in use, so not addressed here. 6

7. Creating the Pivot Table (cont.) The dialog box to the right appears Default settings are to automatically select range of data and place PT in a New Worksheet Change Table/Range to defined name (DisasterData) New Worksheet is usually the desired location Click OK when done 7

8. Creating the Pivot Table (cont.) You are now in PivotTable layout mode Notice that PivotTable Tools menu appears Data fields are now dragged into the report areas (Filter, Row labels, column labels and values) 8

9. Understanding the PivotTable Tools Ribbons Once in the PivotTable Layout mode, the PivotTable Tools Ribbon becomes active The PivotTable Tools Options ribbon provides the tools most often used when working with PTs: The PivotTable Tools Design ribbon provides the ability to present your PT in any desired formatting: 9

10. Pivot Table Layout Development A basic pivottable was created to simply identify how many declared disasters, by type, have occurred. Filter is set by state (select individual state as desired) Incident_Type is the row label and I include Incident_Type in the Value area Excel automatically anticipates I want the count of records. 10

11. Modifying the Pivot Table Once the PivotTable is structured, it is easily modified by moving/changing desired fields. If unsure, best approach is trial and error (very easy to undo). The Values field is controlled through the Values Field Settings dialog box (position cursor in Values area of PT, right mouse click, and select the Value Field Settings from the pop-up menu) Change option as desired Number format revision control 11

12. Modifying the PivotTable (cont.) Value Field Settings controls HOW the number is displayed as well (Show Values as) Many options to choose from. Next page shows two options Count of occurrences % of each Incident Type (of the total occurrences) 12

13. Modifying the PivotTable (cont.) STATE (All) STATE (All) Row Labels Count of INCIDENT_TYPE Row Labels Count of INCIDENT_TYPE Coastal Storm 453 Coastal Storm 1.22% Dam/Levee Break 3 Both views of Dam/Levee Break 0.01% Drought 1,292 Drought 3.49% Earthquake 91 the data are Earthquake 0.25% Fire 2,220 Fire 5.99% Fishing Losses 42 extremely Fishing Losses 0.11% Flood 7,706 insightful Flood 20.79% Freezing 84 Freezing 0.23% Human Cause 6 Human Cause 0.02% Hurricane 7,061 Hurricane 19.05% Mud/Landslide 3 Mud/Landslide 0.01% Other 291 Other 0.79% Severe Ice Storm 1,419 Severe Ice Storm 3.83% Severe Storm(s) 11,413 Severe Storm(s) 30.80% Snow 3,479 Snow 9.39% Terrorist 1 Terrorist 0.00% Tornado 1,322 Tornado 3.57% Toxic Substances 8 Toxic Substances 0.02% Tsunami 1 Tsunami 0.00% Typhoon 115 Typhoon 0.31% Volcano 49 Volcano 0.13% Grand Total 37,059 Grand Total 100.00% 13

14. Modifying the PivotTable (cont.) Default pivottable options are controlled/revised through the PivotTable Options dialog box Set options through these tabs To access, position cursor anywhere in PT, right mouse click, and select the PivotTable Options from the pop-up menu) 14

15. Additional Pivot Table Capabilities Aside from easily obtaining a summary, and different slices, of your data, there are several additional powerful benefits of using pivottables in your data analysis Drill-down capabilities Double-click on any value field amount and a separate sheet appears indicating every record that makes that up (see example on next page) These detail sheets need to be deleted when done using (unless you want them to remain) Summary reports are easily updated when Source Data changes (i.e., DisasterData information) 15

16. Pivot Table Capabilities Drill-Down Analysis STATE (All) Row Labels Count of INCIDENT_TYPE Coastal Storm 453 Dam/Levee Break 3 Drought 1,292 Earthquake 91 Fire 2,220 Fishing Losses 42 Flood 7,706 Freezing 84 Human Cause 6 Hurricane 7,061 Mud/Landslide 3 Other 291 Severe Ice Storm 1,419 Severe Storm(s) 11,413 Snow 3,479 Terrorist 1 Tornado 1,322 Toxic Substances 8 Tsunami 1 Typhoon 115 Volcano 49 Grand Total 37,059 16

17. Pivot Table Capabilities Updating Reports If source data changes (e.g., new records added, existing records changed, etc.), updating reports is very simple: Verify (or update) range name that the Pivot Table is using(1) Go to Pivot Table and Refresh data(2) Manual approach is to position cursor in PT, right mouse-click, and select Refresh Data from the shortcut menu. (1) This process can be automated through Dynamic Range Names. See Advanced Topics slide. (2) This process can be automated so that each time the Sheet containing the pivottable is selected, the PT refreshes. See the Advanced Topics slide. 17

18. Grouping Pivot Table Data Pivot Tables allow for Grouping of data based on a date field. Thus, the data, which is by individual date, can be easily aggregated and displayed by Month, Quarter, Year, etc. Steps to creating a Grouped report Create report with Incident_Begin_Date as the Row Label. Position cursor on any date within the PT row field right mouse click and select Group The next slide shows the Grouping dialog box that appears 18

19. Grouping Pivot Table Data (cont.) Row Labels are shown as Days can revise to reflect by Months, Quarter, or Year. The next slide shows data grouped by Year, then Quarter. 19

20. Grouping PivotTable Data (cont.) Default Format Easily Converted to Understandable/Logical Format STATE (All) STATE (All) RowLabels CountofINCIDENT_BEGIN_DATE 1953 CountofINCIDENT ColumnLabels Qtr2 9 RowLabels Qtr1 Qtr2 Qtr3 Qtr4 GrandTotal Qtr3 1 1953 9 1 3 13 Qtr4 3 1954 2 1 8 6 17 1954 Qtr1 2 1955 5 9 4 18 Qtr2 1 1956 3 7 4 2 16 Qtr3 8 1957 6 9 1 16 Qtr4 6 1958 4 1 2 7 1955 Qtr2 5 1959 3 3 1 7 Qtr3 9 1960 2 5 5 12 Qtr4 4 1961 4 5 2 1 12 1956 1962 11 1 3 7 22 Qtr1 3 Qtr2 7 1963 10 3 5 2 20 Qtr3 4 1964 7 4 5 120 136 Qtr4 2 1965 4 292 129 4 429 1957 1966 70 33 17 120 Qtr1 6 Qtr2 9 1967 63 9 123 2 197 Qtr3 1 1968 21 92 35 14 162 1958 Qtr2 4 Qtr3 1 Qtr4 2 20

21. Formatting the PivotTable Report The PivotTable Tools Design ribbon provides the ability to present your PT in any desired formatting. The PivotTable Styles Options and Styles provide automated formatting options. 21

22. Formatting the PivotTable Report (cont.) Sample report formatting STATE (All) Count of INCIDENT_ Column Labels Row Labels Qtr1 Qtr2 Qtr3 Qtr4 Grand Total 1953 9 1 3 13 1954 2 1 8 6 17 1955 5 9 4 18 STATE (All) 1956 3 7 4 2 16 1957 6 9 1 16 Count of INCIDENT Column Labels 1958 4 1 2 7 Row Labels Qtr1 Qtr2 Qtr3 Qtr4 Grand Total 1959 3 3 1 7 1953 9 1 3 13 1960 2 5 5 12 1954 2 1 8 6 17 1961 4 5 2 1 12 1955 5 9 4 18 1962 11 1 3 7 22 1956 3 7 4 2 16 1963 10 3 5 2 20 1957 6 9 1 16 1964 7 4 5 120 136 1958 4 1 2 7 1959 3 3 1 7 1960 2 5 5 12 1961 4 5 2 1 12 1962 11 1 3 7 22 1963 10 3 5 2 20 1964 7 4 5 120 136 22

23. Advanced Topics Dynamic Range Names Refresh Automatically (upon sheet selection) GetPivotData Adding Formula fields to the Pivot Table Combining PivotTables and Charts for Dashboard presentation Adding additional filters to source data Examples provide grouping at a higher level States included in FEMA data assigned a Region of the country 23

24. Contact Information If additional information is desired or you would like to better understand what our Business Advisory service capabilities are, please contact: Don Tomoff (Principal, Business Advisory) drtomoff@thornhillcpa.com 440-638-7222 LinkedIn Profile Click for Thornhill Financial LinkedIn Profile