Pivot tables are without a doubt one of the most powerful tools available in Excel. They allow you to summarize and analyze large and complex data in a very simple way. However, if you’ve never used them before they can be a bit intimidating.
In this article, I’ll show you just how easy it is to create and use a pivot table in Excel!
What is a pivot table?
As I mentioned before, a pivot table is a feature in Excel that helps you summarize and analyze data.
But what exactly is a pivot table? The easiest way to explain is to show you an example.
Here I have some data about employees at a department store, including their sales in 2019 and 2020, and their most recent bonus.
Now imagine I’m the manager of the department store and I want to know the amount of bonuses I’ve paid for each department.
By using a pivot table, I can quickly get that information.
Pretty cool, right? Pivot tables help summarize and analyze data in all kinds of ways.
But before I get into those details, I’ll show you how to get started with creating a pivot table in Excel.
How to create a pivot table in Excel
Excel makes it really simple to insert a pivot table into your spreadsheet. The exact details will be a bit different depending on what version of Excel you’re running, and on what system. But the overall process will be the same.
Follow the easy steps below to create your pivot table in Excel.
1. Organize your data
The first step is to set up your data. It should be in a table with headers, and it should be as clean as possible. Remove any blank rows, and make sure that each column contains the same type of data (for example, don’t mix numbers and text).
Once your data table is cleaned up, you’re ready to create your pivot table.
2. Insert a pivot table
To insert a pivot table, click on the Pivot Table button. This will typically be under the Insert tab, but can also be under the Data tab, depending on your version of Excel.
Once you’ve located the Pivot Table button, click it to get the following screen.
Within this Create PivotTable screen, select the data that you want to analyze, and choose where the pivot table itself will go.
In the Location section, select your entire dataset including the headers. Below that, choose where to insert the pivot table. For this example, I’ve chosen to insert it on the same worksheet right beside my source data.
There’s also an option to insert a Recommended Pivot Table. If you select this option, Excel takes a look at your data and generates and populates a pivot table automatically.
This is a good option for simple datasets, so feel free to give it a try!
3. Build your pivot table
Press OK and you’ll create an empty pivot table, along with the Pivot Table Builder screen (it can also be called the Pivot Table Fields screen depending on your version of Excel).
This is the part where you choose what aspects of the source data you want to show in the pivot table.
In this screen, you drag and drop the data fields into the section you want them. As you can see, you can designate fields as rows, columns, values, or filters.
I’ll show more of how this works later.
Choosing what fields to add to the pivot table can be the most challenging part of the process. You have a lot of autonomy here to summarize the data in any way you see fit.
Remember the first example, where I showed the bonuses by department? I’ll build that again here so you can see the process.
In order to build this pivot table, the first thing I did was drag the Department field into the Row Labels box. This makes a pivot table row for each unique value in the Department column of my source data (e.g. clothing, food, etc.).
Next, I dragged the Bonus field down to the Values box, since I want to summarize the bonuses paid in each department.
Notice that it says “Sum of Bonus” in the Values section. Any time you generate a value, you need to tell Excel exactly what to show.
In this case I chose Sum, because I want the bonuses added up for each department.
There’s a whole bunch of options however: count, average, maximum, etc. To change the type, just select the field in the Value section and you’ll see all the options.
When you choose the fields to display, you can see the pivot table updating in real time. This allows you to play around and get the configuration you want.
Once you’re happy with your pivot table, just select any cell outside the table and the Pivot Table menu will disappear. Now you’ve created your pivot table!
How to use pivot tables
Now that you’ve seen how to create a pivot table, I’ll talk about some common uses for them, as well as some tips to help you get the exact table you want.
Different value types
As I mentioned before, pivot tables can crunch your data in a variety of ways. In the previous example I used Sum to add up the bonuses for each department.
This time, I’ll use Average to get the average sales within each department.
Again, I start by dragging the fields to the proper sections. When I drag the 2020 Sales field into the Values section, it defaults to Sum. To change this, I select the field and choose Average from the list of options.
Once I do that, the pivot table updates automatically and shows me the average sales in each department!
Two-dimensional tables
You can use pivot tables to generate two-dimensional tables with multiple columns as well as rows.
To expand on the previous example, what happens if I want to see the average sales in 2019 and 2020? I do that by adding both the 2019 and 2020 sales to the Values section.
This generates two values columns: one for each field.
Note that you can even add another field directly to the Column Labels section to get an even more granular summary of your source data!
Filtering
Another cool feature of pivot tables is the option to filter. This way, you can control which data is shown.
To filter a field that is already included in a row or column, simply click on the dropdown icon:
In this case, I choose which departments I want to include in the pivot table.
You can also add filters for fields that are not in your pivot table as rows or columns.
For example, suppose I want to remove Frank’s sales numbers from the calculation of average sales.
I do this by adding the Name field to the Filter section in the Pivot Table Builder. This creates a filter option at the top of the pivot table.
Then I simply click on the dropdown icon and deselect Frank.
Formatting pivot tables
The cells in pivot tables can be formatted just like regular cells. You can change the number type (to a currency, date, etc.), decimal level, font, color, and so much more.
You can even rename the headers to replace the auto-generated ones.
Refreshing data
If the source data changes, you can refresh a pivot table to make sure it’s including the most up-to-date information.
Right-click on any cell of the pivot table and press Refresh Data to do so.
Percent of total
Pivot tables allow you to change the way values are displayed.
For instance, I could choose to view the bonuses by department as a percentage of the total bonuses paid.
To do this, right-click on the data and choose Field Settings. From this menu, you then choose how to display the data.
(Depending on your version of Excel, you may be able to do this directly from the right-click without going into Field Settings).
Final thoughts
Pivot tables are a fantastic way to summarize and analyze data in Excel. Once you learn how to make them, they’re quick and easy to work with, and extremely powerful!
If you want to learn more and maximize your Excel skills, take a look at our Excel functions blog posts.