Everything about Pivot Table in Google Sheets
With 9+ years in SEM and data automation, Piyush Nair helps businesses and individuals simplify processes using Google Sheets, Excel, Apps Script, and other productivity tools, turning data into actionable insights
How this article is generated : This article was written by a human author and AI is used in proofread.
Introduction
There are thousands of articles related google sheets and pivot table in texts but why you would bookmark this article provide screenshot of every small step
Well I have created this article to make sure you do not miss out on any scenarios like –
- What is a pivot table
- Raw Data check points before creating pivot table
- How to create pivot table
- How to organise data in Pivot table
- How to refresh pivot table
- How to sort data in pivot table
- How to filter data in pivot table
- How to use calculated field in pivot table
- How to create pivot table from multiple sheets
What is a Pivot Table in Google Sheets
Pivot table is a tool in spreadsheet softwares which helps you to summarize the data in faster and clean way. It is used to summarize, analyze and organise large data set quickly
Observations like “The total sales done by Marketing team was highest with sales amout of $2700” is a result of pivot table analysis.
Raw Data check points before creating Pivot Table in Google Sheets
Before creating a pivot table first check for possible errors in your raw data because it may impact your pivot table output
- Blank cells – Empty cells in column or in rows
- Duplicate entries – Totals will be inflated, giving false insights
- Inconsistent data formatting – It may identify same item as different due to different data formatting
- Incorrect data types – Calculation error may occur
- Hidden charcters – Even if there is a space added as prefix or suffix in a name it will be treated differently
- Formula errors like in source data – Pivot table may skip those rows or show errors in calculations
How to create Pivot Table in Google Sheets
Open your raw data tab present in your Google Sheets
Click anywhere on your raw data and press CTRL+A it will automatically selects your entire data table
Click Insert on the top menu bar and select Pivot table option from the drop down
Now Google Sheet will ask where to place the pivot table before creating it. You have two options you can place it in New Sheet or in any Existing sheet.
If New sheet is selected then it automatically creates a new sheet and place pivot in cell A1
If Existing Sheet is selected then you can confirm the cell location by clicking the small grid symbol shown below
Once you click on the grid option
It will ask you to select any cell , make sure you see cursor blinking before you select cell on the Google sheets
I have selected cell G1, Now it will automatically takes the cell range
Then click on ok then click on Create button
Pivot table will appear on the same cell as shown below
How to organise data in Pivot table in Google sheets
Now dont get overwhelmed with the column names and rows in the raw data, before creating pivot write a sentence what exactly you want to achieve from this table. What insights you want to check only after that starting organising. I learnt this the hard way so as an advise always make sure you know what you want to see.
For this article we are considering below data set and I want to know which department – Which expense type on Date 2025-01-06 is spending highest . It means in rows we will have department and column expesne type in values we will have Expense and in filter we will have Date
Now lets start organising data in the pivot table
When you click on pivot table on right side click on Add button adjacent to Rows and select Department
You can see that in the pivot table department nameshave been populated
Now you want expense in the values against each department then select Expense In values
If you want to divde the see which department spending how much amount on which expense type then just bring expense type in columns
How to filter data in Pivot Table in Google sheets
Our pivot is almost ready but I only want expense amount department wise for each expense type for only date = 2025-01-06
In filter section bring Date column and select the date you want , pivot will filter out everything and will show data for that date
How to refresh Pivot Table in Google Sheets
Let’s say you open your pivot again tomorrow and now there are more entries in the raw data tab. For this example I have added a same row at the bottom og the table and highlighted as yellow.
You can see in the pivot table currently the total expense is 1200 but it should be 2400.
As new data rows are added we need to increase the pivot data range and for it you have to chnage the data from ‘Data-1’!A2:E12 to ‘Data-1’!A2:E13 and it will update the data automatically as shown below
How to sort data in Pivot Table in Google sheets
Before sorting we should keep following things in mind –
- Columns name
- Ascending / descending ( used in when sorting a numeric data type )
- A to Z ( used in when sorting a text daya type )
If we want to sort the pivot table by sum of expense sorted in descending order for Department Row then select SUM of Expense as shown below
If we want to sort the pivot table by sum of Department name only then select Department in the drop down shown as below
How to use calculated field in Pivot Table in Google sheets
You will find calculated field in the Values section. Go to values click on Add then click on Calculated field
After this you will see an option to enter your custom formula
When you want to write custom formula remember that we have to start with “=” sign then column names and we can sum add divide multiple numbers as we do it in Google Sheets cells
Examples –
How to create Pivot Table from multiple sheets in Google sheets
If you have data in different tabs / sheets and they have same column in same sequence then we can create pivot table from multiple Google Sheets
Step 1 – Combine the all tabs data using Query formula so that you dont have to combine again and again
Step 2 – When all tabs data starts populating in same tab then create pivot table
Create a new tab – All-data
In cell A1 = Create a formula like this
={QUERY(‘Data-1’!$A$1:$E,”select * where A is not NULL”);QUERY(‘Data-2’!$A$2:$E,”select * where A is not NULL”);QUERY(‘Data-3’!$A$2:$E,”select * where A is not NULL”)}
Lets understand, in google sheets if we have 3 data tables in 3 different tabs then we can club all data including all blank rows using ={ datatable1 ; datatable2 ; datatable3 }
Now as it includes all rows including blanks hence we are using query formula to filter rows which have data in it.
To understand it in detail you can also watch the video tutorial below
That’s It.
Conclusion
I have included all the aspects which are pratically useful and important when it come to pivot tables in Google Sheets. I would adcise you to try creating your first pivot and understand it well and improve your efficiency