How to Create and Use a Pivot Table in Google Sheets?

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 for 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.

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.

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”.

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”.

1. 4. Select the location of your pivot table within the existing sheet. Once you finish, click “OK” and then “Create”.

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.

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”.

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.

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”.

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.

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.

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”.

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 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”.

1. 3. Below, you will see the newly created pivot table based on Google’s suggestion.

What if your data is 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.

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.