Guidelines & Tips from IT: Pivot Table in Google Sheets (Part-II)

Creating a Custom Pivot Table

The suggestions provided by Google Sheets might not suit your needs, so you also have the option to build your own pivot table manually.

Let us try to build a custom pivot table to analyze the following:

“How many units of each item type were sold on the different sales channels before the year 2014?”

Let’s break this question down to understand exactly what we want our pivot table to show. We want:

The names of different Item types sold in the form of rows
The different sales channels in the form of columns
The value of total units sold in cells where each Item Type meets a Sales Channel
Filters to display only the data relating to the years before 2014. A filter lets you see parts of the data, while keeping irrelevant parts hidden.

Now that we know what we want, let’s start building our pivot table.

Inserting Rows

To display the list of Item Types in each row, click on the ‘Add’ button next to ‘Rows’ in your Pivot table editor. Select ‘Item Type’ from the dropdown.

The pivot table will obtain the names of all the Item Types from your data table and display them as shown below:

Note that the pivot table has displayed the list of items in alphabetical order and has removed any duplicates.

Inserting Columns

To display the list of Sales channels across each column, click on the ‘Add’ button next to ‘Columns’ in your Pivot table editor. Select ‘Sales Channel’ from the dropdown.

Select ‘Sales Channel’ from the dropdown.

The pivot table will obtain the names of the different Sales channels (‘Online’ and ‘Offline’) from your data table and display them as shown below:

Our spreadsheet pivot table is already starting to take shape.

Inserting Values

Now it is time to populate the pivot table’s cells with the number of Units sold for each Item type through each Sales channel. For this, click on the ‘Add’ button next to ‘Values’ in your Pivot table editor. Select ‘Units Sold’ from the dropdown.

You should now see the sum of units sold for each item on each sales channel.

Note that the Units Sold have been summed up and displayed in each cell. So for Baby Food, the pivot table summed up all the units sold Online and displayed them in cell C3.

If you want to see the average number of units sold instead of total, simply click on the dropdown below ‘Summarize By’ (under Units Sold) and select ‘Average’.

‘Summarize By’ (under Units Sold)

Note that the pivot table also displays the Grand total of Units sold for each channel (which it automatically calculates) at the bottom of the table. The Grand Total per item is also displayed on the right-most column of the table.

Adding Filters

We are already getting a lot of insights into our data from our created pivot table in Google Sheets. The last thing to do now is to narrow down our results so we see only the total number of units sold before sthe year 2014. For this we can use filters.

To add a filter to your pivot table, follow the steps outlined below:

Click on the ‘Add’ button next to ‘Filters’.
Select ‘Order Date’ from the dropdown.
Click on the dropdown under ‘Status’.
Click on the ‘Filter by Condition’ category.
From the dropdown under ‘Filter by Condition’, select ‘Date is before’.
You should see a new dropdown under this now. Make sure that ‘exact date’ is selected.
In the input box that says ‘Value or Formula’, type the date ‘1/1/2014’. This ensures that the pivot table only considers the dates before 1st Jan 2014.
Click OK.

That’s all! We now have a pivot table that displays the sum of units sold for each Item type on each Sales Channel before the year 2014.