A table in Excel is a collection of data, which can be very large, with many rows and columns. A Pivot table is a compressed summary of that data from the table. It can be changed to your liking and updates as the data in the table updates.
I will go over a summary on how to create a pivot table and manipulate it.
Here is some sample data that we will use for our example. It is a list of the top 25 gold metal athletes in the 2012 Olympics.
To create the pivot table, put your cursor anywhere on the table. Then go to Insert then Pivot Table. You want to first be sure there are no blank rows in the data.
You will get the following Wizard to show up:
You have two main parts here to fill in:
Choose the Data: Here you select your table of data or an external source. We want to use the table from above, so we fill in the table name. This was filled in for us already and it is Table1. You could also put the actual range of the table, which would be A1:E25.
Choose where to place the Pivot Table: You get to choose New Worksheet or a spot that you choose in an Existing worksheet. We chose New Worksheet.
Now you will have a new worksheet that looks like this:
The box on the left is where the pivot table will be built. The wizard on the right is where you can drag the column headings to build the pivot table.
So let’s build the table now. We want to create a summary that shows the total Gold metal count by country.
We want to first take the Country header from the top part and drag it down to the Row Labels box. This will then put it on the left side of the pivot table.
Next we drag the Gold Medals to the Values box.
We now have a pivot table which summarizes the gold medals by country.
In addition, we can add more dimensions to this pivot table. Say we want to also summarize this by the sport they won the medals in.
We could drag the Sport field down to the Column Labels:
It will then give us the following pivot table:
Instead of adding Sport to the Column Heading, we can also filter this by Report. To do that, drag Sport out of Column Labels and move it to Report Filter in the Pivot wizard:
It will give you this pivot table then:
From here, you can click the drop down box to the right of Sport and it will show you all the sports. There you can pick which one or ones you want.
EDITING AND FORMATTING THE PIVOT TABLE
Now we want to change the data around and the layout. First let’s show how to format the data.
Let’s format the value, which is the number of Gold medals. To do this, click on the upside down triangle on the right of Sum of Gold Medals. You will get the following :
So click on Value Field Settings and you will get the following to pop up:
From here you can choose how to summarize your values. Excel defaulted it to Sum when we first did our pivot table. So that means it added up all the numbers (Gold medals) and put them in the pivot table.
We could change it to Count or Average or Max or many more options, depending on what you are looking for. However, in this case, leaving it as Sum is the best option.
CHANGE THE NUMBER FORMAT
We can also change the format of the number. Right now they are whole numbers without decimals or commas. But if you press “Number Format” from the wizard, you can change that.
You will then get the following to pop up:
The number is now set as General, but you can change it to whatever number format you want. You can change it to number with decimal places or add in commas or make it a percentage. But again in this case, we will leave it as a general number as that works best here.
CHANGE THE FIELDS IN THE ROW LABEL
Finally, you can change the fields in the Row Labels to exclude certain countries if you want. Right now we have this:
You can click the drop down to the right of Row Labels and get a list of all the fields or in this case “countries” that are in this list:
You can uncheck the box to the left of some of the countries and it will take them out of the pivot table. Say we removed the check next to Jamaica or South Korea. It will leave us with the following pivot table:
So this concludes my tutorial on setting up a pivot table and editing and formatting it. Check out my next blog post about Manipulating pivot tables here.
*** IF you found this useful and was looking for more help with pivot tables, Check out the Magic of Pivot Tables Course here