Excel PivotTables – An essential tool for agency data analysts
Written by: Natalie Komitsky
If you are not already using PivotTables, you probably have heard them mentioned. They are an amazing feature of Microsoft Excel, one that can simplify the efforts of anyone who regularly deals with data.
In a nutshell, PivotTable is a tool within Excel that helps users turn a large amount of data into a summary table in just a few clicks, dragging and dropping information into place without mastering complex formulas and calculations.
PivotTables are used with many different kinds of data. Government agencies and departments, such as the Environmental Protection Agency, the Bureau of Educational and Cultural Affairs, the Department of the Treasury, and the Department of Health and Human Services, mention PivotTables and provide many data sets on their public-facing websites.
How can I use PivotTables?
Building your own PivotTable begins with a spreadsheet that contains data. We have rows from left to right and columns from top to bottom. These rows and columns are referred to as fields in the data analysis.
Source: Elder Justice Initiative
Next, go to the top left corner of your Excel spreadsheet menu bar. Click on Insert, and you will see PivotTable listed as an option in the top left corner of the menu bar.
When you click on PivotTable, a display box will ask you to specify how you would like your PivotTable to be configured.
After selecting the data range, you can decide if you’d like the PivotTable to appear on the same worksheet or on a new worksheet. After you click OK, you will see the PivotTable features on the right and an area reserved for your pivot table report on the left.
Now is the time to decide how your pivot table report will summarize the data.
You can drag the fields from the PivotTable Fields box and drop them into the Filters, Columns, Rows, and Values boxes. This is where the following steps will vary quite a bit, depending on what you would like to do with the data.
To better exemplify the benefit of this feature, let’s look at how one agency is incorporating the use of PivotTables into their work:
Elder Justice Initiative at the U.S. Department of Justice
The Senior Abuse Financial Tracking and Accounting (SAFTA) tool was created to facilitate the work of a simplified forensic instrument for illuminating suspicious financial patterns and facilitating the prosecution of suspected elder financial exploitation. Law enforcement officers download the SAFTA tool and enter financial records. The tool automatically creates pivot tables and charts, providing forensic summaries and insights into the raw financial records.
When the tool is downloaded, the user can open it immediately in Excel. The user interface is shown below:
In their training for law enforcement officers, the Elder Justice Initiative provides a sample of financial data to demonstrate how the SAFTA tool automatically creates a PivotTable that prepares and displays the data in an optimal format for analysis. Users can then manipulate the PivotTable to drill down or summarize the data based on the research question.
PivotTables make it easy to compare data and recognize patterns and trends. They can summarize, analyze, and present findings that support informed decisions. PivotTables are also integrated into various advanced analytics features in Excel, such as data slicing, visualization, dashboards, Power Query, and Power Pivot. The PivotTable features provide endless possibilities for gathering and conveying meaningful insights and analytic information for analysts who want to power up their data literacy.
Hungry for more?
Our Analytics course participants rank learning how to apply PivotTables in data-rich environments among the most significant benefits of our courses. Which course is best for you? Introduction To Analytics, Analytics Boot Camp, Analytics Boot Camp: Excel Essentials, Program And Budget Analysis Using Microsoft Excel or Introduction To Statistics.
