PIVOT table is one of the most powerful feature in Google Sheet. Using PIVOT table you can create your own extract from a large detailed data set. In this tutorial we will learn different behaviors of Pivot tables.
In our example we have the below table containing 5 columns as you can see.
Now select the entire table and go to ‘Data’ menu and click ‘Pivot Table’.
Now in the Create Pivot table window select the source table/range on which you need to create the Pivot table. Since here we have already selected our initial table, so it’s pre-populated.
Here we also need to select whether we want the PIVOT table in a new sheet or existing sheet. We have selected the current sheet and also specified the cell G1 where we need the PIVOT table to appear.
Now click Create.
The empty PIVOT table has been created in cell G1 as shown below. Along with the empty PIVOT table, in the same sheet Pivot Table Editor is also displayed.
Now to build the pivot table, Click Add button corresponding to Columns, Rows, Filters or Values. The Filters area is used to apply global filters to the pivot table.
- Now first add the Amount for the VALUES.
It calculates the sum of all Amount values in the entire table.
2. Then add the Product for the ROWS.
It breaks out the amount by Product. See there are multiple entries of product Banana, Egg & Tomato in source table. In PIVOT table the the sum of amount for each product has been calculated & displayed.
3. Now to apply the currency on the amounts in PIVOT table select the column and in Format menu navigate to ‘Number’ then ‘More Formats’ and click ‘More Currencies’.
4. You can select the desired currency from the Custom Currencies pane. Here we have selected ‘US Dollar’. You can also choose the appearance and the number of decimal places upon clicking the arrow marked place in the below picture. Finally Click Apply.
5. Below you can see the currency and other format changes have been applied to the Sum of Amount column of PIVOT table. The currency format will continue to be applied to Sum of Amount values, even when the pivot table is reconfigured, or new data is added.
6. To sort the PIVOT table on Sum of Amount column,select the column and in Pivot table editor choose the the desired sorting order (Ascending or Descending)
Note in ‘Sort by’ the ‘Sum of Amount’ should be selected.Here we have sorted the Sum of Amount in Ascending order as follows.
7. Now we have updated the amount in source table for products Mango as 50 and Egg as 30. In PIVOT table the same has been automatically updated(remember for Egg the sum has been updated as there are multiple entries for Egg in the source table). Please note, since it has been sorted from smallest to largest on Sum of Amount, even after the change of values it is still sorted.
8. Now we have added one more column in VALUES. That is Unit. In PIVOT table, Sum of Unit column appeared.
9. Now let’s say, you have a requirement to display the Sum of Unit as “% of grand total”. You can do it by selecting “% of grand total” in the ‘Show As’ drop down for the Unit under VALUES in Pivot Table editor’
10. Now close the Unit under VALUES to remove it from VALUES and add the Amount to VALUES for one more time in the Pivot table editor.
11. By this way a second column named ‘Sum of Amount’ has been created in PIVOT table. And then we have chosen to show the values of 2nd Sum of Amount as “% of grand total” as follows.
12. Now remove the 2nd ‘Sum of Amount’ which is the “% of grand total” from VALUES. Add ‘Category’ to COLUMNS in Pivot table editor. Here comes the two-way pivot table that breaks down the amount by product and category as follows.
13. Now Swap Category and Product (i.e. drag Category to the ROWS and Product to the COLUMNS). Here comes another two-dimensional pivot table as follows.
Now we will learn how to build a pivot chart which is the visual representation of a pivot table in Google Sheet. The Pivot charts can be built based on the two dimensional Pivot table like the above one we have.
- To insert a pivot chart, Select the pivot table. Then Click ‘Chart’ in the ‘Insert’ menu.
- The PIVOT chart will appear as follows. Please note, any change you make to the pivot chart will immediately reflect in the pivot table and vice versa.
- Now in PIVOT table editor add ‘Category’ to FILTERS.
The default status is ‘Showing all items’.
- Now in FILTERS select only ‘Non Veg’ in the status for ‘Category’ and click OK.
As you can see below, in Non Veg category we have got the chart for the products Egg and Chicken which only belong to Non Veg Category. Please Note here the filter is being applied in both Pivot table and Pivot Chart.
- Remove ‘Category’ from FILTERS and add ‘Product’. Now filter the ‘Product’ for Carrot, Tomato, Mango, Egg & Banana. The corresponding Pivot table & Pivot chart will be displayed as follows.
- Till Now we are displaying the Grand Total for ‘Product’ & ‘Category’ in our Pivot table and Pivot Chart. Now let’s select all in the status for ‘Category’ in FILTERS and uncheck ‘Show Totals’ for ‘Category’ in ROWS and ‘Product’ in COLUMNS. Below is how our Pivot table and Pivot chart looks now.
- To open the chart editor you can double click on the Pivot chart or right click on the Pivot chart and click ‘Chart Style’
- The type of PIVOT Chart can be changed by Selecting the desired Chart Type in ‘Setup’ tab of Chart Editor.
Here we have selected Stacked Bar Chart.
- There are several other chart type available as Pie Chart, Scatter Chart, Line Chart, Area Chart and so on. In ‘Customize’ tab of the Chart Editor you can choose the desired style for your Pivot chart using several options available to customize each and every section of your chart.