You Are Not Using 100% Feature Of Google Sheets Pivot Table – A Complete Guide

Hello friends!!

Pivot tables are a very exciting feature in Google Sheets; but if you are unaware of Google Sheets Pivot Table and their multiple thrilling uses then do not worry.

In this pivot table Google Sheets guide I am going to explain all tricks which will give you the confidence to start using pivots in your work.

So, without wasting any further time, let’s disclose all secrete of this powerful feature.

What is the Google Sheets pivot table?

Pivot table in Google Sheets is an interactive way to summarize large data in Google Sheets quickly. You can use the Pivot table to evaluate

  • Numerical data in detail.
  • Answer unanticipated questions related to the data.

A Pivot Table Is Designed For

  • Querying large data in multiple user-friendly ways.
  • Subtotaling numeric data.
  • Aggregating numeric data.
  • Summarizing your data by categories or sub-categories.
  • Creating custom calculations & formulas.
  • Expanding or collapsing levels of your data to focus on results, and to drill down it to details from summary data to the areas of your interest.
  • Pivoting i.e., moving columns to rows & rows to columns, to see various summaries of the source data.
  • Conditional formatting, grouping, sorting and filtering the interesting and very useful subset of data which helps you to focus on the information you need.
  • Presenting attractive, annotated, and concise printed or online reports.

Let’s look at a simple example to demonstrate how a pivot table looks and work.

Google Sheets Pivot Table

Now, if you want to summarize this data and want to answer questions like how many Unites are sold by Bishop? And what is the sum of units sold by Bishop?

For this, you can simply use formulas

  • COUNTIF – To count the number of sales done by Bishop
  • SUMIF – To get the sum of units sold by Bishop.

However, if you want to same data for Lee, Parker, Pullen, and Watson then you have to modify your formulas again and again. Which I know is very time-consuming and not so interesting.

Now, to make it easy and interesting you can take advantage of a pivot table where you can see all this data in a single go.

Google Sheets Pivot Table

Here the google sheet pivot table summarizes the data for each salesperson.

It counts how many sales are made by each salesperson and gives the total for it.

After that, it adds up all the units sold by the salesperson and reflect against each salesperson name

Google Sheets Pivot Table

How To Create A Pivot Table In Google Sheets?

By looking at the above data, you might be wondering how you can do the same. Don’t worry as here in this complete guide I am going to show you extract steps of creating this pivot table with only 9 mouse clicks. Let’s get started

  1. Copy the data from the sheet to your blank google sheet. [Step not counted]
  2. Click anywhere inside of your table data. [Step not counted]

(Now we can start counting the steps)

  1. On your Google sheet menu bar, click on Data and then Pivot table. [Click 1 & 2]Google Sheets Pivot Table
  2. A popup window will appear asking where you would like to create your pivot table. Always choose a new window (it is easy to manage pivot table in a new window as it will give you clear data). [Click 3]Google Sheets Pivot Table

(A new window will create in your Google Sheets named ‘Pivot table 1’ (or 2,3……. Etc. as you create multiple pivot tables).

  1. In the new tab of Google Sheets, you will see a pivot table editor on the right side of the sheet. Google Sheets Pivot Table
  2. In that editor under rows, add Sales Person. [Click 4 & 5] Pivot Table Google Sheets
  3. Now under values add Sales person again. [Click 6 & 7] Pivot Table Google Sheets
  4. And under values again add Units. [Click 8 & 9] Pivot Table Google Sheets

Yes, you did it!

With only 9 mouse clicks you have a summarized report of your data which is ready to give you fresh insight.

Note – If you are thinking this is a very small data set, what if you are having a very large data set in front of you. How you will create a pivot in that???
The answer is very simple. Just apply above mention 9 steps and your job is done.

Google Will Build A Pivot Table In Google Sheets For You

Influence the magic of Google Sheets built-in AI !!

When you will create a pivot table in Google Sheets then you will always notice that there will be an automatic suggestion area in Pivot table Editor which will suggest you some pre-build Google Sheets pivot table for you.

Pivot Table Google Sheets

With the help of this automatic suggestion, you can create a pivot table with a single click.

Isn’t it amazing??

This is a very easy and useful way of creating pivot tables in Google Sheets.

Also, you will be surprised to know that Google Sheets is blessed with an Explore tool that can build a pivot table in less than above mention 9 steps or you can say Google Sheets Pivot table will be created automatically.

You will find explore tool on the bottom right corner of the Google Sheet (it will be a star-shaped button). You can also use keyboard shortcut (ALT + Shift + X) to open explore tool.

Google Sheets

After clicking on this star button, explore window will open where you will see a few suggestions and you can select the suggestion as per your need.

Pivot Table Google Sheets

This will also have access to the Pivot table which you have just created

Pivot table in Google Sheets

You can click on any of the suggestions and then click on create a pivot table.

Note – I know this method is very easy, however, I will recommend you to manually create your pivot table in Google Sheets as it will boost your skills. However, if you wish to use an automatic Google Sheets Pivot table, then it is a good idea to learn how it works as you should understand what data it is reflecting.

To embrace you more, let’s take a deep look at building Google Sheets Pivot Tables in more detail.

Fundamentals: Pivot Table Google Sheets

Whenever you create a pivot table from a tabular data set, all the columns from that data set are available to use in the Google Sheets Pivot table.

Pivot Table – Rows, Columns & Values

Rows, columns & values are always at the heart of any Google Sheet pivot table.

Rows

Pivot table in Google Sheets

  1. Whenever you click add under rows, a list of column headings of your table will appear.
  2. After selecting one, the Google Sheets pivot table will include all unique items from the selected column in your pivot table as a row heading.
  3. As per the above example, it will take all rows of salesperson data and squashes it down to just five rows i.e., the five unique salesperson name.

Columns

Pivot table in Google Sheets

Including columns, in the pivot table Google Sheets reflects the same effects as adding rows, but in the form of columns. Here the values are displayed in aggregate form (for each column)

Values

Pivot table in Google Sheets

  1. After clicking on values, you will be presented with the same columns headings.
  2. When you select one, you are telling Google Sheets pivot table to summarize that particular column. Example – If it is a list of units, you may want to sum it up or calculate the average of it.
  3. This will happen when you add values in a pivot table i.e., the data will be summarized. All individual values from every single row will combine in a single value. (i.e., aggregated)
  4. Now if you have anything in the Row section, the aggregate will be calculated on that level.

Note – You can also drag and drop the fields in the Google Sheets pivot table to easily move them.

Totals

In the Value column of your Google Sheets pivot table, you can easily toggle total ON or OFF. This can be done by enabling a button in the row section.

Sorting

This option in Google Sheets can be found in the ROW section of the pivot table.

Every row field can be used to sort and every row has its sorting options. To do this

  1. You need to select which row field you want to sort i.e. under the sort by option. Pivot table in Google Sheets
  2. There is another sorting option under order where you can select from ascending or descending option. Pivot table in Google Sheets

Tips And Tricks: Pivot Tables In Google Sheets

So, this was the basics for Google Sheets Pivot Table. Now, I will tell you some cool tips and tricks of Google Sheets Pivot Table. For this, we will be using the below data set. You can grab it by clicking here.

Pivot table in google sheets

Multiple Values Fields In Pivot Table Google Sheets

First, let me make it clear, you can add any value columns as per your need.

Let’s say, you may wish to count items sold by a particular salesperson and the average price of each sale. Then you have to follow the below steps.

  1. Under the row, section adds property type and sort it in ascending or descending order as per your need.
  2. Now, under the value section add Property type and summarize it by COUNTA function.
  3. After that again add sales price and summarize it by SUM function.

Pivot table in google sheets

Note – You can easily drag any field to make changes as per your requirement.

Changing Aggregation Type in Google Sheets Pivot Table

Suppose you want to get the aggregate of the sales price in your pivot table along with the sum. How you can do it? To do this

  1. Add Sales price again under values.
  2. Now you have to summarize it by AVERAGE function and you will get the average sales price in the same pivot table.

Pivot table in google sheets

Note – Under summarize by option there are multiple function options and you can select any of the function options as per the requirement.

pivot table google sheets

Adding Filters In Pivot Table Google Sheets

Conceptually, adding a filter in a pivot table is the same as an adding ordinary filter in the Google Sheets Pivot table.

These are used to show a subset of the data based on any condition.

Like, for the above data set you may want to show data for only Ice cream and Frozen Yogurt. Here are the steps.

  1. Under pivot, the table editor got to the filter section and add the area where you want to apply the filter. In the current data set, we want to apply the filter for the item type. pivot table google sheets
  2. After this, all the options under item type will be visible to you under your Google Sheets Pivot Table editor. pivot table google sheetsGoogle Sheets Pivot Table
  3. Now, select Ice cream and Frozen Yogurt. Google Sheets Pivot Table
  4. After doing this, salespersons who have sold only these two items will be reflected automatically with COUNT, SUM, and AVERAGE of sales.

Note – You can select any number of items as per the requirement.

Multiple Categories In Google Sheets Pivot Table

Till now we have added only one field under our row section. But what will happen if we add multiple fields under the row section? Let’s have a look.

On adding the first field you will get a unique list of items.

After adding the second row, new items will appear as a sub-category. (It happens to show a unique combination of two fields in the Google Sheets Pivot Table)

Google Sheets Pivot Table

Do not forget to experiment with Google Sheets Pivot Tables. Try to get different data with multiple combinations and take a moment to understand what you are getting with each pivot combination.

Leave a Reply