1. Additional Options for Pivot Tables
We've covered many of the basic features of pivot tables, but there are still some more capabilities built into Google Sheets; we'll cover some of them briefly here.
2. Showing data as percentages
By default, the data in your pivot table is shown as whatever calculation you choose, whether that is SUM, or COUNT, or something else. But what if you want to see how much each category contributes to the total? You can show data as percentages in order to accomplish this. In this example, you can see how much was budgeted in each category in this dataset. If you go to the pivot table editor, in the values section, click on the drop-down where it says Show Data as Default. Select percentage of grand total. Now we'll have to format the numbers as percentages, and you can see that PG-13 movies receive over half of the total budget of all movies. If you have both Rows and Columns selected, then you can choose percentage of row or column for further insights.
3. Drilling down into your pivot table data
By now, we have seen many examples of how pivot tables can summarize and simplify a large dataset. But sometimes, you'll still want to see the full detail, and not just the subtotals, counts, or averages that you'll find in the pivot table. A great way to do this is by drilling down.
4. What is drilling down?
Drilling down is the process of pulling the full details for a specific selection of data. For instance, what if we are interested in the PG-rated films from 2012, and we want to further explore that selection. We can drill down by double-clicking on the number 26. Once you do, you'll see that the full details for those 26 entries have appeared in a new sheet. You'll have all of the same columns as your original dataset. This is just a sub-set of that original data.
5. Where can you drill down?
Drilling down will work on any of the Value cells in the pivot table. Here you can see that the total Gross Revenue from Spanish films is 80 million dollars. If we drill down on the 80 million dollars, then we can see the films that make up that number.
6. Grouping your data into usable ranges or categories
Finally, Grouping can be useful to see how many items fall within certain ranges. For instance, let's set certain ranges of Gross Revenue and see how many films fall into each bucket. We'll start by putting the Gross Revenue in the Rows section, and the COUNTA of titles in the Values section. Right now the pivot table lists every single unique Revenue number individually, but this can be improved. We will right-click on any of the numbers in the Rows section, and choose "Create Pivot Group rule". Now we can decide how to group the data. Let's set the interval value at 100 million dollars so that we can see how many films fall between 0 and 100 million dollars, 100 million dollars to 200 million dollars, and so forth. Hit OK, and now you can see how many films made it into each category.
7. Let's practice!
Now let's try some examples of each of these.