Google Sheets is a handy cloud-based program you can open on your Chrome browser as long as you’re an existing Google or Gmail user with a requisite account. You can learn how to make a pivot table starting with the right data type.
A pivot table allows you to make at-a-glance tables and reports. This tutorial covers the basics of making a pivot table, categorizing by date, how to fill rows and columns, and how to fill the body with relevant data.
What is a Pivot Table?
In simple terms, a pivot table is a more comprehensive version of a table with sums and averages included as well as stacked information or an aggregation function that applies to specially grouped values.
An electronic spreadsheet at its simplest presentation is just a set of rows and columns grouped together to form a grid of cells or the meeting between rows and columns. You can log data there for grouping or apply formulas to derive further data from your raw data.
As the spreadsheet grows, you need to use more comprehensive methods to present your data like graphs, charts, and tables—specifically the information-packed pivot table.
A pivot table is a more extensive version of your normal table composed of grouped values that aggregates the individual items into one or more discrete categories. It’s also known as the table that includes statistics like averages and sums with it.
How to Create Pivot Table in Google Sheets?
Step 1: Open Google Sheets and create a new spreadsheet.
Step 2: Create a table with multiple types of data or copy and paste an existing table with the data already included. We’ve specifically chosen the latter option.
Step 3: Go to the “Insert” menu and select “Pivot table“.
Step 4: The “Create pivot table” box will appear asking you to confirm if your data selection range is correct and whether you want to open the pivot table on the same sheet or a new sheet.
Let’s select new sheet and then press the “Create” button or Enter.
Step 5: You’ll now get an empty pivot table on a new sheet you can edit and elaborate on in any way, shape, or form you can using the Pivot table editor sidebar.
Step 6: Go to rows and click the “Add” button.
For now, let’s choose “Bluetooth brand“.
The “Bluetooth Brand” should now appear as a column on the left. They’re arranged in Ascending order (alphabetical, from A to Z). So far so good.
The properties box of that column is on the Pivot table editor sidebar, by the way. You can uncheck the “Show totals” checkbox for now, since the info contained on that column are names of Bluetooth products rather than figures and stats.
Step 7: Add another row (header) and column of data. This time, we’ll add “Price“.
You can now see the difference between an ordinary table with a pivot table with the way it stacks data and always asks you to put in totals and sums.
If the brands featured multiple prices per brand, you could sum them up there or do something else.
Step 8: We can layer info on your pivot table by choosing to add columns instead with the row representing the Bluetooth brands.
Now go to value and press the “Add” button there. You’ll get a cross-referenced checklist of which Bluetooth data applies to which product now.
Step 9: The power of the pivot table is that you’re able to mix and match figures to figure out stuff about what you’re buying based on a basic table.
For instance, I’ve put in “Connectivity” on the Columns part of the pivot table and “Price” on the values part.
Now I have a better idea how much the “Wi-Fi (802.11 b/g/n, 2.4 GHz, and 5GHz) and Bluetooth 4.2” feature will cost me compared to just Bluetooth, Wi-Fi and Bluetooth, or a specific Bluetooth version.
Curiously, the pivot table fell apart when I tried to compare the “Weight” category relative to the “Price” value. It’s in ascending order (in order of magnitude), but it put the 13-pound UE Hyperboom in the middle of the table, reading it like it’s 1.3 pounds instead!
Still, it’s curious to see how size isn’t necessarily proportionate to price, with $179 Sonos Roam costing more than the heavier $126.49 UE Boom 3.
To Sum It All Up
The pivot table groups together stats using an aggregation function that applies to the grouped values. This gives your pivot table more info at a glance compared to a simpler table where you have to parse the data further to get sums and averages from it.
To make a pivot table, select your table, just go to the “Insert” menu, pick the “Pivot table” option then edit your pivot table using the sidebar pivot table editor.
References:
- John Thomas, “How to Use Pivot Tables in Google Sheets“, Zapier.com, September 18, 2018
- Simpletivity, “How to use Pivot Tables in Google Sheets (Tutorial)“, YouTube, October 8, 2020
- “Create and edit pivot tables“, Google.com Support. Retrieved June 24, 2022
- Prolific Oaktree, “Google Sheets Pivot Tables – Basic Tutorial“, YouTube, June 23, 2018
Google Sheet: Make a copy