Are you struggling with a large set of data on a huge table that is too much to handle? You can narrow down the table and lets you group data in a meaningful way. Pivot tables help you to quickly and efficiently analyze huge amounts of data.
Here, let’s take a detailed look at creating and using a Pivot table in Google Sheets.
What is Google Sheets Pivot Table
While normal tables are ideal for handling large amounts of data, it is quite difficult to analyze or get meaningful information from them. Pivot tables come in handy as they can summarize massive amounts of data on any rows/columns in a spreadsheet.
For example, a pivot table can be used by a business owner to analyze which store has made maximum sales revenue for a specific month. In general, a pivot table can be used to calculate averages, sums, or any other statistics from a large data set.
How to Create a Pivot Table in Google Sheets
Let’s see how to create a Pivot table from your existing table or large data set. For example, let’s take a simple data set that has the sales information from various divisions of the company for a specific month. Before starting to create a pivot table, you need to make sure that every column is associated with a header/title.
- Open the Google Sheets spreadsheet file on your browser.
- Select the data for which you need to create a pivot table.
- To use all the data in the sheet, then just click any of the cells.
- Next, click Data > Pivot Table located on the top menu.
- Select New Sheet and click Create.
- Now, you will get a new sheet Pivot table editor with options to add rows, columns, and values.
Now, let’s see how to get meaningful information out of this data set using the Pivot table we just created.
How to Edit a Pivot Table in Google Sheets
After creating a new pivot table, you can sort and find out the information you want from the data. Let’s take the sample data set mentioned above. Here, we will find out the total number of units sold by every division and the average price per unit using the pivot table in Google Sheets.
- Create a new pivot table for the spreadsheet.
- Under Rows, click Add and enter “division”.
- Click Add again and enter “subdivision”.
- Make sure the value for the drop-down box titled Order is set to Ascending.
- Next, let’s calculate the total number of units sold by every division. Under Values, click Add and enter the “number of units”.
- Then, select the value SUM in the drop-down box titled Summarize by.
- Now, a new column titled SUM of number of units will get added to your sheet.
- Next, let’s calculate the average price per unit. Under Values, click Add and enter “price per unit”.
- Then, select the value AVERAGE in the drop-down box titled Summarize by.
- Here, you will see a new column titled AVERAGE of price per unit which shows the average price per unit and the pivot table will be created like given below:
Likewise, you can use other formulas like MAX, MIN, AVERAGE, MEDIAN and more.
How to Customize a Pivot Table in Google Sheets
Google sheets provide a lot of options to customize the pivot table. Let’s take a brief look at some of the most commonly used options:
Sort Rows or Columns
You can sort the pivot table data by values, row, or column names. In the Pivot table editor window, you will find the “Sort by” drop-down box which lists the names of all rows and columns of your pivot table. Based on your need, you can sort the column or row based on your requirement.
Show Value as Percentage
By default, values (eg. SUM of the number of units) will be displayed as numbers. However, if you would like to display them as a percentage by comparison with whole data, then you can do that as well. Just click the Pivot table. Under Values, click the drop-down box titled Show as and select any of the options given below:
- % of row
- % of column
- % of grand total
By default, the drop-down box is set to “Default”.
You can select a set of values from the pivot table in Google Sheets and group them together based on a rule or manually. To manually create a Pivot group, select all the cells you want to group and right-click the cells. Then, select the Create Pivot group.
In order to group rows by rule, right-click a cell and select Create Pivot group rule. Then, you will see a small window titled Grouping rule. Enter minimum value, maximum value, interval size, and click OK. Now, the values are grouped based on the rule you had created.
If you want to ungroup the data, right-click any grouped cell, and select Ungroup pivot items.
How to Filter Data in a Pivot Table in Google Sheets
Are you working on a spreadsheet with large amounts of data and you would like to hide some rows/columns? You can make use of the Filter option to hide unwanted data on your sheet. Let’s see how to do that.
- Click the Pivot table to launch the Pivot table editor.
- Under Filters, click Add.
- Select the rows or columns you want to filter from the data.
- Click the down arrow in the drop-down box titled Status.
- Choose Filter by values or Filter by condition, depends on what you need.
Use Filter by values when you just want data that have values between a specific range or from a domain. If not, you can choose Filter by condition and create a custom formula to filter data.
When you have large amounts of data on a spreadsheet, no need to look at it as a whole. Using the Pivot table on Google Sheets, you can easily cluster them and find out the necessary information quickly. Hope you found this helpful to summarize your data.