We recently published a tutorial on VLOOKUP, another Excel feature that can help you in data analysis. Like any other good spreadsheet software, Microsoft Excel also provides pivot table feature and it is very easy to use. All you need to do is to understand the concept of a pivot table. In this tutorial, we will explain how to use pivot table with the help of examples. You can download the sample Excel datasheet so that you can easily try the pivot table examples given below. This sample excel sheet contains data about the inventory of CDs in a movie shop.
Insert a Pivot Table in Excel Sheet
To begin the tutorial, we will learn how to insert a pivot table in our sample Excel sheet. NOTE: You may be wondering why Excel does not show a space between the words “Pivot” and “Table”. The reason is that Microsoft uses the compound word PivotTable as a trademark. The term Pivot Table (with a space) is a generic term and any software or vendor can use it but PivotTable™ is a trademark of Microsoft Corporation in the United States.
Adding Fields in Pivot Table
Now that we have created a blank pivot table, it is time to add required fields in this table. Before we do that, we need to understand what we want from our pivot table. So, let’s pose a question! Let’s say, we want to know the total number of movie CDs we have under each category. For example, we want to know how many CDs we have under the Action category. Similarly we want to know the sum for all the movie categories. Can pivot table answer this question? Yes, of course! And you would be surprised how easy it is! Just select Category and Inventory fields from the Choose fields to add to report box. And that’s it! You’ll see that both these fields have been added to the blank pivot table and the Inventory has been summed up according to category! If you click on the dropdown arrow in the Row Labels cell, you can sort the rows and also hide/show any of them.
This was easy, wasn’t it? Now let’s see what else pivot table can do for us. Another question for our pivot table. We want to see the Category-wise sum of inventory where Supplier‘s name is Cosmo Store. So, basically we want to know how many CDs purchased from Cosmo Store are still available under each category. For this, we just need to add a filter in our pivot table
Adding Filter in Pivot Table
Using Pivot Table with Functions like Count, Average, Min, Max etc.
It’s not that pivot table can only do summation for you. Pivot table can summerize data using the following Excel functions: With the help of Min function, for example, we can easily find out the minimum inventory available for Supplier. This can help us in reordering the items. Here we can see in the pivot table that the minimum available items from Tmedia is 1 and from Cosmo Store 4 items are available. The question is answered!
Show more information in Pivot Table
We can add more information in our pivot table to make it more meaningful. In the previous example, we found the minimum number of available inventory from each of the Supplier. But the above answer does not tell us which movies the minimum inventory belongs to. We see that Tmedia’s minimum inventory is 1. So, we should call Tmedia and reorder from them; but we don’t know which movie this “1” belongs to. So, we don’t know which movie is to be reordered. To sort this out, we need to display more information in our pivot table. We need to add the Name field as well. So, we do that by simply ticking the Name field in the Choose fields to add to report box. And the new information would be instantaneously added to the pivot table.
What We Learnt From This Pivot Table Tutorial?
To better understand the utility of pivot tables, let’s take another example. The following data sheet has been taken from Wikipedia:
This data sheet is pretty flat and it does not offer any useful information. But when we pivot this table, we get a simpler table that summerize and simplify the data. For our eyes the following pivoted table is more meaningful as it tells us how many units were shipped on a particular date to various regions.
From this, we learn that an Excel sheet contains flat data… pivoting turns the flat data into information that we can use to make decisions. We hope this tutorial on pivot tables in Excel was useful for you. If you have any questions on this topic, please feel free to ask through the comments section. We will try our best to assist you. Thank you for using TechWelkin!
