Do you find it a challenge to organize data in Microsoft Excel? Do you feel overwhelmed with all the information and have no idea where to start? Pivot tables are an excellent way of organizing your data. They allow you to see the breakdown of any number of fields, which makes identifying patterns easier. This tutorial shows you how to create a pivot table in Excel. (Includes practice file.)
What’s an Excel Pivot Table?
You might think of a pivot table as a custom-created summary table of your spreadsheet. It’s a little bit like transpose in Excel, where you can switch your columns and rows. But it also has elements of Excel Tables. And like tables, you can use Excel Slicers to drill down into your data.
You create the pivot table by defining which fields to view and how the information should display. Then, based on your field selections, Excel organizes the data to see a different view of your data.
For example, I’ve uploaded a data file with information on 4000 fictitious voters that include the following data fields:
- Voter ID
- Party Affiliation
- Precinct
- Age group
- When they last voted
- Years they’ve been registered
- Ballot status
Looking at the first 20 records, you can see the content is beyond boring. In this format, the key question it answers is how many voters exist in all the precincts.
Using an Excel pivot table, you can organize and group the same data in ways that start to answer actionable questions like:
- What is the party breakdown by precinct?
- Which precincts have the most Democrats?
- How many voter pamphlets do I need for Precinct 2416?
- Do 18-21-year-olds vote?
Excel pivot tables allow you to group the spreadsheet by any of your data fields. For example, the picture below shows a voter count by the political party by precinct.
Using a pivot table, I can continue to refine the information by selecting more fields from the PivotTable Field List. For example, I can take the same data and segment it by voter age group.
Understanding Table Structure
In the screen snap above, I’ve labeled the main areas of the pivot table. Depending on which Excel version you have, the labels may be different for the lower quadrants. For example, some versions use “Rows” and “Columns,” whereas others use “Row Labels” and “Column Labels.”
- PivotTable Field List – this section in the top-right displays the fields in your spreadsheet. You may check a field or drag it to a quadrant in the lower part.
- The lower right quadrants – this area defines where and how the data shows on your pivot table. For example, you can have a field show in either a column or a row. You may also indicate if the information should be counted, summed, averaged, filtered, and so on.
- The area to the left results from your selections from [1] and [2]. You’ll see that the only difference I made in the last pivot table was to drag the AGE GROUP field underneath the PRECINCT field in the Row Labels quadrant.
How to Create Excel Pivot Table
There are several ways to build a pivot table. Excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. For example, numeric data such as Precinct counts tend to appear to the right in columns. Textual data, such as Party, would appear in rows.
While you can check fields to display and let Excel build your pivot table, I prefer to use the “drag and drop” method. This is partly because I like to visualize my data in columns and rows. It may also be easier if you have fields, which can appear to be number-like such as a precinct value.
To create an Excel pivot table,
- Open your original spreadsheet and remove any blank rows or columns.
You may also use the Excel sample data at the bottom of this tutorial.
- Make sure each column has a meaningful label.
The column labels will be carried over to the Field List.
- Verify your columns are properly formatted for their data type.
Mixed data formats will pose a problem.
- Click any populated cell.
This helps set the range.
- Highlight your data range.
Tip: You can press Ctrl + A to select all.
- Click the Insert tab.
Your toolbar groups will change.
- Select the PivotTable button from the Tables group.
This should be the first group.
- The Create PivotTable dialog appears.
Your highlighted range will be shown. Also, the PivotTable report will open in a new worksheet.
- Click OK.
A new worksheet opens with a blank pivot table with the fields from our Voters (source) sheet carried over to the PivotTable Field List on the right.
- Drag a field down to the Rows Labels quadrant.
I used the Precinct column. The left side of your sheet should show a row for each precinct.
- The next step is to ask what you would like to know about each precinct. I’ll drag the PARTY field from the PivotTable Fields List to the Column Labels quadrant. The sheet updates and shows a breakout for each precinct. Note that no values are showing.
- To see each political party’s counts, I need to drag the same field to the Values quadrant.
Excel has determined that I want to COUNT the number of voters.
Additional Groupings and Options
As you build your Excel pivot table, you’ll think of more ways to group the information. For example, you might want to know the Age Range of voters by Precinct by Party. In this case, I would drag the AGE GROUP column from the PivotTable Field List down below the PRECINCT value in Row Labels.
Each age group is broken out and indented by precinct. At this stage, you might also be thinking of the usability. As with a regular spreadsheet, you may manipulate the fields. For example, you might want to rename “Grand Total” to “Total” or even collapse the age values for one or more precincts. You can also hide or show rows and columns. These features work the same way as a regular spreadsheet.
One different area is the pivot table has its own options. You can use these options by right-clicking a cell within and selecting PivotTable Options… For example, you might only want Grand Totals for columns and not rows.
There are also ways to filter the data using the controls next to Row Labels or Column labels on the pivot table. You may also drag fields to the Report Filter quadrant.
Troubleshooting Excel Pivot Tables
You might encounter several “gotchas” with this example file or another spreadsheet. For example, sometimes, when you move around your pivot table, the PivotTable Field List disappears. To get it back, click any cell with a value.
You can also move or “pivot” your data by right-clicking a data field on the table and selecting the “Move” menu. From here, you can move a column to a row or even change the position. An example of this might be the “LAST VOTED” values since Excel will sort by the month first. You might prefer to move the data, so the election dates are in chronological order.
I prefer not to add fields to a pivot table. I think it’s easier to add the fields first to your source spreadsheet. The reason is you might get items out of sync if you move data unless you make them a calculated field.
Another concern is blank fields. While making the example pivot table spreadsheet, I managed to create a “pivot table field name is not valid error.”
Excel pivot tables may not make the election exciting, but they can make the data analysis easier. Without these tables, you’d probably spend more time filtering, sorting, and subtotaling. The other benefit is that it’s easy to start over by deselecting fields or moving them around. Feel free to download the practice spreadsheet below and play with the data. This may be the only time you’re allowed to manipulate election data.
Practice Resource File
Click the button below to download the pivot table example file.