Before creating Google Sheets Pivot table it is important to make sure your data is arranged in columns, each column has heading and data in each column is correctly formatted as per data type. As Pivot table does not change the formatting of data, so the Pivot table will not give correct results if data is incorrectly formatted.
In this article, we will learn how to create a Pivot Table in Google Sheets and convert our data set into a concise and meaningful report.
Let’s assume you are working a company that sells different brands of cigarettes in various regions. You have a data set of sales of cigarettes brands in different regions and you are required to see;
- How much each brand has contributed to the company’s revenue?
- How much each region has contributed to revenue?
- What is the sales performance of each brand region wise?
Before creating Pivot Table in Google Sheets you must ensure that text and number fields are correctly formatted in their respective data fields as shown below.
How to create a Pivot Table in Google Sheets
Open Google sheet that contains sales data and select whole data set for which you want to create Pivot Table. Go to menu, click on Data tab and select Pivot table… from the list of items.
When you click on Pivot Table… option Google Sheets create a new sheet named “Pivot Table 1”. In this Pivot Table sheet, left side is blank showing Rows, Columns, and Values area and on right “Pivot table editor” panel appears. Using this panel you can add the data fields to Rows, Columns, Values, and Filter areas to analyze and display your data results.
Now to get answers of your question listed above, you need to add the required data fields in appropriate areas of Rows, Columns, and Values. You can add the fields one by one by clicking on “ADD” button in each area.
- First, add Brand field in Row area,
- Then, add Region field in Column area
- Finally, add Sales field in Values area
You can see Google Sheets Pivot table summarizes the data by summing up the sales values of each brand for each region into a meaningful report by placing the “Brands” in Rows and “Regions” in Columns areas. It automatically generates “Grand Total” for each Region and each Brand.
From above Pivot table in Google Sheets, you can easily get answers to your listed questions;
- How much each brand has contributed to the company’s revenue? If you look at “Grand Total” figure row-wise, it summarizes the total sales of each brand in all the regions. So these figures show how much each brand has contributed to total revenue.
- How much each region has contributed to revenue? If you look at “Grand Total” figure column-wise, it summarizes the total sales of each region for all brands. So these figures show how much each region has contributed in total revenue.
- What is the sales performance of each brand region wise? If you want to see the sales performance of each band in a specific region, then you need to look at the figure in the intersection of row (Brand) and column (Region).
You can also switch Rows and Columns fields easily to have another view of the same data by keeping the Sales field intact in Values area. Just remove the field names from Rows and Columns area by clicking on “X” mark and again add the fields in Rows and Columns areas by moving the two fields interactively.
You can see Google Sheets Pivot table flexibly switches the two fields interactively and creates another glance of results.
Show as % of…
By default Pivot table in Google Sheets summarizes the number as SUM and shows as a number. Instead of showing sum of Sales figure, if you can also show the summarized data as % of the row, % of column and % of Grand Total. To do that you need to place the number field in Values area, click on the drop-down arrow of “Show as” region and select desired option.
For example, you want to show your data as “% of Grand Total” then you can easily do that by selecting this option from the list of items in “Show as” region.
When you select this option, Google Sheets Pivot table converts the sales figures as % of Grand Total of sales. It generates another view of the results and you are able to see at data from a different angle. Percentage figures in row-wise Grand Total show how much each brand has contributed in total revenue, while percentage figures in column-wise Grand Total show how much each region has contributed in total revenue. Depending upon your requirement, you can flexibly change look and feel of your data in Google Sheets Pivot table.
In Google Sheets Pivot table you can summarize your result by a number of ways. It has many options including SUM, COUNT, COUNTA, Average, Min, Max etc. Depending upon your data set you can easily choose the desired method to summarize your data. In this example, you can summarize your sales figure as Average.
For example, you want to summarize your data by calculating Average sale of each brand across all regions. To do that you need to add Brand field in Rows area and Sales field in Value area. By default Google Sheet Pivot table summarizes numbers as SUM, click on drop-down arrow of “Summarize by” region and select “Average”.
Pivot table in Google Sheets flexibly summarizes the data by calculating average sales of each brand across all the regions. You can disable column-wise Grand Total by unchecking “Show totals” in Rows area. In this way results can be viewed differently using same data set.
By default Pivot table in Google Sheets sorts the field’s data in ascending order, and by name of that field, but you can change data layout by selecting other available options. To do that, you need to select the appropriate option in “Order” and “Sort by” regions of Rows and Columns areas.
For example, you want to sort the resulting figures of sales in Ascending or Descending order by selecting “Sort by” option of “Average of Sales”, in Rows area you will select “Order” as descending and “Sort by” as “Average of Sales”. It will sort the whole data in descending order by brands’ average sales figure as shown below.
Google Sheets Pivot table editor panel also contains some suggested calculations based on the nature of selected data to further make it easy for you to quickly have a glance at your data. If you select any of these suggested calculations, Google Sheets Pivot table automatically adds the required data fields in appropriate pivot table editor areas and make suggested calculations for you, as shown below.
Suppose you want to see one of the suggested calculations “Sum of Sales of each Region”. When you click on this suggested calculation, Google Sheets Pivot table automatically adds the required data field in their respective areas and makes calculations for you.
In this article, we have learned how to create a Pivot table in Google Sheets and have tried to use as many features as available in Google Sheets Pivot table depending upon the nature of our data set. Google Sheets Pivot table has some limitations as compared to Excel Pivot Tables, like creating “Consolidated Pivot Table” from multiple data sheets, and creating “Calculated Items” within the Pivot table. These features are not currently available in Google Sheets Pivot table, but it has almost all the other features which are supported in Excel Pivot tables.