Pivot Table In Google Sheets

What Is Pivot Table In Google Sheets?

Pivot Table in Google Sheets is a table retrieved from an existing dataset that allows users to swiftly summarize, analyze and create consolidated summary reports from huge data sets with just a few clicks.

The Google Sheets Pivot Table extracts data and helps users design, reorganize, summarize data in categories and sub-categories, apply filters, conditional formatting, slicers, subtotals, aggregate numerical data, execute other complex calculations on it, etc.

For example, we have the names and their exams percentage of five students. Let us create a Pivot Table for the dataset.

Select cells A1:B6 and insert the Pivot Table in the same worksheet in cell D1.

We will get the output shown above. The Pivot Table is generated in cells D1:E6 and the sum of the percentages are calculated as well.

How To Create A Pivot Table In Google Sheets?

The steps to create a Pivot Table in Google Sheets as follows:

Step 1: Choose a dataset with column names or headers – select the “Insert” tab – click the “Pivot table” option, as shown below.

Step 2: The “Create pivot table” window appears, as shown below. Now, go to the next step.

Step 3: We can select any of the following options where we want our Pivot Table.

• If we select the “New sheet” option, then the Pivot table generates in a new worksheet.
• If we select the “Existing sheet” option, then the Pivot table generates in the same worksheet where the source dataset is. But it will ask for the result cell to generate the same, as shown below. We can provide any cell either beside or below the dataset.
• Finally, select the “Create” option.

Step 4: Now the Pivot Table outline appears. Then the “Pivot table editor” window appears on the right, as shown below.

Now, we can add the required data to view in the Pivot Table.

Examples

Let us consider some Pivot Table in Google Sheets examples by creating a Pivot table, sorting or filtering the extracted data, creating a dynamic pivot table and apply conditional formatting too.

Example #1 – Create a Pivot Table

The dataset given below consists of fruits, their grades and the quantity ordered and we will create a Pivot Table in Google Sheets.

The steps to create a Google Sheets Pivot Table based on the source dataset are as follows:

Step 1: Select the cell range A1:C13 – select the “Insert” tab – click the “Pivot table” option.

Step 2: The Create pivot table window appears, displaying the chosen data range in the Data range field. Select “Existing sheet” and provide cell E1 as the location to generate the Pivot table.

Step 3: Click “Create” in the “Create pivot table” window to generate the Pivot table and to open the “Pivot table editor” window on the right, as shown below.

Step 4: In the “Pivot table editor” pane, select the rows as “Fruit”, Columns as “Grade.” Select values as “Order Quantity” to create the required Pivot Table in the target cell, as shown below.

Step 5: Close the “Pivot table editor” to view the generated Pivot Table, as shown below.

Example #2 – Sorting Data in Pivot Table.

The following dataset consists of five students’ Physics, Chemistry and Biology test scores. We will first generate a Pivot table and then sort the required data.

The steps to create a Google Sheets Pivot Table based on the source dataset are as follows:

Step 1: Select the cell range A1:C16 – select the “Insert” tab – click the “Pivot table” option.

Step 2: The Create pivot table window appears, displaying the chosen data range in the Data range field. Select “Existing sheet” and provide cell F1 as the location to generate the Pivot table.

Step 3: Click “Create” in the “Create pivot table” window to generate the Pivot table and to open the “Pivot table editor” window on the right, as shown below.

Step 4: In the “Pivot table editor” pane, select the rows as “Names”, Columns as “Subjects. Select the values as “Marks” to create the required Pivot Table in the target cell, as shown below.

Step 5: Close the “Pivot table editor.” It is to view the generated Pivot Table, as shown below.

Step 6: Let us sort the data in the Pivot table. Remember that we can sort a data alphabetically or numerically, in ascending or descending order. Click on the “Edit” option or button below the generated pivot Table to open the “Pivot table editor”, if we have closed it, as shown below.

Step 7: We get the selected options and under each option, we see an “Order” drop-down and the “Sort by” drop-down. Any required data can be sorted. Here, let us sort the names in an order. Therefore, in the “Rows” selection, select the “Descending” option, as shown below, because the names are already in alphabetical or ascending order.

The Pivot Table sorted in descending order, is shown below.

In a similar way, we can sort any required data in the generated Pivot Table.

Example #3 – Creating Dynamic Ranges in Pivot Table. 

The following dataset consists of 10 US cities their states and population. Let us create a Pivot table for 

the data and then modify the source data and make the Pivot table dynamic, to reflect the updated data.

The steps to create a Google Sheets Pivot Table based on the source dataset are as follows:

Step 1: Select the cell range, A1:C11 – select the “Insert” tab – click the “Pivot table” option.

Step 2: The Create pivot table window appears, displaying the chosen data range in the Data range field. Select “Existing sheet” and provide cell E1 as the location to generate the Pivot table.

Step 3: Click “Create” in the “Create pivot table” window to generate the Pivot table and to open the “Pivot table editor” window on the right, as shown below.

Step 4: In the “Pivot table editor” pane, select the rows as “US City”, Columns as “US State”. Select values as “Population” to create the required Pivot Table in the target cell, as shown below.

Step 5: Close the “Pivot table editor” to view the generated Pivot Table, as shown below.

Step 6: We must remember that any data modification within the dataset, like inserting or deleting new data or existing data, respectively, will update the Pivot Table. 

However, when we add new data after the dataset or outside the existing dataset, then the generated Pivot Table will remain untouched, meaning it will not get updated. 

In such scenarios, we must convert the Pivot table into a dynamic one, so that it can update any newly added data outside the dataset.

Let us add 5 more cities data, as shown below, in cells A12:C16. Here, we can see that the Pivot table remains the same.

Step 7: Let us make the Pivot Table dynamic in order to accept the new data. Click the “Edit” option. below the existing Pivot Table and to open the “Pivot Table Editor”.

The “Pivot table editor” pane appears on the right with the initial data source range, A1:C11.

Step 8: Click the “Select data range” option and the “Select a data range” window appears, as shown below.

Step 9: In the above window, either manually edit the cell range as A1:C16 or select the cell range along with the newly added data and click “OK”, as shown below.

Finally, we get the updated pivot table with the newly added data, as shown below.

Please note that the Pivot Table created using the initial source dataset lists the US cities in alphabetic order. Notice that the newly added data too is sorted in the Pivot Table.

Example #4 – Conditional Formatting in Pivot table.

We will consider the exam results of five students and apply Conditional Formatting rules in the Pivot Table, as shown in the below data.

The steps to apply Conditional formatting to a generated Pivot Table are as follows:

Step 1: First, let us generate a Pivot table. Therefore, choose the cells A1:B6 – select the “Insert” tab – click the “Pivot table” option.

Step 2: A “Create pivot table” window appears. Here, choose the “New sheet” option and click the “Create” button, as shown below.

Step 3: The Pivot Table gets generated in the new worksheet without any data, as shown below. However, the “Pivot table editor” pane opens at the right side of the window.

Step 4: Let us add the data as follows: Select the rows as “Names” and Values as “Percentage” to create the required Pivot Table in the target cell, as shown below.

The final Pivot table will be as shown below.

Step 5: Let us now apply the “Conditional formatting” as follows:

Choose the data range of the generated Pivot table, i.e., A1:B6, select the “Format” tab – click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Step 6: Now, select the “Single color” tab and under the “Format rules” section,

• First, click the “format if…” drop-down, select the “is between” option and enter 65% and 70% in the fields that appears as soon as we select one of the options from the drop-down.
• Next, in the “Formatting style” options select the color “Yellow” from the “Fill color” option.
• Finally, click the “Done” button, as shown below.

We get the output shown below.

Important Things To Note

• Sometimes, the Pivot Table does not update automatically. We must manually refresh the Pivot Table or change the data source to update it.
• When we update a Pivot Table to include data newly added to the source dataset, the Pivot Table in Google Sheets displays the new data in a sorted order. 
• If the source dataset is modified or added in-between, we do not need to change the data source range for the Pivot Table to refresh it. Instead, we can use the Refresh option to update it.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Pivot Table in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is the Pivot Table In Google Sheets. We learn how to create it from large datasets for summarization with detailed examples. You can learn more from the following articles –

Freeze Panes In Google Sheets

ISERROR In Google Sheets

TODAY In Google Sheets