Google Sheets Pivot table – Working with a Pivot Table Guide

In this tutorial, you will learn to create a Pivot Table in Google Sheets and customize it in different ways to make the best of it.

What is a Pivot Table

A PIVOT TABLE in Google Sheets is a tool that allows users to summarize and analyze large data quickly and easily. It uses a structure where rows and columns can be defined to create a summary table that shows values from large data in a compact form. You can sort and filter a pivot table to change its view.

Key Features of Pivot Tables

Below, I’ll outline the key features of pivot tables in Google Sheets:

• Row and Column Grouping:  Pivot tables allow you to group data automatically based on the fields you add to rows or columns. This feature helps you organize and summarize data efficiently.
• Summarization: You can summarize your data using different calculations, such as Sum, Count, Average, Max/Min, etc.
• Filters: Pivot tables provide full filtering options, allowing you to display only the data that meets certain conditions.
• Sorting – You can sort the data in your pivot table rows or columns in ascending or descending order based on numeric values, alphabetic order, or date.
• Refresh: You can refresh the pivot table when the source data changes. You can use Manual or automatic refresh.

Getting Data Ready in Google Sheets to Create a Pivot Table

• Organize Your Data: Ensure your data is in tabular format. Each column should have a unique header that clearly describes the data type in that column and should contain the same data type. Make sure not to have merged cells anywhere in the data.
• Clean Your Data: Check for blank cells or errors affecting calculations. Remove any duplicate rows. Extra spaces before or after values may cause errors.
• Correct Data Formatting: Make sure all date, number, and percentage columns are formatted with the right data type. Also, ensure the values are not formatted as text; this will create an error when calculating.

Steps to Create a Pivot Table in Google Sheets

Creating a pivot table in Google Sheets can be a great way to summarize and analyze large data. Here’s a step-by-step guide to help you create your first pivot table:

• Select the Data: Select the cells that contain the data you want to use in your pivot table. Include the column headers, but exclude any total rows or columns.

• Insert Pivot Table: Go to the top menu and click on Data. Select the Pivot table from the dropdown menu. Choose whether you want the pivot table in a new or existing sheet. It’s generally best to place it in a new sheet for clarity.

• Choose Row/Column Labels – In the pivot table editor (which appears on the right side of the screen), use the Rows and Columns sections to add fields to organize your data vertically and horizontally. For example, drag the “Region” field to the Rows and Representative to the Columns.

• Add Value Field: Drag fields to the Values section to perform calculations over your data (We are using the “Total” column in our example to use as Values).

By default, Google Sheets summarizes the data using the SUM. Still, you can change this by clicking on the dropdown next to the field under “Values” and selecting another type of calculation like COUNT, AVERAGE, MAX, MIN, etc.

Now, at this point, your pivot table is ready.

Understanding the Major Parts of a Pivot Table

Understanding the different parts of a pivot table is essential to fully utilizing it.

1. The rows area in a pivot table displays the data vertically. You can drag fields to the rows area to organize your data by them. For example, adding the “Region” field here would organize your data in rows grouped by regions.
2. The columns area works similarly to the rows area but displays data horizontally. Fields added here will organize data across the top of the pivot table. For instance, adding the “Representative” field to columns will display all Representative names across the column headers.
3. The values area is where you add fields to perform calculations. You can summarize data using different calculations such as sum, average, count, max, min, etc. For example, adding a “Total” field to the values area and setting it to sum would give you the total amount.

Using filters in a Pivot Table

Filters in a pivot table in Google Sheets allow you to narrow down the data in the pivot table.

• Access the Pivot Table editor: The Pivot Table editor should be on the right side of your Window. If it is not activated, click the small edit icon at the bottom of the pivot table to open the editor.

• Locate the “Filters” section: In the Pivot Table editor, there’s a section labeled ‘Filters’. In this header, you’ll find an ‘Add’ button.
• Select the field to filter: After clicking the ‘Add’ button, a drop-down menu will appear. This menu lists all the fields currently in your Pivot Table. From this list, select the field you want to add a filter to.

• Select the field to filter: After clicking the “Add” button, a drop-down menu will appear. This menu lists all the fields currently in your Pivot Table. Select the “Item” field from this list to add to the filter.

• Specify filter criteria: Once you’ve selected a field, a new option labeled ‘Showing all items’ will appear on the drop-down. Click on this drop-down to specify the criteria for your filter.

• Apply the filter: Click the ‘OK’ button to apply the filter to your Pivot Table.

Changing Number Formatting for a Pivot Table in Google Sheets

Once your Pivot Table is ready, you can change the formatting to make it easier to read and understand.

Click on the cells in the pivot table that contain numbers or values you want to change the format for. Go to Format > Number in the menu to select the required number format such as Currency, Percent, or Accounting.

Sorting in a Pivot Table

Sorting a pivot table in Google Sheets helps you view your data in the order that makes sense to you, from highest to lowest, earliest to latest, or any other order.

When you open the “Pivot Table editor” on the right side of the window, you have two dropdowns to sort the data within each field you have added to the rows and columns.

The first dropdown says “Order”, which shows you have the order to define for sorting, and the second one is “Sort By” to select the column you want to use for the sorting.

You can even have multiple-level sorting. In the above example, we have the two fields in the rows “Region” and “Representative.” And we have used descending order to sort both fields.

Add Calculated Field in a Pivot Table

Creating calculated fields in a pivot table in Google Sheets lets you perform custom calculations with your data directly in the pivot table. You don’t need to change the source data.

• Click on the pivot table to open the “Pivot Table editor” on the right. Under “Values,” click on “Add” next to “Values” and then choose the “Calculated field.”

• In the “Formula” input bar, enter the formula you want to use. You can use the names of the fields from your pivot table. For example, you can use a formula (=Total/Units) to get the average value of the unit in the pivot table for the region and representative-wise.

• Once you enter the formula, you can change the column’s name to something more meaningful. Just edit the column header, type the new name there, and then hit enter to save. And the moment you do this it will also change the name of calculated field in the “Pivot Table editor”.

Using Conditional formatting in a Pivot Table

Conditional formatting is helpful when you want to apply formatting to some specific cells based on their values. Let’s say you want to apply the color scale to the pivot table to show low or high values.

• First, select the value cells in your pivot table, go to “Format” in the menu bar at the top, and click “Conditional formatting.” A sidebar will open on the right side of your screen.

• Now, you have two tabs in the sidebar that allow you to create a conditional formatting rule. Now, you need to click on the “Color Scale”. This option will enable you to apply a multiple-color scale to the selected cells in the pivot table.

• From here, click “Preview” to select the color shade you want to apply. Here, we use the “Red to White to Green” shade.

• Finally, at the end, once you are done with the formatting, click “Done” at the bottom sidebar to apply your formatting rule.

If you want to add more rules, you can click “Add another rule” and repeat the steps we have used above.

Note – It is great to use color scales with a pivot table as they do not clutter the table but give it a clean, organized look. Color scales make numbers a visual spectrum, making it easier to see high and low values visually.

Note – Using conditional formatting in the pivot tables can slow down your spreadsheet, especially if the rules are complex.

Grouping data in a Pivot Table in Google Sheets

Grouping data in a pivot table in Google Sheets allows you to consolidate and analyze summarized data. Grouping is useful when you have detailed data you want to view in groups. In a pivot table, you can create two major types of groups: date groups and number groups.

Grouping Dates: Let’s say you have a column with dates in a pivot table; you need to right-click on it and select create pivot date group. Depending on how you want to summarize the data, choose the grouping option, such as by year, month, or day. You can also select multiple grouping options, such as year followed by month.

Grouping Numbers – Right-click on any cell in the column where you have a number and want to group those numbers. In your example, we have the “Order ID” number that we need to group. Right-click and choose “Create pivot group rule”. Enter the range and the interval for your groups. For example, if you are grouping Order ID data and want to group the data into intervals of 10 (0-10, 11-20, etc.), you would specify these intervals.

>>>Sample Sheet<<<