The best part about using a Pivot Table is that even if you don’t know anything in Excel, you can still do pretty awesome things with it with a very basic understanding of it. Let’s get started. Click here to download the sample data and follow along.
What is a Pivot Table and Why Should You Care?
A Pivot Table is a tool in Microsoft Excel that allows you to quickly summarize huge datasets (with a few clicks). Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports. Suppose you have a dataset as shown below:
This is sales data that consists of ~1000 rows. It has the sales data by region, retailer type, and customer. Now your boss may want to know a few things from this data:
What were the total sales in the South region in 2016? What are the top five retailers by sales? How did The Home Depot’s performance compare against other retailers in the South?
You can go ahead and use Excel functions to give you the answers to these questions, but what if suddenly your boss comes up with a list of five more questions. You’ll have to go back to the data and create new formulas every time there is a change. This is where Excel Pivot Tables comes in really handy. Within seconds, a Pivot Table will answer all these questions (as you’ll learn below). But the real benefit is that it can accommodate your finicky data-driven boss by answering his questions immediately. It’s so simple, you may as well take a few minutes and show your boss how to do it himself. Hopefully, now you have an idea of why Pivot Tables are so awesome. Let’s go ahead and create a Pivot Table using the data set (shown above).
Inserting a Pivot Table in Excel
Here are the steps to create a pivot table using the data shown above:
Click anywhere in the dataset. Go to Insert –> Tables –> Pivot Table. In the Create Pivot Table dialog box, the default options work fine in most of the cases. Here are a couple of things to check in it: Table/Range: It’s filled in by default based on your data set. If your data has no blank rows/columns, Excel would automatically identify the correct range. You can manually change this if needed. If you want to create the Pivot Table in a specific location, under the option ‘Choose where you want the PivotTable report to be placed’, specify the Location. Else, a new worksheet is created with the Pivot Table. Click OK.
As soon as you click OK, a new worksheet is created with the Pivot Table in it. While the Pivot Table has been created, you’d see no data in it. All you’d see is the Pivot Table name and a single line instruction on the left, and Pivot Table Fields on the right.
Now before we jump into analyzing data using this Pivot Table, let’s understand what are the nuts and bolts that make an Excel Pivot Table.
The Nuts & Bolts of an Excel Pivot Table
To use a Pivot Table efficiently, it’s important to know the components that create a pivot table. In this section, you’ll learn about:
Pivot Cache Values Area Rows Area Columns Area Filters Area
Pivot Cache
As soon as you create a Pivot Table using the data, something happens in the backend. Excel takes a snapshot of the data and stores it in its memory. This snapshot is called the Pivot Cache. When you create different views using a Pivot Table, Excel does not go back to the data source, rather it uses the Pivot Cache to quickly analyze the data and give you the summary/results. The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing the data. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results. Note: One downside of pivot cache is that it increases the size of your workbook. Since it’s a replica of the source data, when you create a pivot table, a copy of that data gets stored in the Pivot Cache.
Values Area
The Values Area is what holds the calculations/values. Based on the data set shown at the beginning of the tutorial, if you quickly want to calculate total sales by region in each month, you can get a pivot table as shown below (we’ll see how to create this later in the tutorial). The area highlighted in orange is the Values Area.
In this example, it has the total sales in each month for the four regions.
Rows Area
The headings to the left of the Values area makes the Rows area. In the example below, the Rows area contains the regions (highlighted in red):
Columns Area
The headings at the top of the Values area makes the Columns area. In the example below, Columns area contains the months (highlighted in red):
Filters Area
Filters area is an optional filter that you can use to further drill down in the data set. For example, if you only want to see the sales for Multiline retailers, you can select that option from the drop down (highlighted in the image below), and the Pivot Table would update with the data for Multiline retailers only.
Analyzing Data Using the Pivot Table
Now, let’s try and answer the questions by using the Pivot Table we have created. Click here to download the sample data and follow along. To analyze data using a Pivot Table, you need to decide how you want the data summary to look in the final result. For example, you may want all the regions in the left and the total sales right next to it. Once you have this clarity in mind, you can simply drag and drop the relevant fields in the Pivot Table. In the Pivot Tabe Fields section, you have the fields and the areas (as highlighted below):
The Fields are created based on the backend data used for the Pivot Table. The Areas section is where you place the fields, and according to where a field goes, your data is updated in the Pivot Table. It’s a simple drag and drop mechanism, where you can simply drag a field and put it in one of the four areas. As soon as you do this, it will appear in the Pivot Table in the worksheet.
Now let’s try and answer the questions your manager had using this Pivot Table. Q1: What were the total sales in the South region? Drag the Region field in the Rows area and the Revenue field in the Values area. It would automatically update the Pivot Table in the worksheet. Note that as soon as you drop the Revenue field in the Values area, it becomes Sum of Revenue. By default, Excel sums all the values for a given region and shows the total. If you want, you can change this to Count, Average, or other statistics metrics. In this case, the sum is what we needed. The answer to this question would be 21225800.
Q2 What are the top five retailers by sales? Drag the Customer field in the Row area and Revenue field in the values area. In case, there are any other fields in the area section and you want to remove it, simply select it and drag it out of it. You’ll get a Pivot Table as shown below:
Note that by default, the items (in this case the customers) are sorted in an alphabetical order. To get the top five retailers, you can simply sort this list and use the top five customer names. To do this:
Right-click on any cell in the Values area. Go to Sort –> Sort Largest to Smallest.
This will give you a sorted list based on total sales.
Q3: How did The Home Depot’s performance compare against other retailers in the South? You can do a lot of analysis for this question, but here let’s just try and compare the sales. Drag the Region Field in the Rows area. Now drag the Customer field in the Rows area below the Region field. When you do this, Excel would understand that you want to categorize your data first by region and then by customers within the regions. You’ll have something as shown below:
Now drag the Revenue field in the Values area and you’ll have the sales for each customer (as well as the overall region).
You can sort the retailers based on the sales figures by following the below steps:
Right-click on a cell that has the sales value for any retailer. Go to Sort –> Sort Largest to Smallest.
This would instantly sort all the retailers by the sales value. Now you can quickly scan through the South region and identify that The Home Depot sales were 3004600 and it did better than four retailers in the South region.
Now there are more than one ways to skin the cat. You can also put the Region in the Filter area and then only select the South Region. Click here to download the sample data. I hope this tutorial gives you a basic overview of Excel Pivot Tables and helps you in getting started with it. Here are some more Pivot Table Tutorials you may like:
Preparing Source Data For Pivot Table. How to Apply Conditional Formatting in a Pivot Table in Excel. How to Group Dates in Pivot Tables in Excel. How to Group Numbers in Pivot Table in Excel. How to Filter Data in a Pivot Table in Excel. Using Slicers in Excel Pivot Table. How to Replace Blank Cells with Zeros in Excel Pivot Tables. How to Add and Use an Excel Pivot Table Calculated Fields. How to Refresh Pivot Table in Excel.
