In this article, you will learn about the fundamentals of pivot tables in Google Sheets and elaborate how the pivot tables feature the necessary data from a huge data pile. Spreadsheets are excellent and efficient tools for working with data and analyzing them. But, when you load them with huge amounts of data, it becomes difficult to summarize, average, aggregate, count, or sort data with spreadsheets. Now, to resolve this issue, you need a pivot table.

The pivot table provides the methods of working with different mathematical operations (e.g. summation, average, etc.) on any data set. The original/source data of the table is located in another location of the spreadsheet, and your pivot table will present the data in a more accessible way.

You can use pivot tables for different purposes like financial reporting, ERP applications, statistical analysis, and more. Here, you will learn how to create pivot tables in Google Sheets and how they will help in your regular workflows.

How to add/edit pivot tables in Google Sheets?

  1. First, open a spreadsheet in Google Sheets from your computer.
  2. Next, select all the cells with their source data.
  3. Then, click Insert > Pivot table.Insert a sheet Fundamentals of Pivot Tables in Google Sheets
  4. After that, a Create pivot table panel will appear. There you will get two options - New sheet and Existing sheet. Choose anyone as per your requirements.
  5. Finally, click Create.Create a pivo table with Fundamentals of Pivot tables in Google Sheets Article
  6. Then, you will find a side panel with ‘Rows’ or ‘Columns’. Here, click Add to choose a value.Click Add to choose a Value with Fundamentals of Pivot Tables in Google Sheets article

How to insert rows and columns in the pivot tables in Google Sheets?

Suppose, you want to show the Product Name and Product Type in your pivot table, then here is a process –

  1. To create a row first head over to the Pivot table editor panel.
  2. Then, click Add beside the Rows.
  3. After that select Product Name. Google Sheets will automatically take the source data of the clients’ names.Select Product Name with Fundamentals of Pivot Tables in Google Sheets articleThen you have to add the columns with the same method. That means –
  4. Go to the Pivot table editor panel.
  5. Then, click Add beside the Columns.Add the column with Fundamentals of Pivot Tables in Google Sheets article

Note: Google provides automated suggestions for recommended pivot tables according to the data you have chosen. Here, you can choose a pivot table under the ‘Suggested’ section.

If you don’t require suggestions, you can turn it off. To turn off the suggestions, you should click Tools > Autocomplete. Then, turn off the Enable Pivot table suggestions. You will see the tick marks in front of every option after disabling the suggestions.Enable Pivot table with Fundamental of Pivot Tables in Google Shreet Article

How to change/remove data from the pivot table?

Oftentimes you might need to update or delete data from the pivot table as per your requirement. For example, when you don’t need any old data, you need to update the pivot table. Now, to do that, you can either change and replace the data with new information, or simply delete them and include what you want. Either way, you need to know how to edit and manage data on a pivot table, so here is a detailed outline of the process –

  1. Open the spreadsheet of Google Sheets where you have the pivot table.
  2. Then, click the pop-up Edit button beneath the pivot table.
  3. Now, you can change or remove the fields from the side panel.
  • You can drag a field to another category when you want to move it.
  • If you want to remove the field, click Remove.
  • Also, you can change the data range of the pivot table.In this case, users can adjust the data range by clicking Data Range in the pivot table editor.
  • If you prefer to clear these cells, then click on Clear all

See the details of a cell

  1. To see the details of the cell, double click on the cell. 
  2. Finally, you can see a new sheet that incorporates the details of the particular cell.How to change data from the Pivot Table with Fundamental of Pivot Tables in Google Shreet Article

How to use a custom formula in a pivot table?

  1. To use a custom formula, go to the Pivot table editor from the pivot table. 
  2. Next, click Add > Calculated field from the next of the side panel. How to use custom formula with Fundamental of Pivot Tables in Google Shreet Article
  3. Select the Calculated field.Find the calculated field with Fundamental of Pivot Tables in Google Shreet Article
  4. Then, click the down caret under Sort by and choose the custom formula that you want. For example, if you want the summation of your data, then choose SUM.Calculated the Sum with Fundamental of Pivot Tables in Google Shreet Article

Benefits of using pivot tables

  • Pivot tables let you handle huge amounts of information quickly with the drag-and-drop methods. As a result, you can analyze the most crucial data and organize them effectively. Thus, you can summarize heaps of data clearly and concisely.
  • It automatically retrieves data from the source and presents it quickly. As a result, you can save time from the manual process of retrieving any data from its source. It uses formulas to transform raw data into workable information, and as a result, you can complete your task within a strict timeline.
  • These tools make your data reliable and precise. Thus, you will be able to create accurate reports from your data.
  • As pivot tables present accurate data, they help you make quick business decisions, which ensure business growth and success.

Customize your workflow with xFanaticalForesight automation tool

We often use several manual, repetitive, and time-consuming workflows in Google Workspace. Thus, xFanatical Foresight Automation tool streamlines your regular repetitive tasks in Google Workspace and optimizes your business procedure. This SaaS platform helps Google Admins and users efficiently handle their workflows without any prior coding knowledge. That means everybody can use this tool, including people without a technical background. 

So, embrace the revolution of productivity and growth with Foresight. It has excellent scalability, which helps to meet your needs. You can find several use cases of Foresight. Among them, you may learn Apps Reporting and getting alerts in Google Workspace, etc.


Now, you have understood how to create pivot tables in Google Sheets and how they help in data analysis. These tables can help you organize your data efficiently in a short span. Additionally, learning about the Foresight automation tool is also essential, as it automates your regular Google Workspace workflow-related tasks. 

So, try out a 14-day free trial and learn to automate your repetitive workflows.

Related Articles: