Pivot tables let you analyze large amounts of data and narrow down large data sets to see the relationships between data points. Google Sheets uses pivot tables to summarize your data, making it easier to understand all the information contained in your spreadsheet.
What Are Pivot Tables?
Pivot tables are handy for analyzing massive amounts of data. Where a regular spreadsheet uses only two axesâcolumns and rowsâpivot tables help us make sense of the information in your spreadsheet by summarizing any selected columns and rows of data. For example, a pivot table could be used to analyze sales brought in by divisions of a company for a specific month, where all the information is randomly entered into a dataset.
Creating a pivot table from the information in the picture above displays a neatly formatted table with information from selected columns, sorted by division.
How to Create a Pivot Table
Fire up Chrome and open a spreadsheet in Google Sheets.
Next, select any of the cells you want to use in your pivot table. If youâre going to use everything in your dataset, you can click anywhere on the spreadsheet, you donât have to select every cell first.
Note: Each column selected must have a header associated with it to create a pivot table with those data points.
On the menu bar at the top of the page, click âData,â then click âPivot Table.â
If the new table doesnât open automatically, click âPivot Table,â located at the bottom of your spreadsheet.
How to Edit a Pivot Table
From the pivot table sheet, the side panel lets you add rows, columns, values, and filters for viewing your data. Sometimes, Sheets offers up suggestions based on the information you chose. Click a suggestion or click âAdd,â located next to any of the other options below.
When you click on any of the suggestions, Sheets automatically builds your pivot table using the option you selected from the list given.
If youâd rather customize a pivot table to for your own needs, click any of the âAddâ buttons next to the four options below. Each option has a different purpose, hereâs what they mean:
- Rows:Â Adds all unique items of a specific column from your dataset to your pivot table as row headings. They are always the first data points you see in your pivot table in light grey on the left.
- Columns:Â Adds selected data points (headers) in aggregated form for each column in your table, indicated in the dark grey along the top of your table.
- Values:Â Adds the actual values of each heading from your dataset to sort on your pivot table.
- Filter:Â Adds a filter to your table to show only data points meeting specific criteria.
Click on âAddâ next to Rows and add in the rows you want to display in your pivot table. For this example, weâll be adding division and subdivision.
Next, click âAddâ next to Values As and insert the values you want to sort information. Weâll be using the sum of the number of units sold and the average price per unit.
To change the sorting of each unit, click the drop-down menu, located under the heading âSummarise by.â You can choose from the sum, count, average, min, max, among others listed below.
After adding all rows, columns, values, etc. what weâre left with is an easy to read pivot table that outlines which division sold the most units and the average cost of all units sold.
If youâd prefer to make your own formula, click âAddâ next to the Values as heading, then click âCalculated Field.â
From the new value field, enter a formula that best summarises the data in your pivot table.
If you want to add a filter to your table, click âAdd,â located next to the Filters heading.
When adding a filter to your table, selectâor deselectâthe values you want to show on your table, then click âOKâ to apply the filter.
Thatâs all there is to it. Although this is just an introduction to using pivot tables, there is a seemingly endless amount of possibilities for utilizing this feature that not many people know much about.