How to create a pivot table in Google Sheets
When it comes to making spreadsheets, there are different tiers of users. At the bottom is data entry. This is having enough knowledge to organize your information in a table for record keeping. One step up from this are the users that can perform basic manipulations on that data, such asfinding sums and averages.
But sometimes, the data in a spreadsheet can be so extensive that it’s impossible to extract any meaningful information. To overcome this hurdle, take the next step and harness the power of pivot tables. And if you’re trying to harness the power of thebest Chromebooks on the market, we’ve got you covered.
What is a pivot table?
A pivot table is a semi-automated tool that presents custom summarizations of large sets of data to make sense of it. Before pivot tables, even if you knew how to crunch your data, you still had to input the formulas and format the output manually so that it made sense to the VPs.
Pivot tables take that work out of your hands and let your computer handle the heavy lifting. To understand what pivot tables are capable of, let’s play with a data set.
How to make your first pivot table
To start, navigate to Google Sheets and open the spreadsheet you want to work on. This example uses a coin collection because that’s more interesting than looking at monthly revenues and expenses.
There are two ways to generate pivot tables in Sheets. You can either use the Explore button in the lower-right corner of the browser window or the Insert menu from the ribbon menu at the top of the page.
Make a pivot table using the Explore button
Congratulations, you just made your first pivot table! Time to upgrade your resume. The sheet where you inserted your pivot table looks different from the sheet with the source data. The difference is thePivot table editorpanel on the right.
Make a pivot table using the Insert menu
You’ll see a new pivot table that’s similar to the one made using the Explore button, only it’s empty. That’s because the first method we used made some guesses about the kind of information we wanted to extract from our data set. Using the second method doesn’t make any assumptions, so we have to tell Sheets which data we want to summarize and how we want it summarized.
How to manipulate pivot tables in Google Sheets
The insight discovered by Pito Salas, the inventor of pivot tables, was that spreadsheets were made up of three parts: input, output, and formulas. Pivot tables make managing these three elements easier by taking the grunt work of integrating them and hiding them under a layer of abstraction.
At the right of the window is thePivot table editor. This is where you manage which inputs, or groups of cells, you want to use in your table and which formulas you want to use to manipulate that data. On the left is your spreadsheet with your pivot table. Unlike a regular spreadsheet, you won’t directly put any information in the cells. That process is handled automatically based on how you use the Pivot table editor.
You’ll see the cells in the first column of the pivot table filled with the unique values of theFace Valuecolumn from the original data set. There’s also a new box underRowsin thePivot table editor.
This is a good start, but we need to do something about the categories of coins mixed in with the face values.
This leaves a list of the unique face values in the collection, which isn’t very interesting and doesn’t reveal anything that was hidden in the original data.
To uncover some of the power of pivot tables, you’ll need to add some values.
At first glance, what you’re looking at might not be obvious. So, widen column B to see what it says.
COUNTA is a Sheets function that looks at a range of cells and outputs how many cells in that range have anything in them. In this case, the pivot table displays the number of cells in the Face Value column of the original data that have those corresponding values. In other words, there are nine pennies in this collection, 10 nickels, four dimes, 117 quarters, seven half dollars, and 31 dollars.
This is pretty neat, but part of the power of pivot tables is how easy it is to refactor data.
With just a few clicks, we’ve gone from counting the coins in the collection to getting the total face value. Let’s add one more field to see how much information can be squeezed out of the original data.
This shows how many coins are in the collection and the distribution of the quality of the coins across the different face values.
Making pivot tables work for you
Pivot tables weren’t made for nerds to geek out on their coin collection. Pivot tables are a powerful tool for businesses that want to make sense of the vast amount of data they collect. People put proficiency with pivot tables on their resumes because businesses value employees that know how to use them.
This tutorial is far from being the definitive guide on pivot tables. There are tons of tricks to make them do what you want with minimal effort. Still, what we laid out is more than enough to start using them and figuring out how to make pivot tables work for you.
The note-taking app I should have used all along
Broader branding hints at wider paid-tier ambitions
M3 Expressive now refreshes the chat screen
It’s time to sniff out the culprit
This article is sponsored by Total Wireless.
Get 14 ports for $170
