Don’t forget to share this post

Analyzing large datasets for decision-making can be a daunting challenge, especially if we are relying wholly on a basic spreadsheet table view. This is where the potential of pivot tables is immeasurable.

Google Sheets allows you to create pivot tables to achieve better and more efficient data analysis. Users can adopt a pivot table to any purpose effortlessly; unless you are more accustomed to using formulae, it is definitely a faster alternative to explore your data.

This article will explain the concept of a pivot table and its many applications in business tasks. You’ll then learn step-by-step how to create a pivot table in Google Sheets manually and based on Google’s automatic suggestion.

What is a pivot table?

A pivot table is a summary of key data previously collected and stored in a spreadsheet. Users can then reduce this data into clusters to make it easier for the user to find and retrieve according to their needs. By summarizing your data into charts, you gain meaningful insights through easier interpretation. The term “pivot” refers to the re-positioning of data from rows to columns and vice versa.

Now that you have a better idea of what they are, let’s see the many ways in which pivot tables can be used.

What can pivot tables in Google Sheets be used for?

If your work involves finding key patterns, trends, or making a comparative analysis on large datasets, then you will probably relate to the following cases where pivot tables in Google Sheets can be used for:

  • Easier interpretation: By summarizing data, pivot tables help users focus on key information and get a better understanding of the information they present.
  • Faster pattern and trend spotting: This can be achieved through sorting and rearranging data according to the type of information you seek.
  • Reliable decision-making: Rows and columns can be rearranged to compare data in a more straightforward way. This will also improve decision-making for managers since they gain a holistic view of their data.

Now that you’ve seen the most useful ways to use pivot tables in Google Sheets, let’s see how you can easily create them.

Transfer Data From One Excel Worksheet to Another Automatically
Transfer Data From One Excel Worksheet to Another Automatically

Excel worksheets or workbooks can be linked to automatically update data. Here's how to transfer data from one Excel worksheet to another automatically

READ MORE

How do you create a pivot table in Google Sheets?

Let’s start by illustrating how to create a pivot table in Google Sheets manually, then based on suggestions, and finally, offer possible ways to work around creating pivot tables from another sheet and multiple Google Sheets.

How to create a pivot table in Google Sheets manually?

Let’s say I have a dataset containing the costs of products per department. Rather than adding up the total costs manually for each, a pivot table will help me automatically restructure my data and make it more readable. The following steps show you how to both create and edit your pivot table.

  1. 1. Open your Google Sheet and go to Data > Pivot table.
How to create a pivot table in Google Sheets Data Pivot Table
How to create a pivot table in Google Sheets - Data > Pivot Table
  1. 2. Select the range of data you will use to create the pivot table. Here, I have selected the suggested range, which includes all data in the sheet. Click “OK”.
How to create a pivot table in Google Sheets Select a data range
How to create a pivot table in Google Sheets - Select a data range
  1. 3. Click “New sheet” if you want to create the pivot table on a separate sheet, or “Existing sheet” if you prefer to set it in the current sheet. Since this dataset has room for a pivot table viewing, I’ve chosen to create it in the existing sheet. Then, click “Create”.
How to create a pivot table in Google Sheets Create in Existing Sheet
How to create a pivot table in Google Sheets - Create in Existing Sheet
  1. 4. Select the location of your pivot table within the existing sheet. Once you finish, click “OK” and then “Create”.
How to create a pivot table in Google Sheets Location of pivot table
How to create a pivot table in Google Sheets - Location of pivot table
  1. 5. You should now be able to see your pivot table and the “Pivot Table Editor” to the far right-hand side of your sheet.
How to create a pivot table in Google Sheets Pivot table editor
How to create a pivot table in Google Sheets - Pivot table editor
  1. 6. Each table feature has the “Add” button next to it, so you can select the data to view in the pivot table and how to view it. Click on “Add” next to “Row” and select the data you would like to include in your pivot table. Here, I will select “Department”.
How to create a pivot table in Google Sheets Pivot Table Add
How to create a pivot table in Google Sheets - Pivot Table Add
  1. 7. From the “Department” box, you can choose how you want the data to be ordered and sorted in “Order” and “Sort by”. Here, I will leave it to the default selection and add data corresponding to the “Product” column.
How to create a pivot table in Google Sheets Pivot Table Row
How to create a pivot table in Google Sheets - Pivot Table Row

You can also rearrange the data in the “Columns” section to specify which data you want to make viewable in the pivot table according to the column categories.

  1. 8. In “Values”, you can determine how you would like to calculate the data. The “Calculated Field” allows you to calculate and create new values not currently stored in your data. Here, I will “Add” calculations for the data previously selected, “Department” and “Total Costs”.
How to create a pivot table in Google Sheets Pivot Table Values
How to create a pivot table in Google Sheets - Pivot Table Values
  1. 9. In “Summarize by”, you can choose to calculate data as a sum, an average, or set the minimum or maximum value. The “Show as” allows you to show your calculations as a default value or as a percentage of rows, columns, or the grand total. Here, I will leave to the default selection.
How to create a pivot table in Google Sheets Values features
How to create a pivot table in Google Sheets - Values features
  1. 10. “Add” in the “Filter” section according to the “Rows” and “Columns” data added previously. Here, I have selected “Total Costs” as I would like to analyze the highest costs across all departments.
How to create a pivot table in Google Sheets Add Filter
How to create a pivot table in Google Sheets - Add Filter
  1. 11. Click on the drop-down menu “Showing all items” to “Filter by condition” or “Filter by values”. Here, I want to highlight the highest costs overall, so I will choose “Filter by condition”, aka conditional formatting.
How to create a pivot table in Google Sheets Filter by condition
How to create a pivot table in Google Sheets - Filter by condition

Please note: Although here this example only includes cells containing data, you can also filter by condition to show items with no data.

  1. 12. Then, include the conditional formatting rule to apply to your data. Here, I will select “Greater than” and include the value I would like to establish as a threshold, “25,000”.
How to create a pivot table in Google Sheets Filter by condition 2
How to create a pivot table in Google Sheets - Filter by condition

Now that you have seen how easy it is to create your own pivot table, let’s see how to insert a pivot table in your spreadsheet based on Google Sheets’ suggestions.

How to VLOOKUP in Excel with Two Spreadsheets?

Sometimes our data may be spread out among different Excel sheets or workbooks. Here's how to do VLOOKUP in Excel with two spreadsheets

READ MORE
How to VLOOKUP in Excel with Two Spreadsheets

How to create a pivot table in Google Sheets based on suggestions?

If you think that creating a pivot table manually in Google Sheets was easy, creating one based on a suggestion will take seconds. Although the suggested pivot tables may not adapt to advanced needs, it offers a larger range of pivot tables for your data.

  1. 1. Follow steps 1-4 from the previous section.
  2. 2. In the “Suggested” menu, select any of the pivot tables. Here, I will select the “Average of Total Costs for Each Department”.
How to create a pivot table in Google Sheets Suggested pivot table
How to create a pivot table in Google Sheets - Suggested pivot table
  1. 3. Below, you will see the newly created pivot table based on Google’s suggestion.
How to create a pivot table in Google Sheets Suggested Average
How to create a pivot table in Google Sheets - Suggested Average

What if your data spread across various Google Sheets? Theoretically, Google Sheets only allows you to create pivot tables from one table. However, you have two options:

  1. 1. Manually copy and paste the data from other Google Sheets into the same table.
  2. 2. Use the Google Sheets QUERY function to combine data from another sheet.

Once you’ve done this, simply follow the instructions listed above.

How to manage Google Sheets data with Layer?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

  • Upload or connect your existing Excel or Google Sheets-based budget.
  • Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made and decide which ones to merge with your spreadsheet or discard.
  • Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

Conclusion

Pivot tables are a great tool to use to organize your tables in a unique way and extract key trends and patterns from your data. Although they may seem quite complex at first glance, Google Sheets makes it extremely easy to create one yourself, even suggesting specific pivot table formats in order to make the most out of your data.

By the end of this article, you should now know what pivot tables can offer you and how to create one. You should also know the key features of the Pivot Table Editor and how to use these to transform the way your pivot table shows data.

Want to create a pivot table in Excel? Take a look at our blog article for a comprehensive, step-by-step guide on How To Create A Pivot Table In Excel.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Apr 4 2022, Updated Apr 8 2022