How to Make a Google Sheet Pivot Table [Step-by-Step]

The concept of pivot tables in Google Sheets may seem confusing at first, but once you get the hang of it, you’ll find your data analysis skills rise to a different level. This tutorial is aimed at not only introducing Google Sheet pivot tables and their use to beginners, but also helping intermediate-level users up their pivot table skills with some additional tips and tricks.

What is a Google Sheet Pivot Table and Why do I Need it?

A pivot table is a table composed of columns and rows that can be moved around (pivoted), allowing you to group, isolate, expand and aggregate your data in various ways, and in real-time.

As such, pivot tables help you analyze your data through various angles, giving you deeper insights into relationships between elements of your data.

If that sounded complex, let’s break it down with an example.

Take a look at the data set below:

 sample data set

The above dataset is large, looks complex, and it’s quite hard to make any sort of inference from it. A pivot table could help you condense a data set like this to get a better view and understanding of relationships in the data.

For example, a pivot table could help you answer questions like:

  • How many countries are there from each region?
  • What is the average number of units sold for each item type?      
  • Which region made the most sales?
  • Which items were sold the least?
  • How many orders were made for baby food?
  • Which types of items were sold more online?
  • What is the number of item types sold by each region?
  • What is the total number of units sold for each item type?
  • How did sales change before and after a given date?

As you can see, asking these questions lets you understand your data better and helps you make more informed decisions. Pivot tables can organize your data such that you can easily get answers to questions like these.

At this point, you might be thinking, ‘Why not just use formulas to answer these questions?’. The answer is that pivot tables get your information extracted much faster than formulas. Since they are flexible (and ‘pivot’-able), they let you turn your data around to quickly and easily get your answers. Moreover, pivot tables reduce the chances of human error, giving you more accurate information.

How to Create a Pivot Table in Google Sheets

Now that you understand the basic idea behind pivot tables, let us see how you can create one. The best way to understand the process is by example. We are going to use the following data sample (which you can download from this link), and create various pivot tables from it to answer some of our questions.

To create a pivot table from the above data, follow the steps shown below:

  • Select all the data in your spreadsheet (on which you want to base your pivot table).
  • From the Insert menu, select Pivot table.
From the Insert menu, select Pivot table.
  • In the ‘Create pivot table’ box, if you want to display your pivot table in a new sheet, then select the radio button next to ‘New sheet’. If you want it in the same sheet, select the radio button next to ‘Existing sheet’.
New sheet
  • You should now see a pivot table created. If you had asked for the table to be displayed in a new sheet, you should find the new tab name as ‘Pivot Table 1’.  You can rename it to something else if you want to.
pivot table google sheets

At the beginning, your pivot table would be blank as shown in the image below. It will start filling up once you start adding rows, columns and values to the table (explained in step 6).

To the right of your Google Sheets window, you should see a Pivot table editor sidebar. This sidebar displays the following:

  1. The range of cells on which the pivot table is based
  2. Some suggestions for pivot tables based on your data
  3. Options to customize your pivot table according to your requirement. This includes 4 insert options:
    1. Rows
    2. Columns
    3. Values
    4. Filters
Pivot table editor sidebar.

Suggested Pivot Tables

Google Sheets provides a few ideas for pivot tables based on your selected data. For example, for our sample data, Google Sheets suggests the following:

  • Average of Unit Price for each Region
  • Count of Country for each Region
  • Number of unique Item Type for each Region

Clicking any of these suggestions will automatically create your initial pivot table. For example, let’s click on the second suggestion: “Count of Country for each Region”:

 Count of Country for each Region

Looking at the above table lets us know at a glance how many countries operate from each geographical region.

Creating a Custom Pivot Table

The suggestions provided by Google Sheets might not suit your needs, so you also have the option to build your own pivot table manually.

Let us try to build a custom pivot table to analyze the following:

“How many units of each item type were sold on the different sales channels before the year 2014?”

Let’s break this question down to understand exactly what we want our pivot table to show. We want:

  • The names of different Item types sold in the form of rows
  • The different sales channels in the form of columns
  • The value of total units sold in cells where each Item Type meets a Sales Channel
  • Filters to display only the data relating to the years before 2014. A filter lets you see parts of the data, while keeping irrelevant parts hidden.

Now that we know what we want, let’s start building our pivot table.

Inserting Rows

To display the list of Item Types in each row, click on the ‘Add’ button next to ‘Rows’ in your Pivot table editor. Select ‘Item Type’ from the dropdown.

click on the ‘Add’ button next to ‘Rows’ in your Pivot table editor. Select ‘Item Type’ from the dropdown.

The pivot table will obtain the names of all the Item Types from your data table and display them as shown below:

 Item Types

Note that the pivot table has displayed the list of items in alphabetical order and has removed any duplicates.

Inserting Columns

To display the list of Sales channels across each column, click on the ‘Add’ button next to ‘Columns’ in your Pivot table editor. Select ‘Sales Channel’ from the dropdown.

Select ‘Sales Channel’ from the dropdown.

The pivot table will obtain the names of the different Sales channels (‘Online’ and ‘Offline’) from your data table and display them as shown below:

 Sales channels (‘Online’ and ‘Offline’)

Our Google Sheets pivot table is already starting to take shape.

Inserting Values

Now it is time to populate the pivot table’s cells with the number of Units sold for each Item type through each Sales channel. For this, click on the ‘Add’ button next to ‘Values’ in your Pivot table editor. Select ‘Units Sold’ from the dropdown.

Select ‘Units Sold’ from the dropdown.

You should now see the sum of units sold for each item on each sales channel.

sum of units sold for each item on each sales channel.

Note that the Units Sold have been summed up and displayed in each cell. So for Baby Food, the pivot table summed up all the units sold Online and displayed them in cell C3.

If you want to see the average number of units sold instead of total, simply click on the dropdown below ‘Summarize By’ (under Units Sold) and select ‘Average’.

‘Summarize By’ (under Units Sold)                   

Note that the pivot table also displays the Grand total of Units sold for each channel (which it automatically calculates) at the bottom of the table. The Grand Total per item is also displayed on the right-most column of the table.

Adding Filters

We are already getting a lot of insights into our data from our created pivot table in Google Sheets. The last thing to do now is to narrow down our results so we see only the total number of units sold before sthe year 2014. For this we can use filters.

To add a filter to your pivot table, follow the steps outlined below:

  • Click on the ‘Add’ button next to ‘Filters’.
Add filters
  • Select ‘Order Date’ from the dropdown.
  • Click on the dropdown under ‘Status’.
Click on the dropdown under ‘Status’.
  • Click on the ‘Filter by Condition’ category.
Click on the ‘Filter by Condition’ category.
  • From the dropdown under ‘Filter by Condition’, select ‘Date is before’.
  • You should see a new dropdown under this now. Make sure that ‘exact date’ is selected.
  • In the input box that says ‘Value or Formula’, type the date ‘1/1/2014’. This ensures that the pivot table only considers the dates before 1st Jan 2014.
filter by condition
  • Click OK.
We now have a pivot table that displays the sum of units sold for each Item type on each Sales Channel before the year 2014.

That’s all! We now have a pivot table that displays the sum of units sold for each Item type on each Sales Channel before the year 2014.

How to Create a Pivot Chart in Google Sheets

A pivot chart is simply a chart made from a pivot table. Like pivot tables, a pivot chart helps you get a focused visualization of the important parts of your data. Moreover, since it is based on a pivot table, it offers the same dynamism that a pivot table offers.

Now there is no direct option for creating pivot charts in Google Sheets. You always need to first create a pivot table and then build the pivot chart from it.

Here are the steps to create a pivot chart from an existing pivot table:

  • Select your pivot table. Don’t forget to include the headers of the table. You can skip selecting the Grand totals.
  • From the Insert menu, select ‘Chart’.
From the Insert menu, select ‘Chart’.
  • This will display your pivot chart on the same page as your pivot table.

Here’s how the pivot chart based on our sample pivot table looks:

 pivot chart

Once your chart is ready, there are a few things that you need to note.

  • Based on our data, Google Sheets has assumed a Bar chart to be the best visualization option. However, you are free to select the chart that works best for you.
  • Any changes to your original data (and consequently your pivot table) gets updated on your pivot chart.
  • You will notice that your chart does not have chart and axis titles. However, you can manually add them as you would for any chart in Google Sheets.

Google Sheets Advanced Pivot Tables

Now that you’re familiar with the basics of creating a pivot table and, subsequently, a pivot chart, let’s move to the level of Google Sheets Advanced Pivot Tables. Let’s move forward and look at some questions you might come across as you practice and start getting better at your pivot table skills.

How to Refresh a Pivot Table in Google Sheets

A common question about pivot tables is ‘How do I refresh my pivot table in Google Sheets after making changes to the original dataset?

This is a question most Excel users might ask. However, unlike Excel, Google Sheets automatically refreshes pivot tables when the original dataset gets changed, so they don’t need to be manually refreshed.

There may still be situations where you find the data in the pivot table not getting updated. In such cases, check out our tutorial on “How to Refresh Pivot Table in Google Sheets”, where we speak in detail about the possible causes and solutions for this.

Pivot Table in Google Sheets Mobile

Google Sheets Mobile users might find it difficult to create a pivot table from the app. This is because this feature is not yet available on the app. But if you still want to use your phone to create a pivot table, there’s an alternative route.

  1. Go to your mobile phone browser.
  2. Login to your Google Drive.
  3. Go to the Google Sheets webpage (sheets.google.com).
  4. You will see the mobile version of the page. Change this to the desktop version of the site.
  5. Now you can open the sheet that contains your data.
  6. Create a pivot table from your data as you would create one on any desktop computer.  

How to Hide the Pivot Table Editor in Google Sheets

A lot of Google Sheets users would like to have the pivot table editor hidden once they’ve created a pivot table. However, at this point, there really is no way to hide the pivot table editor in Google Sheets from view. Every time you select a cell of your pivot table, the editor sidebar will appear, and there’s not much that you can do other than to simply close it.

Create a Google Sheets Pivot Table from Multiple Other Sheets (and with a Dynamic Source Data Range)

Often we have our data spread out over multiple sheets or tables. However, a single pivot table can only be created from one data span. So inherently, it is not possible to use data from different tables to create a single pivot table.

However, there is a workaround if you really need to use data from multiple sheets in one pivot table. You can use an array to combine all the data tables into one common table, after which, you can use this array (or combined table) to create your pivot table!

Let’s take an example to understand how to do this. Let’s say you have data spread out over two sheets. Say Sheet1 has the following 15 rows:

 Sheet1

Say Sheet2 has the following 15 rows:

 Sheet2

Now the first step is to create an array that combines both tables. Each table has entries ranging from cells A2:G16. So in a new sheet, add the column headers in the first row, and in the second row, enter the following formula:

={Sheet1!A2:G16;Sheet2!A2:G16}

Now this will display all the rows from both the tables, and you can easily create a fresh pivot table from this.

all the rows from both the tables

Here’s a pivot table created from this list:

google sheets pivot table

There’s one drawback with this formula though. If your aim is to create a pivot table with a Dynamic Source data range, then this will not work.

This is because your formula is limited to just the cells between A2 and G16 from each sheet. So if you add a new row to any of the sheets, this will not get updated in your array, and thereby your pivot table.

To solve this problem, you can remove the row numbers from the ending cell references in the formula, so that they contain only the column name. So instead of A2:G16, reference the cells A2:G.

This way, no matter how many new rows you add or remove from your original data, the pivot table always remains up-to-date. This means instead of the above formula, you can use the formula shown below:

=SORT({Sheet1!A2:G;Sheet2!A2:G})

Notice we put the array inside a SORT function. This is because the references A2:G will refer to all the rows of Sheet1 and Sheet2. This means all the blank rows of these sheets will also get included in the array. To avoid that, we used a SORT function. This will ensure that all the blank rows of the resultant array are moved to the end, and the non-blank rows are all visible at the top of the array.

SORT function

We now have a Google Sheets pivot table with a dynamic range!

Google Sheets pivot table with a dynamic range

Another thing you need to do in order to make your pivot table dynamic is to add a filter to it that displays only those rows that are not empty. This ensures that even if you delete a few rows, you don’t end up with blank rows in your pivot table.

For example, take a look at what happens to your pivot table when you delete row 3 from Sheet1:

 delete row 3 from Sheet1

Here’s the resultant pivot table:

pivot table results

The first row of the pivot table is now blank because the row containing Item type =”Cereal” does not exist anymore.

To make sure blank rows like these are not displayed in your pivot table, follow the steps shown below:

  • From your Pivot table editor, click on the ‘Add’ button under ‘Filters’.
  • Select Item Type from the field list that appears.
  • In the Item type block under Filters, click on the dropdown under ‘Status’.
In the Item type block under Filters, click on the dropdown under ‘Status’.
  • Click on ‘Filter by condition’.
  • Click on the dropdown under it and select ‘Is not empty’.
Click on the dropdown under it and select ‘Is not empty’.
  • Click OK.

This should now get rid of any blank rows in your pivot table, and keep your pivot table fully dynamic.

dynamic pivot table

Conclusion

Google Sheet pivot tables help you look at your data from different angles and perspectives. They condense your data such that you get maximum information with minimal distractions. In this tutorial, we introduced you to pivot tables and showed you how to create one with a simple example. We also showed you how to create a pivot chart in Google Sheets from a pivot table and answered some questions that are commonly asked by Google Sheets users when creating pivot tables. We hope this step-by-step tutorial was informative and helpful for you.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.