When handling a lot of data in Microsoft Excel, sometimes you need to summarize them and compute instant calculations. That’s where pivot tables come in. If you have not created a pivot table before, this article a great way to start you on your way.
What is a pivot table?
A pivot table is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail. A PivotTable is like a user-friendly and instantly customizable table to summarize large amounts of data.
How do I create a pivot table?
First, you should have a table of data complete with proper headings like the one shown below.
As you can see in the example above, we have different columns like Agent Name, Team Name, the Total Sales for each month, and the Total Contacts Closed for each month.
What if we want to summarize the total sales amount of each Team for each quarter?
The long and tedious way is to create a new table, then use formulas to compute the data that you need to display.
Using pivot tables would be easier.
Step 1. Select your table.
Step 2. On the top section of your scree, click on Insert, then PivotTable, then PivotTable.
Step 3. On the dialog box that will show up, choose where you want your pivot table to show up. I have selected New Worksheet for this example, so that MS Excel will create new sheet (i.e. a new tab near the bottom of the screen) where my pivot table will be displayed. Then, press OK.
You will then see a blank pivot table.
On the left of the screen:
On the right side of the screen:
Step 4. On the right side of the screen, choose the columns that you want to use in your pivot table.
Since we are creating a report that shows the sales of each Team, we will select Team Name and drag it to the section named Row Labels. Yes, you can drag and drop these things!
We also want to display the Q1 Sales and Q2 Sales of each Team, so we drag and drop Q1 Total Sales and Q2 Total Sales to the section below named Values.
This is how the bottom right section of the screen will look like after the drag and drop actions that I did.
And just like magic, the pivot table will now be updated to show this:
But we’re not finished yet as we want to show the breakdown of sales of each sales agent under each team. So…
Step 5. We go back to the right side of the screen and select Agent Name and drag and drop it into the section below named Row Labels.
The pivot table will now be updated to this:
Step 6. Make our table look beautiful. Since we’re dealing with sales figures, it would be nice if we could format the numbers so that they are easier to read with commas. So I select all the numerical data, go to Home, then in the Number section, I click on the comma symbol.
And voila, here is our pivot table!
I told you at the start of this article that our pivot table is INTERACTIVE. That means we can easily change what we want to display.
For example, instead of total sales, what if we want to display the average number of contracts closed by each team each month? We did not compute for this in our original data table, but the pivot table will be able to help us with this.
So we just drag and drop the headings we need for our pivot table.
But this time we need the average instead of the sum. So we click the little black triangle at the side…
…and click on Value Field Settings. Then a dialog box will pop up. Instead of Sum, we select Average from the list.
Press OK. Then, repeat this for Feb, Mar, and so on. Then make it easier to read (see Step 6 above).
And that’s it. That’s the basics of creating and using pivot tables. Explore them a bit more by yourselves to master it.