How to Use Pivot Tables in Google Sheets

Extracting meaningful insights from a spreadsheet is tough, especially if you’re dealing with massive amounts of data. 

Creating a pivot table can help.

This feature lets you organize and categorize your dataset. Making it easier to identify trends, patterns, and outliers.

In this guide, we’ll show you how to create a pivot table in Google Sheets effortlessly. 

Let’s get started.

How to Create a Pivot Table in Google Sheets

Suppose you run a Shopify store and want to extract insights from your inventory management spreadsheet. 

Here’s a dummy dataset, comprising the following columns: Product, Brand, Price, Availability, Stock, Sold in October 2022, Sold in November 2022, Sold in December 2022, Primary Category and Sub-Category.

To extract meaningful insights from this dataset, we’ll create a pivot table. 

Follow these steps:

1. Set up your data

Launch your spreadsheet inside Google Sheets. You can either create a new sheet and upload your spreadsheet file or upload your spreadsheet file to Google Drive and load it from there.

Once you’ve opened the spreadsheet, move to Step 2.

2. Select your data

Click and drag your mouse to select the data you want to use for your analysis, including column headers.

3. Select pivot table from the top menu.

Next, navigate to the top menu in Google Sheets and click "Insert." From the drop-down menu that appears, select “Pivot table.”

4. Choose your data range

Google Sheets will ask you to specify the data range. We specified the range earlier, however if you want to make any modifications you can do so here. 

Also, Google Sheets will ask you if you want to insert the pivot table in a new sheet or inside the existing one. 

Let's pick the "New Sheet" option and click "Create."

Next, you'll see the pivot editor where you can set up your pivot table.

5. Organize your data

Think about where each piece of information should go in your pivot table:

• For categories like Primary Category, Sub-Category, put them in the "Columns" or "Rows" section.
• For numbers like Stock, Sold in October 2022, Sold in November 2022, put them in the "Values" section.
• If you want to filter your data, like only looking at sales in a particular year, use the "Filters" box.

By understanding where to place your variables, you'll be able to arrange and analyze your data in a way that provides valuable insights.

Let’s say we want to find out how many products across specific categories and sub-categories were sold in October, November and December 2022. Now, you can either analyze the entire spreadsheet and do it one-by-one. Or you can just organize your data using the Pivot table and get your answer.

First, let’s add “Primary Category” in the Rows column.

Click "Add" next to "Rows" and choose "Primary Category" from the list.

Result: 

Let’s now add “Sub-Category” in the Columns section of the Pivot Editor. 

Result: 

Let’s now add “Sold in October 2022,” “Sold in November 2022,” and “Sold in December 2022” in the Values section of the Pivot editor to get a comprehensive overview of your sales data for these months. You can add all three from the Values section. And here are the results you’ll see:

Some insights: 332 products from the Primary Category “Clothing, Stores & Accessories” and sub-category “Accessories” were purchased in October 2022. The number stood at 244 in November 2022 and 177 in December 2022.

And that’s how you can arrange your dataset using Google Sheets’ Pivot Table feature and gain valuable insights.

6. Filter your data

If you want to focus on specific details or filter your data further, you can use the "Filters" box in the pivot table editor.

For example, if you want to further narrow down this data to find out of stock products, you can do this by applying the filter as “Availability -> Out of Stock”

Result (sales of products that are out of stock right now based on categories and sub-categories in October, November, December):

‍

What are the limitations of the pivot table in Google Sheets?

Even though Google Sheets is a powerful spreadsheet application, it comes with its own limitations. Some of them are:

• Google Sheets has a limit of 10 million cells per spreadsheet. 
• Using the pivot table feature in Google Sheets can be tricky for beginners. Comes with a steep learning curve.
• And even if it’s tricky, most of the rows and columns here are very empty and don’t look insightful. You need to do a lot of back-and-forth to get meaning from this data. 

Gigasheet, our big data cloud spreadsheet platform, overcomes these limitations. 

• You can work with large spreadsheets, even those with over one billion rows and files larger than 140GB.
• It's beginner-friendly, allowing beginners to create pivot tables within seconds.
• Bonus: you can use Gigasheet’s AI assistant to make a pivot table with prompts.

Let us show you how to create pivot tables in Gigasheet.

How to Create Pivot Tables in Gigasheet

Let’s perform the same operation inside Gigasheet. 

We uploaded the same spreadsheet to our platform. And here’s how it looks like inside Gigasheet’s editor:

To create a pivot table, click the 'arrow' icon on the right side of the sheet.

Select the second icon representing columns. This will open a pivot function box.

Inside the pivot function box, locate and click the "Pivot Mode" toggle to enable/disable it. We’ll turn it on here.

Note that almost all data will disappear from your sheet after enabling pivot mode.

You can hide/unhide specific data points using the eye-icon. 

Here, we’ll unhide the following columns: Primary Category, Sub-Category, Sold in October 2022, Sold in November 2022, Sold in December 2022 and Availability.

We’ll drag the following columns to the following Pivot table groups:

• Row Groups: Primary Category & Sub-Category
• Values: Sold in October 2022, Sold in November 2022, Sold in December 2022
• Column Groups: Empty

The reason why we’re adding Sub-Category in the Rows Group here (which is we didn’t do inside Google Sheets) is that Gigasheet allows you to add sub-groups, however; that’s not the case with Google Sheets. Hence, the end-result in Google Sheets feels bland, whereas in Gigasheet, the ability to add sub-groups enhances the depth and granularity of our data analysis.

Result:

As visible, two layers of grouping have been created (which is way better view than in Google Sheets). The first layer is the Primary Category. Upon expanding it, you’ll see their respective sub-categories as the second-layer. And next to these, you’ll see how many products in these categories and sub-categories were sold in October, November and December 2022.

Let’s further filter to find out of stock products.

Result:

Final Thoughts - Pivot Tables in Google Sheets

If you are a beginner and want to use pivot tables without worrying about data limits and a steep learning curve, we highly recommend that you try Gigasheet. 

Our platform can process up to a billion rows and extremely large spreadsheet files.

But don’t take our word for it. 

Take Gigasheet out for a test spin today!

Sign up for free here.