If your work or personal life requires you to manage large datasets across multiple platforms or spreadsheets, you know that it can be challenging to see the big picture. Drawing valuable information from vast amounts of data is vital but nonetheless difficult, especially if you’re unsure how to aggregate and categorize the data efficiently. Thankfully, Excel makes it relatively easy to see the most important statistics and conclusions that can be drawn for your dataset using the built-in PivotTable feature.
But what is the process for making a pivot table in Excel? What are the different types of pivot tables at your disposal? What are some real pivot table examples for reference? Finally, where can you get expert help with making pivot tables in Excel?
In today’s guide, we will answer all of these questions, plus we will teach you how to make a pivot table with easy, step-by-step instructions. So, let’s get started!
What Is A Pivot Table?
A pivot table (sometimes written as “PivotTable” in Excel) is simply a table that aggregates the data of a much larger set containing at least one category. In other words, a pivot table takes a large data set and creates a kind of summary table in which you can view important statistics like sums, averages, and different subsets of information. Consequently, there are virtually limitless ways to use a pivot table in Excel.
That said, some of the most common uses for a pivot table include:
- Asking questions that can be answered by your data
- Creating customized formulas to summarize, categorize, or aggregate your data
- Focusing only on the categories of parts of your data that you need
- Pivoting columns to rows and vice versa
- Presenting more concise versions of a larger dataset
- Identifying and aggregating data from duplicated categories
- Filling empty cells with customized values
Naturally, these are pretty broad ways to look at pivot tables and their potential benefits. But the truth is that pivot tables can be used to do so many different things with your data that it’s difficult to summarize them all in one guide. Once you learn how to make them, you’ll soon realize that there are tons of interesting ways to put pivot tables to good use.
For example, if you use spreadsheets to track sales for your small business, you could use a pivot table to view and compare the sales figures of different products. Alternatively, if you’re trying to create a report to present to investors based on years of accumulated data, you could save yourself a lot of time by letting Excel PivotTable do most of the work for you. In short, if you have a lot of data to sort through, there’s a very good chance that you could make use of pivot tables.
How To Create A Pivot Table In Excel
Now that you know what makes a pivot table so versatile, let’s go through the steps you need to learn to create one:
Making a Pivot Table
To insert a pivot table, you simply need to open a spreadsheet that contains the dataset you want to configure. Then, follow these steps:
- Click on any cell within the spreadsheet
- Go to Insert > Tables > PivotTable
- A dialog box will open where you can change the location of the pivot table and decide if you want to use any external data sources; by default, Excel will put the pivot table in a new worksheet and use the data from the current spreadsheet
- Drag and drop the categories you want into Filters, Columns, Rows, and/or Values
- Click OK to finalize
Using Filters, Columns, Rows, and Values
As you can see, creating a pivot table in Excel is very easy; the hard part is deciding what kind of information you want the table to show. This is where Step 4 in the instructions above comes into play. Here, you will need to decide exactly what data will be shown in the pivot table and how it will be formatted.
Here is a breakdown of what each category means:
- Filters – As the name implies, the Filters category helps you choose the pieces of data that will display in your table. Excel offers a lot of popular filters to choose from, including Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Between, Top 10, Above Average, and Below Average. Alternatively, you can set a custom filter to meet your specific needs.
- Rows & Columns – The Rows and Columns categories simply allow you to customize where the information shows up in your table. For example, if you have different item labels within your data, you could put the items into the Rows section. This would display each item in its own row. Alternatively, you could put your items in columns, and each item would have its own column.
- Values – Values is one of the most important categories, as it determines what kind of data you actually see in your table, as well as what the table does with these values. You can choose which values will go into the table from existing categories within your data. By default, Excel will show the Sum of Values that adds the total sum of your values together. You can add or change this when you create the table or after it’s finished by right-clicking on a cell and choosing “Value Field Settings.” Some common Values include Sum, Count, Average, Maximum, and Minimum.
Analyzing a Pivot Table Example
Understanding how pivot tables work is easiest when you can work from a straightforward example. Let’s say that we are working with the following data set:
| A | B | C | D |
| 1 | Product | Sales Price | Date of Sale |
| 2 | Headphones | $20 | 11/5/2022 |
| 3 | Keyboard | $40 | 10/31/2022 |
| 4 | Monitor | $150 | 12/03/2022 |
| 5 | Monitor | $150 | 11/16/2022 |
| 6 | Carrying Case | $100 | 12/01/2022 |
| 7 | Keyboard | $40 | 11/25/2022 |
| 8 | Battery | $400 | 11/07/2022 |
Needless to say, this is not a lot of data. You wouldn’t really need a pivot table to extract the most important information from this spreadsheet. However, it is still more than enough to demonstrate how a pivot table really works, especially when you have repeating values (in this case, “Products”).
When creating the pivot table, let’s assign the following categories:
- Filters – Date of Sale (All)
- Rows – Product
- Columns – N/A (Empty)
- Values – Sum of Amounts
By following the steps outlined above, you’ll end up with a pivot table that looks something like this:
| A | B | |
| 1 | Date of Sale (All) | |
| 2 | ||
| 3 | Row Labels | Sum of Amount |
| 4 | Headphones | $20 |
| 5 | Keyboard | $80 |
| 6 | Monitor | $300 |
| 7 | Carrying Case | $100 |
| 8 | Battery | $400 |
| 9 | Total | $900 |
In this pivot table example, we have effectively combined the sales totals for the same products, eliminating redundancy in our dataset. This also allows us to see the total sales numbers for each product type, as well as the total sum of all sales for the designated period of time.
How To Refresh Pivot Tables
Pivot tables are extremely useful — but only if the data is up to date. Unfortunately, if you change data in your source spreadsheet, it will not automatically apply to the pivot table (unless you’ve told it to do so). Similarly, if you try to change data in your pivot table, it will not change your source dataset. This is why it is necessary to refresh your pivot table(s), especially if you are actively updating your spreadsheet.
Thankfully, refreshing one or more pivot tables is really easy:
- Click on the pivot table you want to refresh
- Select “PivotTable Tools”
- Go to Analyze > Refresh
- (Optional) If you want to refresh all of your pivot tables, go to Analyze > Refresh > Refresh All
You can also set your pivot tables to automatically refresh when you open your workbook by following these steps:
- Click on the pivot table you want to automatically refresh
- Select “PivotTable Tools”
- Go to Analyze > Options
- Under the “Data” tab, choose “Refresh data when opening the file”
How To Delete A Pivot Table
Deleting a pivot table is simple. You just click anywhere on the table, click on PivotTable Tools, go to Analyze > Select, choose “Entire PivotTable,” and click on “Delete.” But what if you want to keep the data without keeping the actual pivot table? The easiest way is to use Copy and Paste Special, like this:
Windows
- Highlight your entire pivot table
- Press CTRL + C
- Click on an empty cell on your spreadsheet
- Press CTRL + ALT + V
Mac
- Highlight your entire pivot table
- Press Command + C
- Click on an empty cell on your spreadsheet
- Press Control + Command + V
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!