The pivot table is one of Microsoft Excel's most powerful — and intimidating — functions. Pivot tables can help you summarize and make sense of large data sets. However, they also have a reputation for being complicated.
The good news is that learning how to create a pivot table in Excel is much easier than you may believe.
We’re going to walk you through the process of creating a pivot table and show you just how simple it is. First, though, let's take a step back and make sure you understand exactly what a pivot table is, and why you might need to use one.
What is a pivot table?
What are pivot tables used for?
How to Create a Pivot Table
Pivot Table Examples
In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily.
The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can reveal useful information.
What are pivot tables used for?
If you're still feeling a bit confused about what pivot tables actually do, don't worry. This is one of those technologies that are much easier to understand once you've seen it in action.
The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They can be used to better understand, display, and analyze numerical data in detail.
With this information, you can help identify and answer unanticipated questions surrounding the data.
Here are seven hypothetical scenarios where a pivot table could be helpful.
1. Comparing Sales Totals of Different Products
Let’s say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and product 3. You want to figure out which of the three has been generating the most revenue.
One way would be to look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. The same process can then be done for product 2, and product 3 until you have totals for all of them. Piece of cake, right?
Imagine, now, that your monthly sales worksheet has thousands upon thousands of rows. Manually sorting through each necessary piece of data could literally take a lifetime.
With pivot tables, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 — and calculate their respective sums — in less than a minute.
2. Showing Product Sales as Percentages of Total Sales
Pivot tables inherently show the totals of each row or column when created. That's not the only figure you can automatically produce, however.
Let's say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. The pivot table automatically gives you three totals at the bottom of each column — having added up each product's quarterly sales.
But what if you wanted to find the percentage these product sales contributed to all company sales, rather than just those products' sales totals?
With a pivot table, instead of just the column total, you can configure each column to give you the column's percentage of all three column totals.
Let’s say three products totaled $200,000 in sales. The first product made $45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.
To show product sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select Show Values As > % of Grand Total.
3. Combining Duplicate Data
In this scenario, you've just completed a blog redesign and had to update many URLs. Unfortunately, your blog reporting software didn't handle the change well and split the "view" metrics for single posts between two different URLs.
In your spreadsheet, you now have two separate instances of each individual blog post. To get accurate data, you need to combine the view totals for each of these duplicates.
Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title.
Voilà, the view metrics from those duplicate posts will be aggregated automatically.
4. Getting an Employee Headcount for Separate Departments
Pivot tables are helpful for automatically calculating things that you can't easily find in a basic Excel table. One of those things is counting rows that all have something in common.
For instance, let’s say you have a list of employees in an Excel sheet. Next to the employees' names are the respective departments they belong to. You can create a pivot table from this data that shows you each department's name and the number of employees that belong to those departments.
The pivot table’s automated functions effectively eliminate your task of sorting the Excel sheet by department name and counting each row manually.
5. Adding Default Values to Empty Cells
Not every dataset you enter into Excel will populate every cell. If you're waiting for new data to come in, you might have lots of empty cells that look confusing or need further explanation.
That's where pivot tables come in.
You can easily customize a pivot table to fill empty cells with a default value, such as $0, or TBD (for "to be determined"). For large data tables, being able to tag these cells quickly is a valuable feature when many people are reviewing the same sheet.
To automatically format the empty cells of your pivot table, right-click your table and click PivotTable Options.
In the window that appears, check the box labeled Empty Cells As and enter what you'd like displayed when a cell has no other value.
How to Create a Pivot Table
- Enter your data into a range of rows and columns.
- Sort your data by a specific attribute.
- Highlight your cells to create your pivot table.
- Drag and drop a field into the "Row Labels" area.
- Drag and drop a field into the "Values" area.
- Fine-tune your calculations.
Now that you have a better sense of what pivot tables can be used for, let's get into the nitty-gritty of how to actually create one.
Step 1. Enter your data into a range of rows and columns.
Every pivot table in Excel starts with a basic Excel table, where all your data is housed. To create this table, simply enter your values into a specific set of rows and columns. Use the topmost row or the topmost column to categorize your values by what they represent.
For example, to create an Excel table of blog post performance data, you might have:
- A column listing each "Top Pages."
- A column listing each URL's "Clicks."
- A column listing each post's "Impressions."
We'll be using that example in the steps that follow.
Step 2. Sort your data by a specific attribute.
Once you’ve entered all your data into your Excel sheet, you’ll want to sort your data by attribute. This will make your information easier to manage once it becomes a pivot table.
To sort your data, click the Data tab in the top navigation bar and select the Sort icon underneath it. In the window that appears, you can sort your data by any column you want and in any order.
For example, to sort your Excel sheet by "Views to Date," select this column title under Column and then select whether you want to order your posts from smallest to largest, or from largest to smallest.
Select OK on the bottom-right of the Sort window.
Now, you’ve successfully reordered each row of your Excel sheet by the number of views each blog post has received.
Step 3. Highlight your cells to create your pivot table.
Once you've entered and sorted your data, highlight the cells you'd like to summarize in a pivot table. Click Insert along the top navigation, and select the PivotTable icon.
You can also click anywhere in your worksheet, select "PivotTable," and manually enter the range of cells you'd like included in the PivotTable.
This opens an options box. Here you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet, in addition to setting your cell range.
If you open a new sheet, you can navigate to and away from it at the bottom of your Excel workbook. Once you've chosen, click OK.
Alternatively, you can highlight your cells, select Recommended PivotTables to the right of the PivotTable icon, and open a pivot table with pre-set suggestions for how to organize each row and column.
Note: If using an earlier version of Excel, "PivotTables" may be under Tables or Data along the top navigation, rather than "Insert." In Google Sheets, you can create pivot tables from the Data dropdown along the top navigation.
Step 4. Drag and drop a field into the "Row Labels" area.
After you've completed Step 3, Excel will create a blank pivot table for you.
Your next step is to drag and drop a field — labeled according to the names of the columns in your spreadsheet — into the Row Labels area. This will determine what unique identifier the pivot table will organize your data by.
For example, let's say you want to organize a bunch of blogging data by post title. To do that, you'd simply click and drag the “Top pages” field to the "Row Labels" area.
Note: Your pivot table may look different depending on which version of Excel you're working with. However, the general principles remain the same.
Step 5. Drag and drop a field into the "Values" area.
Once you've established how you're going to organize your data, your next step is to add in some values by dragging a field into the Values area.
Sticking with the blogging data example, let's say you want to summarize blog post views by title. To do this, you'd simply drag the "Views" field into the Values area.
Step 6. Fine-tune your calculations.
The sum of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depending on what you want to calculate.
On a Mac, you can do this by clicking on the small i next to a value in the "Values" area, selecting the option you want, and clicking "OK." Once you’ve made your selection, your pivot table will be updated accordingly.
If you're using a PC, you'll need to click on the small upside-down triangle next to your value and select Value Field Settings to access the menu.
When you’ve categorized your data to your liking, save your work and use it as you please.
Pivot Table Examples
From managing money to keeping tabs on your marketing effort, pivot tables can help you keep track of important data. The possibilities are endless!
See three pivot table examples below to keep you inspired.
1. Creating a PTO Summary and Tracker
If you’re in HR, running a business, or leading a small team, managing employees’ vacations is essential. This pivot allows you to seamlessly track this data.
All you need to do is import your employee’s identification data along with the following data:
- Sick time.
- Hours of PTO.
- Company holidays.
- Overtime hours.
- Employee’s regular number of hours.
From there, you can sort your pivot table by any of these categories.
2. Building a Budget
Whether you’re running a project or just managing your own money, pivot tables are an excellent tool for tracking spend.
The simplest budget just requires the following categories:
- Date of transaction
- Withdrawal/Expenses
- Deposit/Income
- Description
- Any overarching categories (like paid ads or contractor fees)
With this information, you can see your biggest expenses and brainstorm ways to save.
3. Tracking Your Campaign Performance
Pivot tables can help your team assess the performance of your marketing campaigns.
In this example, campaign performance is split by region. You can easily which country had the highest conversions during different campaigns.
This can help you identify tactics that perform well in each region and where advertisements need to be changed.
Digging Deeper With Pivot Tables
You've now learned the basics of pivot table creation in Excel. With this understanding, you can figure out what you need from your pivot table and find the solutions you’re looking for.
For example, you may notice that the data in your pivot table isn't sorted the way you'd like. If this is the case, Excel's Sort function can help you out. Alternatively, you may need to incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.
Editor's note: This post was originally published in December 2018 and has been updated for comprehensiveness.