Excel Pivot Table is a wonderful feature that is available for excel users. This tutorial is targeted to help people understand pivot table and use them at the right place.
Just like the VLOOKUP feature, pivot table is not known to many people and are often ignored, which I guess is because of the jargon itself isn’t it? Now my goal is to remove that complexity and present you a plain simple example that can help you to quickly understand what exactly a pivot table can give you, sounds good? So let’s get started.
What the heck is a Pivot Table?
When you have a huge list of data in hand and you would like to see some meaning in that data and make it more presentable, then that’s where excel pivot table comes into picture. Normally excel sheets are flat and all you see is huge raw data displayed in rows and columns that is difficult to understand, pivot table can help you to turn these data into meaningful representation.
What good is an explanation without an example?
Sample Data for Excel Pivot Table Explanation
What you see below is a sample data that we are going to use for understanding what we want to learn.
What I have in this sheet is nothing but the expenses of a typical family that is recorded for the period of two years.
Now just take a moment and look at the data, though it gives us all the details about the expenses made by the family members, will you be able to answer the following questions?
- Who is the heavy spender?
- Which type of expense is emptying your pockets?
- How much amount was spent on items purchased?
- Which item was purchased more frequently?
Whoa hold on, I guess I am asking too many questions isn’t it? All these questions can be easily answered using one technique and that’s called Pivot Table.
Creating an Excel Pivot Table
Let’s get our hands dirty by creating a pivot on the above shown data, how else do you think you can learn it?
Perform these generic steps for all the below scenarios:
- Place your cursor on cell C9 as shown in the below screenshot.
- Now navigate to Insert menu, and click on Pivot Table and select Pivot Table option from the drop down.
- Create Pivot Table dialog box will be launched with the data selected. Click on OK button.
- You will get the Pivot Sheet created with Pivot Table field list as shown below.
Who is the heavy spender?
Now that you have the empty pivot sheet ready, let us just go ahead and find out who is the heavy spender first. Check the Spender and Amount check boxes in the Pivot Table Field List and you should see that spender is placed under Row Labels section and Sum of Amount (in $) is placed under Values section.
You can actually rename the column heading Row Labels into something meaningful by just placing the cursor on the header and typing the name you want. You should now be able to identify who the heavy spender is.
Which type of expense is emptying your pockets?
Check the Expense Type and Amount check boxes in the Pivot Table Field List and you should see that Expense Type is placed under Row Labels section and Sum of Amount (in $) is placed under Values section.
You should now be able to see which expense type is emptying your pockets.
How much amount was spent on items purchased?
Check the Item Purchased and Amount check boxes in the Pivot Table Field List and you should see that Item Purchased is placed under Row Labels section and Sum of Amount (in $) is placed under Values section.
You should now be able to see how much amount was spent on each of the items.
Which item was purchased more frequently?
Now this can be a little tricky, not that much tricky as you think. Add the same Item Purchased field in both Row Labels and values section to get the result you want.
Well you can do umpteen things with Excel pivot table and what we have seen here is just a tip of the iceberg.