Creating the pivot table is only the first part of getting your data to the way you need to see it. Once you have a pivot table created, you need to manipulate it.
If you didn’t read our tutorial on Creating the pivot table, you can read it here.
First, let’s start off with our data. I will use the same data from our earlier tutorial on Creating the Pivot Table. Here is the data. It is a table of the top athletes in the 2012 Olympics and their gold medal count:
We created the following simple Pivot table. It is a summary of Gold medals won by country.
First, we want to summarize the gold medals by Country and by Sport. We can do this by using multiple criteria in the Rows.
To do this, we want to move the Sport field over the the Row Labels and put it right below Country.
We will then get a pivot table that has each Country on the left side with the Events right underneath them .
Now that we have our data compiled the way we want, we want to manipulate it to look how we want.
Formatting the Style of your Pivot Table
When you have your cursor on your pivot table, you get the Pivot table toolbar to show up at the top of your screen. To adjust the style of the pivot table, click on the Design toolbar.
On the right of the toolbar should be the Pivot Table Styles. Click on the drop down on the right.
You will get a large dropdown box with all the different styles of pivot tables you can choose. You can also choose a New Pivot Table style which lets you create your own. Just pick which one you want and your pivot table will adjust.
Adjusting Subtotals and Grand Totals of your pivot table
Right now we have the following Table. It lists all the gold medals won by each country and the sport. This table also shows the grand total of gold medals and also shows subtotals for each Country.
You can choose to display the subtotals or grand totals on your pivot table. There are buttons on your Pivot Table Tools toolbar on the left side. Put your cursor on the pivot table and go to Design under Pivot Table Tools. You will see the following buttons.
Click on the drop down for Grand Totals.
From here you can choose to Turn off the Grand Totals, Turn them on for everything or just turn them on for for Rows or Columns.
Additionally, you can turn the subtotals on and off from the same menu.
From here, you can decide if you want subtotals or not.
The following is how it would look if we turn off Subtotals and Grand Totals.
Changing the Pivot Table Values being displayed
Right now we have the sum of gold medals being displayed in our pivot table. However, we can change that if we wanted. We can change this to show the count of people with gold medals, or the average or other calculations.
To do this, put your cursor on the pivot table and then go to Options under the Pivot Table Tools at the top of the screen.
From there, click on the dropdown on the Summarize Values By button.
From here you can see it is set as Sum, so it adds the gold medals. But you can change it to Average or Count or so on.
Additionally, you can turn the numbers into percentages of totals or some other item. To do this, go to the Options button again but go to the button called Show Value As and click the drop down.
Here you can see it defaults to No Calculation, so it will show the actual numbers. You can also choose to change the numbers to percentages of different Totals like Grand Total, Row Total, Running total and so on.
If we choose to do % of Grand Total, we would get the following.
Everything stays the same except the values now become percentages of the Grand Total. So instead of showing a 2, it shows 3.92%. That is 2 divided by 51 total medals.
Refreshing Pivot Table Data
Pivot tables are created from regular table’s data. When that data changes, pivot tables can change. You can refresh the pivot table to show the updated values.
Let’s say we have our table here:
If we change the first row of data for Michael Phelps from 4 to 7 medals, our pivot table goes from this:
It updated the data after we refreshed it.
To Refresh the Data, you can do it in two ways.
First, right click on the Pivot table and drop down to Refresh.
Or second, put your cursor on the pivot table and go to the Pivot Table Tools, Options and then over the Refresh.
So that concludes our tutorial about manipulating your pivot table. There are still many other ways to manipulate it, however, just knowing these steps above can greatly improve your ability to use pivot tables.
I hope this way helpful! Please share if you found it useful!
*** IF you found this useful and was looking for more help with pivot tables, Check out the Power Pivot Course here