An Easy Pivot Table Google Sheets Guide for 2023

Have you ever stared at a thick datasheet document until your eyes lie down? Working with large amounts of data can be very taxing. That’s where pivot tables come in. Pivot tables are an easy way to present aggregated data from a bloated spreadsheet. So can you put a pivot table in Google Sheets? Luckily, you can. This Pivot Table Google Sheets Guide explains how to create pivot tables and how useful they are.

The concept of pivot tables in Google Sheets can seem confusing at first, but once you get the hang of it, you’ll find your data analysis skills taken to another level. This tutorial is intended to introduce beginners to Google Sheets pivot tables and how to use them, and to help intermediate users improve their pivot table skills with additional tips and tricks.

No one, even the most experienced Excel user, has ever wondered, “Does Google Sheets have pivot tables?” Or if you’re a first-time user and wondering how it works, don’t worry. Read this article to learn how to create pivot tables in Google Sheets.

What Is a Google Sheet Pivot Table?

A pivot table is a table of columns and rows that can be moved (pivoted) to group rows and columns, allowing you to separate, expand, and summarize data in real-time in a variety of ways.

It might sound complicated, but let me explain with an example.

Look at the dataset below:

The above dataset looks large and complex, making it very difficult to make any inferences from it. Pivot tables help compress such data sets to better capture and understand the relationships within the data.

Why Do I Need a Pivot Table?

So what are PivotTables used for? PivotTables help you analyze your data from different angles, giving you greater insight into the relationships between elements of your data. They help form aggregated data by combining and summarizing information from the large amounts of data you hold.

For example, pivot tables help answer questions like:

• How many countries are participating from each region?
• What is the average number of units sold for each item type?
• Which regions generated the most sales?
• Which product sold the least?
• How many baby food orders were there?
• What types of products sold more online?
• What is the number of types of items sold in 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 can help you understand your data better and make more informed decisions. Pivot tables allow you to organize your data in a way that makes it easier to answer questions like these.

The Benefits of Using a Pivot Table

At this point, you might be thinking, “Why don’t we use formulas to answer these questions?” The answer is that pivot tables let you extract information much faster than formulas. It’s flexible (and allows you to “pivot”) so you can rotate your data to get answers quickly and easily. Additionally, using pivot tables reduces the chance of human error and provides more accurate information.

There are several other reasons to use pivot tables. Here are some:

• It’s easy to make and you can make it quickly
• Immediate data creation
• Summarize large datasets
• Helps recognize data patterns

How to Make a Pivot Table in Google Sheets

Now that you understand the basic idea behind pivot tables, let’s see how to create one. The best way to understand the process is to look at an example. We will use the following data sample (which you can download from this link) and create various pivot tables from it to answer some questions.

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

Step 1: Select all the data in your spreadsheet (on which to base your pivot table). Be sure to include the header.

Step 2: Select Pivot Table from the Insert menu.

Step 3 : In the Create Pivot Table box, select the radio button next to New Sheet if you want the Pivot Table to appear on a new sheet. If you want to include it on the same sheet, select the radio button next to ‘Existing sheet’.

The data range options allow you to choose the data set to use for your pivot table. To do this, click on the four squares and select the cells of the data set you want to use.

Step 4: You can see that the pivot table has been created. If you specified the table to appear on a new sheet, the new tab name should be “Pivot Table 1”. You can change the name to something else if you like.

Step 5 : Initially the pivot table is blank as shown in the image below. As you start adding rows, columns, and values ​​to the table, the table will begin to fill (described in step 6).

On the right side of your Google Sheets window, you’ll see the Pivot Table Editor sidebar. This sidebar shows:

1. The range of cells that the pivot table is based on
2. Some suggestions for data-driven pivot tables
3. Option to customize the pivot table as per your requirement. This includes his four insert options:
   1. line
   2. column
   3. values
   4. filter

Suggested Pivot Tables

Thanks to the latest AI features Google added to Google Sheets, you can automatically create pivot tables. Google Sheets offers some ideas for pivot tables based on your data selection. For example, for your sample data, Google Sheets suggests:

• Average unit price for each region
• Number of countries in each region
• Number of unique item types in each region

Clicking any of these suggestions will automatically create your first pivot table. For example, let’s click on the second suggestion “Number of countries in each region”:

Looking at the table above, you can see at a glance how many countries are active from each geographic region.

It also has a query function that allows you to ask questions and explore data in pivot tables.

Step 6: Creating a Custom Pivot Table

You also have the option to manually create your own pivot table, as the suggestions provided by Google Sheets may not suit your needs.

Let’s build a custom pivot table to analyze:

“How many units of each product type were sold across different sales channels before 2014?”

Let’s break down this question to understand exactly what we want to show in the pivot table. What we want is:

• Names of different item types sold in line format
• Introducing various sales channels in column format
• Value of total units sold in cells where each item type matches the sales channel
• Filter to show only data related to years prior to 2014. Filters allow you to show some of your data and hide the irrelevant parts.

Now that you know what you need, let’s start building your pivot table.

Inserting Rows

To see a list of item types for each row, click the Add button next to Rows in the PivotTable Editor. Select Item Type from the dropdown.

The pivot table will get the names of all item types from the data table and display them like this:

Note that the Pivot Table shows the list of items in alphabetical order and duplicates have been removed.

Inserting Columns

Click the Add button next to Columns in the pivot table editor to see the list of sales channels for each column. Select Sales Channels from the dropdown.

A pivot table gets the names of the various sales channels (“online” and “offline”) from the data table and displays them as follows:

Spreadsheet pivot tables are already starting to take shape.

Inserting Values

Then enter the number of units sold for each item type through each sales channel in the pivot table cells. To do this, click the Add button next to Values ​​in the PivotTable Editor. Select Sales Unit from the dropdown.

You can also add calculated fields to customize pivot tables with special functions.

You should now be able to see the total quantity sold for each item in each sales channel.

Notice that the sales quantity is totaled and displayed in each cell. So for baby food, the pivot table sums all units sold online and displays it in cell C3.

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

Note that the pivot table also displays a grand total of units sold for each channel (calculated automatically) at the bottom of the table. Totals for each item are also displayed in the rightmost column of the table.

Adding Filters

Pivot tables created in Google Sheets already give us a lot of insight into our data. The last thing to do is narrow the results to show only the total number of units sold before 2014. You can use filters for this.

To add a filter to your pivot table:

1. Click the ‘Add’ button next to ‘Filter’.
   

   

2. Select Order Date from the dropdown.
   

   

3. Click the dropdown under ‘Status’.
   

   

4. Click the Filter by Criteria category.
   

   

5. From the ‘Filter by Criteria’ dropdown, select ‘Date is before’.
6. A new dropdown should appear below this. Make sure “Exact date” is selected.
7. In the input box labeled Value or Expression, enter the date 1/1/2014. This ensures that the pivot table only considers dates prior to January 1, 2014.
   

   

8. Click OK.

that’s all! You now have a pivot table that displays the total sales quantity of each item type for each sales channel prior to 2014.

You can also filter your table using the sheet slicer, which is much more intuitive than regular filters. To learn how to add sheet slicers to Google Sheets, see Google Sheet Slicers.

How to Create a Pivot Chart in Google Sheets

A Pivot Chart is a graph created from a Pivot Table. Like pivot tables, pivot charts help you focus and visualize important parts of your data. Plus, because it’s based on pivot tables, it offers the same dynamism that pivot tables offer.

There is currently no direct option for creating pivot charts in Google Sheets. You should always create a pivot table first and then create a pivot chart from it.

To create a Pivot Chart from an existing Pivot Table:

1. Select a pivot table. Don’t forget to include the table headers. The total selection is optional.
2. From the ‘Insert’ menu, select ‘Chart’.
   

   

3. This will display the pivot chart on the same page as the pivot table.

A pivot chart based on the sample pivot table looks like this:

Once your chart is ready, there are a few things you need to be aware of.

• Google Sheets assumes that a bar chart is the best visualization option based on your data. However, you are free to choose the chart that works best for you.
• Any changes to the original data (resulting in the pivot table) are updated on the pivot chart.
• You can see that the chart is missing chart and axis titles. However, they can be added manually just like charts in Google Sheets.

Google Sheets Advanced Pivot Tables

Now that you understand the basics of creating pivot tables and subsequently pivot charts, let’s move on to advanced pivot table levels in Google Sheets. Let’s go ahead and take a look at some questions you may encounter as you begin practicing and improving your pivot table skills.

How to Create a Pivot Table in Google Sheets Mobile

Google Sheets Mobile users may find it difficult to create pivot tables from the app. This is because this functionality is not yet available in the app. However, if you still want to use your phone to create pivot tables, there is another way.

1. Go to your mobile phone’s browser.
2. Log in to Google Drive.
3. Go to the Google Sheets web page (sheets.google.com).
4. A mobile version of the page is displayed. Change this to the desktop version of the site.
5. Now you can open the sheet containing the data.
6. Create a pivot table from your data the same way you create a pivot table on your desktop computer.

How to Hide the Pivot in Google Sheets Table Editor

Many Google Sheets users want to hide the pivot table editor after creating a pivot table. However, there is currently no way to actually hide the pivot table editor in Google Sheets. Whenever you select a pivot table cell, the editor sidebar is displayed. There isn’t much you can do other than close it.

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

Data is often spread across multiple sheets or tables. However, a single pivot table he can only create from one dataspan. So, in essence, he cannot create one pivot table using data from different tables.

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

Let’s look at an example to understand how to do this. Suppose the data spans her two sheets. Suppose Sheet1 has the following 15 rows:

Suppose Sheet2 has the following 15 rows:

The first step is to create an array that joins both tables. Each table has entries ranging from cells A2 to G16. Add column headers in the first row of a new sheet and enter the following formula in the second row:

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

Now you can see all the rows from both tables and easily create a new pivot table from them.

Here’s the pivot table created from this list:

However, this formula has one drawback. This will not work if your goal is to create a pivot table with a dynamic source data range.

This is because the formula is restricted to only cells between A2 and G16 on each sheet. Therefore, adding a new row to either sheet does not update in the array, nor does it update the pivot table.

To fix this issue, remove the row number from the last cell reference in the formula so that it contains only the column name. Therefore, instead of A2:G16, we will refer to cells A2:G.

That way, the PivotTable will always be up to date, no matter how many new rows you add or remove from the original data. This means that instead of the formula above, you can use the formula shown below:

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

Notice that we put the array inside the SORT function. This is because the reference A2:G refers to all rows of Sheet1 and Sheet2. This means that all blank rows in those sheets will also be included in the array. To avoid that, I used the SORT function. This will move all blank lines in the resulting array to the end so that all non-blank lines appear at the beginning of the array.

You’ve just created a Google Sheets Pivot Table with Dynamic Range!

Another thing we need to do to make the pivot table dynamic is add a filter to the pivot table that shows only non-empty rows. This ensures that no blank rows remain in the pivot table even if you delete some rows.

For example, let’s see what happens to the pivot table when we remove row 3 from Sheet1:

The resulting pivot table is:

The first row of the PivotTable is blank because there are no rows with item type =”Serial.

To prevent such blank rows from appearing in your pivot table, follow the steps given below:

1. In the PivotTable Editor, click the ‘Add’ button under ‘Filter’.
2. Select the item type from the field list that appears.
3. Under ‘Filter’, in the ‘Item type’ block, click the dropdown under ‘Status’.
   

   

4. Click ‘Filter by conditions’.
5. Click the dropdown below it and select ‘not empty’.
6. Click ‘Filter by conditions’.
7. Click the dropdown below it and select ‘not empty’.
   

   

8. Click OK

This removes blank rows in the pivot table and ensures that the pivot table remains fully dynamic.

How To Use Pivot Tables In Google Sheets

Pivot tables should be used to summarize and make sense of large data sets. It doesn’t make much sense to add more data if the existing data is easy to read. Here are the easy steps to insert a Pivot Table in Google Sheets:

1. Select the cells you want to pivot table
2. Go to Data > PivotTable
3. Check if the proposed pivot table is suitable
4. Click Add and/or Filter to customize

Frequently Asked Questions

How Do I Create a Pivot Table in Google Sheets?

1. Select the cells you want to pivot table
2. Go to Data > PivotTable
3. Check if the proposed pivot table is suitable
4. Click Add and/or Filter to customize

What Is a Pivot Table and How Does It Work?

Pivot tables are overviews of large data sets. It works by capturing the data points you want into a more understandable format.

How Do I Update a Pivot Table in Google Sheets and Why Won’t It Refresh?

In most cases, the pivot table automatically updates as you enter new data. However, there are some potential reasons why this might not be the case. Let’s see them:

There is a filter in the pivot table: To fix this issue, remove the filter and add it again when the pivot table is refreshed.

The new row is outside the pivot table range. To fix this, make sure the row is included in the pivot table editor menu.

You are using filters or formulas that need to be updated. If you use formulas like NOW, TODAY, RAND, etc., the pivot table will not update automatically.

How do I add an editable column to my pivot table?

1. Click on the pivot table to open the editor
2. Click Add next to Columns

Can You Create a Pivot Table From Multiple Tabs Google Sheets?

Unfortunately it is not possible to get data from multiple ranges. First you need to combine the data into one sheet.

How Do You Aggregation Types in a Google Sheets Pivot Table?

Change the Aggregation method: drop-down menu option to the desired aggregation type, such as AVERAGE. By default it is set to SUM.

How do I format a pivot table in Google Sheets?

You cannot change the formatting of pivot tables. Instead, change the formatting of the source data.

How do I Automatically Update a Pivot Table in Google Sheets?’

This is a question most Excel users will have. However, unlike Excel, Google Sheets automatically updates pivot tables when the underlying dataset changes, so you don’t have to manually refresh them.

There may still be situations where the data in the pivot table is not updated. If this is the case, check out our tutorial on how to update a pivot table in Google Sheets. There, we detail possible causes and solutions.

Conclusion

Pivot tables in spreadsheets help you look at your data from different angles and perspectives. Data is condensed for maximum information retrieval with minimum disruption. This Pivot Table Google Sheets tutorial introduced you to Pivot Tables and demonstrated how to create one using a simple example. We also showed you how to create a pivot chart in Google Sheets from a pivot table and answered some of the most common questions Google Sheets users have when creating pivot tables. I hope you found this step-by-step tutorial informative and helpful. There are other useful tutorials, such as how to create a table in Google Sheets.