How to Use Pivot Tables in Google Sheets

Implementing a pivot table in Google Sheets will compress your spreadsheet data into a custom table. As such, the tool is handy and convenient for those who want their data described in an easy to understand format.

Say you’re a student or someone who regularly uses Google Sheets and you want to refer to an abbreviated version of all your data. Look no further than Pivot Table. Even if you don’t have a detailed spreadsheet, you can still use the feature to enhance this sheet by making it more accessible.

Using the PivotTables Tool

Step 1: Select the data that will be added to the pivot table.

2nd step: Now click on the Insert tab > Pivot table.

The pivot table option in Google Sheets.

Step 3: Choose where you want to insert the pivot table via the new sheet and Existing sheet options. I selected new sheet for this demonstration. Click it Create once you have selected a location.

Choose where to insert a pivot table in Google Sheets.

Step 4: Google will now create a new sheet on which your pivot table will be based. Ideally, Google Sheets provides a Suggested section where they will do most of the work.

However, in some cases, the algorithm won’t be able to suggest anything, as is the case with mine.

The suggested section for the pivot table editor in Google Sheets.

Step 5: But don’t worry: you can fill in the pivot table yourself and customize it to your needs.

There are four components that will determine what your pivot table will present. In this demonstration, I used a variation of it.

I used the Add button for Lines to insert the Month, Revenue, and Profit sections of my spreadsheet. You will need to use the Add button to add a specific column or row. In my case, I clicked Add button three times to Lines to include the Month, Turnoverand Profit.

Adding rows in Google Sheets for a pivot table.

Step 6: Within the Values section, I chose Revenue again, in addition to adding Profit. I chose the SUM possibility in the Summarize by section, as well as the selection % of column in the neighbor Show as region.

On my pivot table, I can now see which month accounted for which percentage of overall revenue and profit. With this I can determine, for example, which months the company should pay attention to and others it can improve.

Added Values ​​section as for adding data to a pivot table in Google Sheets.

Step 7: So if you want to insert the pivot table into the main worksheet, there is no need to Revenue and Benefits more lines. Delete them by clicking on the X on its section, then repeat the above steps to add a pivot table in the main worksheet where the data is.

If you have many years of revenue and profit and implement the pivot table as explained above, you can quickly browse the table itself to see which month generated the most revenue and profit instead of manually browsing all the data to find the answers.

As you can see via the image below, without even looking at my original data, I can determine that July had the highest revenue and profit compared to all other months.

Using a variation of different fields will allow you to see what’s relevant and what’s not, so don’t worry if you don’t get it right the first time. For example, you can use the Medium option in Values to calculate the average value of certain data.

With that in mind, Pivot Tables can turn out to be an extremely useful feature of Google Sheets.

A pivot table added to an existing spreadsheet in Google Sheets.

How do I update a pivot table in Google Sheets?

Step 1: You don’t need to update anything regarding the pivot table once it has been created and you have populated it. Just make changes in the cells you selected to form the pivot table. Google Sheets will do the rest by updating the PivotTable to implement the changes.

2nd step: For example, I will change the value of two of my cells to a higher amount of income and profit. As you can see in the pivot table, it updated the whole table next to those two fields.

Updating a pivot table in Google Sheets.

To learn more about Google Sheets, check out our guides on how to merge cells and create a chart or table.

Editors’ Recommendations






Comments are closed.