We bring up spreadsheets quite often here at Tech Tip Tuesday World Headquarters, and we've covered a number of Sheets-related issues in the past, including...

But we've been holding out on you! That's right, despite dropping all this knowledge, we still have barely even mentioned Sheets' most powerful feature! Bwahahahaha!

Oh yes, it's finally time to reveal the most powerful spell in the arsenal of the Spreadsheet Sorcerer: The Pivot Table.


Prepare to have your mind blown by my spreadsheet skillz


Wait, what's a Pivot Table?

Pivot Tables have long been the ranked (by me) as the most useful spreadsheet feature...that surprisingly few people know about. Pivot Tables have been included in Excel since 1994 which, let's just acknowledge, was a LONG time ago. The Sign by Ace of Base was the #1 song of that year. I routinely meet people who interact with spreadsheets all day long as a part of their job who have never used this feature. I blame this squarely on the name.


Yes, Pivot Tables are tables of data, but that doesn't really tell you what they do. A Pivot Table is really a Summary Table. It takes a large data set and spits out a summary of that data in a much more digestible format. While both Sheets and Excel include Pivot Tables, I think the Sheets implementation is easier for beginners. Excel, on the other hand, offers more options for customization so it might be a better choice if you're trying to create a really fine-tuned summary of a complex data set.


An Example: TSS Stream Team Data

Here's an example data set: the TSS Stream Team water quality database from 2006-2012. #citizenscience! If you want to follow along with the examples in this Tech Tip, click on this link to make a copy of my example workbook.


So, we have this data set and we want to generate a more digestible summary of it. To create a Pivot Table, select the full range of the data, then open the Data menu and select "Pivot table...". Pro-tip: to quickly highlight a large data set, select the cell in the upper-left corner (in this case, cell A1), then tap Ctrl + Shift + Down Arrow, then Ctrl + Shift + Right Arrow. 



As you can see, Sheets will create your Pivot Table in a new spreadsheet. It's not going to look like much, just a blank field. You need to use the right-hand sidebar (the Pivot table editor) to customize your table.




It helps if you have a vision in your head for what you want your summary to look like. 

For instance, lets say I'm interested in comparing the pH of different streams. This means I probably want to use pH as my Value. 

ProTip- if you have a mix of discrete and continuous data that you want to summarize, you usually want to choose your continuous data as your Value. I do this by clicking the ADD button next to "Values"...


..and then I'm going to choose pH from the drop-down menu. I have some choices about how I want to summarize the data. For this example, it makes sense to look at the average pH, so I'm going to select 'AVERAGE'.


Notice that my Pivot Table is now summarizing my data into a single number: I have the average pH (7.479) for the whole data set. Of course, I want more detail. I'd like to break that number down to see the average pH in each stream that was tested. To do that, I'm going to click on the ADD link next to 'Rows' and choose "Stream":



Now, my Pivot Table has just changed again and I can see the average pH for each stream:



This is more interesting. If I want to break these numbers down more, I could look at the average pH in different seasons. I'll click on the ADD button next to columns, then select season, and...


Boo-ya! In just a few short steps, I have a useful summary of the average pH in each stream, in each season. So, that covers the basics of how to create a Pivot Table. But, of course, if you want Sheets to create a table for you...




The Explore Feature

I've written about the Explore feature before, Google's AI assistant that helps you wrangle your data. Explore was recently upgraded so that it now will suggest a Pivot Table to you based on whatever data it's looking at. Here's how it works:


I'm going to navigate back to my sheet of stream team data. This time, I'm going to open the Explore sidebar by clicking the Explore button in the bottom-right corner (or by using the keyboard shortcut Alt + Shift + X). If you look at the sidebar, you can see that Explore is already suggesting a Pivot Table for me to view:



If I hover over this suggested data summary, I'll have two buttons available; a preview button and an 'insert' button. The second option will create a Pivot Table summary in a new sheet, which you can then customize as needed. It's worth mentioning that Pivot Tables can be formatted and customized just like any other cells in Sheets, with custom fonts, colors, and borders:


Gotchas and Pitfalls of Pivot Tables

In no particular order, here are some of the issues and restrictions to be aware of when using Pivot Tables.

  • Pivot Tables like 'Clean' Data. This means your original data set needs to be standardized, consistent, and boring for Pivot Tables to work effectively. If you have subtotals or or other summary data included in your data set, a Pivot Table will include those subtotals when making calculations and give you some incorrectly-large sums. If you have addresses where the State field alternates between abbreviations (WY) and names (Wyoming), the Pivot Table won't recognize those as the same state. If you're summarizing donor data and you have an entries for both "Boaty McBoatface" and "Boatie McBoatface", the Pivot Table will treat these as different people because of the spelling difference. Take a few minutes to clean up your data before you start going all crazy with the pivot tables; it'll be worth it in the long run.
  • The Value Field is Tricky. I'll reckon that most new Pivot Table users all get stumped by Value at some point or another. The challenge is to choose the correct math operation that you're going to use for summarizing your value data. Chances are, you're going to be using SUM, AVERAGE, or COUNTA (for counting the number of entries with non-empty cells) as your go-to workhorses.
  • Pivot tables use a lot of processing power. There's a lot of CPU cycles that go into summarizing all that data into your compact summary table. This means that the more pivot tables you have in a Sheets workbook, the slower it will load and run. Especially with large data sets, try to limit yourself to just one or two pivot table sheets or your Sheet will have noticeably performance problems.
  • Pivot Tables in Excel need to be Manually Refreshed. This is probably the biggest reason I recommend Sheets over Excel, at least when it comes to Pivot Tables. Excel pivot tables don't automatically re-calculate when you make a change to your data; Google Sheets do. This makes Excel sheets more efficient at handling large data sets (because it's not constantly re-calculating the summary data in the pivot table), but it also means that you have to remember to right-click on your pivot table and choose 'refresh' every time you make a change to your source data. It's very easy to forget this.
  • Pivot Tables do Descriptive Statistics, not Inferential Statistics. Pivot Tables do a great job of generating descriptive summaries of your data, but it's important not to put too much stock in the data they generate. A Pivot Table can help you identify trends, for instance, but it can't tell you how significant those trends are. My Stream Team Pivot Table shows me that the average Spring pH for the Gros Ventre River is 7.5, which might give me some confidence to draw conclusions based on that number. What the Pivot Table doesn't show me, however, is that it's generating that number from ONE SAMPLE. Pivot Tables only summarize the data that you already have, but they don't draw inferences about broader trends or the significance of a data relationship.
  • You can Choose Multiple criteria for Columns, Rows, and Values. You can get really granular with your data if you want to, choosing multiple criteria for columns and rows. It's easiest to illustrate this with an example. Let's say I have some data about deer harvests on a plot of land I manage. I want to see a breakdown of how many deer were harvested with different implements (rifle, bow, muzzle-loader) each year, broken out by number of male and female deer harvested. Here's what that would look like (I'm choosing two ROW criteria: year, then sex):



The Promised Land

Despite these potential pitfalls, Pivot Tables are still one of the most powerful tools available to a spreadsheet sorcerer. Whenever you have a large spreadsheet that you need to summarize quickly, your mind should go straight to 'Pivot Table!!" For instance, I know that quite a few folks in the organization use Pivot Tables to generate budget projections. Put all of your expected, coded expenses into a spreadsheet, then generate a pivot table to tell you how much you're expecting to spend in each expense line (or project or campus or whatever dimension you want):




They're also good for summarizing inventories. I can use a Pivot Table to generate a summary of all the computers that the organization has owned, organized by how much RAM each one has:


Or maybe I'm curious about the breakdown of cell phones with TSS plans. I can download the data from our phone provideras a spreadsheet, then use a Pivot Table, then turn that data into a chart of how many iPhones are deployed on the TSS plan (the iPhone 6s is currently the champ, but the iPhone 8 is gaining):



I'm sure you can think of many, many other ways that an automatically-updating summary table or chart could be useful in your work life. Good luck!