How To Use Pivot Tables in Google Sheets?

This tutorial will teach you how to use pivot tables in Google Sheets.

Pivot tables are a powerful tool in Google Sheets that allow you to summarize and analyze large sets of data quickly and easily. In this tutorial, we will guide you step-by-step on how to use pivot tables in Google Sheets.

How To Use Pivot Tables in Google Sheets?

1. Prepare Your Data

Before you can create a pivot table, you need to make sure that your data is in the correct format. Your data should be organized into columns and rows, with a header row at the top. Each column should have a unique name, and there should be no blank rows or columns within your data set. 

Before we proceed with the example, we will prepare a sample dataset. We just created a simple location-wise sales quantity dataset with a few rows and columns to see how pivot tables actually work.

Our dataset is well organized with no empty rows or columns. So, time to move to the next step.

2.  Creating a Pivot Table

To create a pivot table in Google Sheets, you need to follow these steps

Select the range of data you want to analyze. 

I selected the whole data. If you want to choose a portion of it, you can do so. It’s merely up to you. Make sure to include headers as it is the reference point of the pivot table.

Then, click on the “Insert” option from the menu and select “Pivot Table” from the dropdown list.

This will open a dialogue box of the pivot table.

The range of the pivot table is already selected. You may choose to add a pivot table on the same sheet or on the existing one. It is recommended to add it to a new sheet. 

Click on “Create” to Proceed.

A pivot table menu will appear like the one shown in the picture. You can add values to rows and columns by just dragging the values.

The zone and area are dragged to rows and sales Qty is dragged to the values section. It created a pivot table which is telling us the sales quantity zone-wise and area-wise. You can play with the drag option to create a perfect pivot table for you. 

3. Customize Your Pivot Table

You can customize your pivot table by changing the summary function, sorting your data, and adding filters. To change the summary function, click on the “Values” section of your pivot table and select the summary function you want to use from the dropdown list. To sort your data, click on the column you want to sort by and select “Sort A-Z” or “Sort Z-A” from the dropdown list. To add filters, click on the “Filter” section of your pivot table and select the filter criteria you want to use.

How To Add Filters on Pivot Table

To add a filter we will just drag the zone into the filter option. 

We just dragged the zone in the filter option and just selected East and North and unchecked the rest two options. Now the pivot table shows us the values of Urban and Rural for East and North only.

One can always try other customization of sorting as well. It’s pretty straightforward.

4. Refresh Your Pivot Table

If your data changes, no need to refresh your pivot table to update the results. The data will be updated automatically.

A few of the sales values were altered and the pivot table was updated automatically without refreshing. 

In conclusion, pivot tables are a useful tool in Google Sheets that allow you to quickly analyze large sets of data. By following these simple steps, you can create and customize your own pivot tables in Google Sheets to make data analysis easier and more efficient.