The problem we all face is that we have mountains of data and need a way to digest it. We're all looking for a way to make sense out of large sets of data and find out what the data indicates about the situation at hand.
Excel's PivotTable feature is a drag and drop analysis tool. Point Excel to tables of data in your spreadsheet, and slice your data until you find an answer to your question. Most importantly, it's an easy-to-use tool right inside of Excel where your data might already live.
The screenshot below shows a great example of PivotTables in action. The original data is shown on the left side, with two different PivotTables used to summarize and analyze the data.
Without PivotTables, you have to write multiple formulas to understand your data. I generated the reports below in under 30 seconds with the help of PivotTables. It's quick to use and quite powerful. This tutorial will build upon our recent beginners PivotTable tutorial:
The skills that we'll build in this tutorial will help you advance your PivotTable knowledge and get more comfortable with advanced features.
How to Analyze Data Using PivotTables in Excel (Watch & Learn)
In the screencast below, you'll watch me walk through several ways to analyze your data with PivotTables. If you're curious about how you might use them for your own data, give it a watch to learn more about the power of PivotTables.
Ready to walk through these steps on your own? Read on to follow along with my step-by-step instructions for developing your PivotTable analysis skills.
Review: Create Your First PivotTable
Download this: I've created a spreadsheet with example data for you to download for free and use as you work through this tutorial. Use this data or your own to practice the skills as we go.
To set this stage for a tutorial, let's review how to create a PivotTable. In Microsoft Excel, start with a spreadsheet of data you want to digest and summarize.
Highlight all of the columns in your spreadsheet, and then browse to Insert > PivotTable. I always leave all of the settings as default and press OK to create the PivotTable on a new worksheet.
Now, Excel will take you to a new worksheet to build out your PivotTable. On the right side is a report builder which is a drag-and-drop tool to customize your PivotTable.
If you have problems with creating your PivotTable, make sure to double check the following:
- Make sure every column has headers, or titles for the column at the top.
- I typically highlight the entire columns so that my PivotTable will include any new rows that are added to the data later on.
- Each row of the data should be an individual entry, or basically a "record" of its own.
Remember to review the first tutorial, How to Create Your First PivotTable, for additional tips on building your first PivotTable.
Now that we've reviewed PivotTables 101, it's time to move onto more advanced techniques.
Using PivotTable Structures
The key to using PivotTables powerfully is to understand what I call the "report builder." Let's dive in head first to learn how this menu works.
Working With PivotTable Fields
In the top half of the PivotTable menu is the Fields menu. Each column in your original data is shown as a field of its own.
We can drag and drop any field into one of the four boxes below: Filters, Columns, Rows, and Values.
The boxes that we drag our fields to will shape how the PivotTable appears. Let's walk through how each one of them will affect our PivotTable's presentation.
1. Filters
When you drop a field into the Filter box, the PivotTable will add the option to filter for that column. Let's try an example; here's the field arrangement I'm using below:
In the screenshot below, I've dragged Client into the Filters box. At the very top of the PivotTable is a box that says Client. When I click on the dropdown menu, I can choose which client I want to filter for in the list. I'll click on a single client, and then click OK.
You can see that the PivotTable is now filtered down for the single client I've selected. Also try out checking the Select Multiple Items box to choose more than one option to include.
2. Rows
Rows are what appears on the left side of a PivotTable. When you drag a field to the "Rows" box, each of the values from that field will be shown on a row.
In the screenshot below, you can see two different configurations. When I place Client in the Rows box, notice that it puts each of the client names on a row of its own.
In the top screenshot, I've put Client in the rows box, and the PivotTable has put each of the client names on a row. The bottom screenshot removes Client as a row, and trades it for the Project type.
Also try out dragging multiple items to the Rows box. You can create basically two layers of division by stacking items as rows.
3. Columns
Dragging a field to the Columns box will create a separate column for each value in your data.
In the example below, I've kept the Client field as the rows in my PivotTable. To get more detailed views of my data, I've added Project Type in the Columns field to split the type of work I'm doing for each client up.
Using combinations of rows and columns will give you more insight into your data. I like to use Rows for my key field, and then split the data up by column using the Columns field.
4. Values
You can build out a complex set of rows and columns, but PivotTables really take on meaning when you drag a field to the Values box.
The values column works a little differently than the rest of the fields. You can't drag just any field to the Values box and get good results. This box is reserved for the numerical values in your data, like the a dollar amount or amount of hours spent, for example.
PivotTables let you work with values in a variety of ways. When you drag a field into the Values box, it often defaults to showing a Count of each item. Basically, Excel is counting up each time a row appears, for example, and prints the count of that item in the PivotTable.
What if we don't want to show a count? Instead, let's say that we want to show the Sum of the values in a row (for example, adding up all amounts that we've billed each client for.)
Simply right click in the PivotTable and chose Summarize Values By > Sum. You'll notice in the screenshot above that the PivotTable now shows the total for each client.
You can change the way your PivotTable works with values to show a count, average, maximum value, minimum value, or product.
How to Find Meaning in Your Data
So, now we understand how to build PivotTables by dragging and dropping fields into one of the four boxes in the report builder. The question is: how do we use this to understand our data better?
When I'm building a PivotTable, I start off by deciding what I want to know first. If I can picture the end result first, I can think about how to arrange the fields to get the right result.
It often helps me to phrase the outcome as a question. Try building PivotTables that answer the following questions from the example data:
- Which clients did I bill the most in 2016? In 2017?
- What month of 2016 was my most productive?
- Which project type did I bill the highest hourly rate? The lowest hourly rate?
Keeping Your Data Source Updated
Maintaining your PivotTables is essential to meaningful and correct analysis. Before we wrap up, let's look at two important features to make sure your PivotTables are working correctly.
Let's say that your data evolves, and additional rows or columns are added to your spreadsheet. We'll need to update the data that we're including in the PivotTable.
With the PivotTable selected, browse to the Analyze tab and click on Change Data Source. You can type in a new selection of columns, or click on the arrow to re-select which columns and rows to include your data.
Refreshing Your PivotTable Data
Even if you've selected the correct range of data, if you add new records (rows) or change your original data, you'll need to refresh the PivotTable. Even though PivotTables are dynamic and it's easy to change the report you're building, you'll need to refresh the PivotTable.
To refresh your PivotTable, start off by making sure that your PivotTable is selected. The easiest way is to simply right click within the PivotTable and choose Refresh. You can also find the Analyze tab on the ribbon and click on the Refresh button.
I use PivotTables to keep an eye on my data. I'm constantly feeding it new rows of data as more events happen. Making sure that I periodically refresh the PivotTable will ensure that my data is up to date and accurate.
Recap and Keep Learning More
This tutorial helped you use PivotTables to make sense of your data. These basic analysis tools can help you take a spreadsheet and gain a better understanding of your data.
Check out these tutorials to learn more about Excel and analysis tools:
- How to Create Your First PivotTable in Microsoft Excel is a great way to get started with no prior knowledge.
- Bob Flisser has an excellent tutorial on the analysis potential of the Scenario Manager.
- Understanding your data is a critical part of what's broadly called "Business Intelligence." Andrew Blackman explains this in his piece, What Is Business Intelligence?.
How will you use PivotTables to digest your data better? Check in with a comment below to let me know, or feel free to ask a question.
