Spreadsheets provide a great way to work with data. They help you keep your data organized so that you can get good insights from it. Google Sheets gives the added facility of Pivot tables that makes the process of data analysis even more powerful.
If you are new to Pivot tables, this tutorial will give you the jumpstart you need to delve deeper into your data and do some serious data processing magic.
What are Pivot Tables?
A pivot table takes a large set of data and helps summarize, and draw conclusions from it.
When you have a small spreadsheet with little data, it is easy to read, understand and make inferences from it. But as the data gets bigger, all you see are a lot of numbers. It then becomes difficult to draw conclusions from your data.
Let us see a use-case. Consider the sample data in the image below. It consists of details about sales made by employees in an imaginary chain of stationery stores. At first sight, all we see are lots of numbers, dates, and text. It’s quite difficult to make any sense from this data.
But by using pivot tables you can narrow down a large data set like this and visualize relationships between the data points quite easily. For example, you could use a pivot table to answer questions like:
- Which salesperson brought the most revenue for a specific month?
- Which region made the most sales?
- How many units of Pencils were sold?
- Which sales reps failed to reach their targets?
Now, of course, it is possible to analyze the data using formulas and functions, but Pivot tables can get your work done much quicker and easier, and with minimum human error.
Why do We Need Pivot Tables?
Here are some of the reasons more and more people are loving the power of Pivot Tables:
- As mentioned above, the Pivot table can help analyze data more efficiently
- They are quicker to put together and easier to explore than formulas and functions.
- They are flexible and versatile. Using Pivot tables, you can narrow down your data to just the important data points and add more to it as and when required.
- They even allow you to summarize data using aggregates, formulas, and functions if needed.
In the above use case, we can easily use Pivot tables to narrow down the data to show us just how much each sales rep sold in total.
How to Insert Pivot Tables in Google Sheets?
Here’s how you can insert and use Pivot tables in Google Sheets to analyze data in our use case. We obtained this sample data from the following page:
The data is free to use and download, so you can download it too, and convert it to Google Sheets in order to follow our tutorial.
- Select all the cells in the given spreadsheet. You can do this quickly by selecting the first cell on the top-right, then pressing the shift key while simultaneously clicking the last cell at the bottom right. Make sure you also select the column headers.
- From the Data menu, select Pivot Table.
- You will be asked if you want to insert your Pivot Table in a new sheet or into your existing sheet. It’s usually better to use a fresh sheet. So select the “New Sheet” option.
- Click on the Create button
You will see your Pivot table created in a new Spreadsheet named Pivot Table 1. You can go ahead and rename it to something else if you need to.
- On the right, you will see a sidebar, which is the “Pivot Table Editor”. Here you will find some Pivot tables that Google Sheets automatically suggests based on your selected data. You can choose to either accept the suggestions (if you feel they answer your needed questions aptly) or you can create your own Pivot tables with the data that you require.
Pivot Tables Suggested by Google Sheets
If you click on any of the suggestions provided, Google Sheets will automatically create your initial pivot table. Let’s take a few minutes to first explore some of these suggestions.
One of the suggestions is a Pivot table that shows the average units sold by each sales rep. If you click on this option, you will get a Pivot table that can help you easily find out which sales rep sold the most units on average, how each of them stacks against each other and who possibly deserves a promotion this year!
Another suggestion is the sum of the unit cost for each region. This will give you a table with each region and the corresponding total of unit costs. Through this, you can get a sort of idea about how each region has been doing (sales-wise).
Note that you can use the suggested Pivot table and add your own rows and columns to customize it.
For example, it makes more sense to see the number of units sold region-wise rather than the total of unit costs. So, you can delete the “Unit Cost” column by selecting the cross corresponding to the “Unit Cost” option under “Values”.
This will delete the SUM of Unit Costs column from the Pivot Table.
You can then click on the Add button next to “Values” and select “Units” from the dropdown.
Creating your Own Custom Pivot Table
If the suggestions given by Google Sheets do not match your requirements, you can also manually build your own Pivot table.
The Pivot Table Editor (on the right sidebar) also gives you options to insert data to your Pivot table according to your requirements.
You will see 4 insert options here:
Let’s try to make a custom Pivot table to perform analysis like this:
How many sales did each sales rep make for different types of items sold in the year 2018?
To answer the above question, here’s what we need in each of the dimensions:
- We want the Pivot table to display the names of the sales reps in each row.
- We want to display the different items sold across each column.
- We want the value of the total sales in cells where each Sales Rep name meets the item name.
- We want filters to display only the values for the year 2018. Filters let you see parts of the data while keeping unnecessary parts of it obscured.
Now let’s start building our Pivot Table.
To display the names of sales reps in each row, click on the Add button next to “Rows” and select “Rep” from the dropdown. The Pivot table will pull the names of all the Sales reps from your original table.
Notice that the Pivot table takes the names of the sales reps, removes duplicates and displays them in alphabetical order in Column A.
Next, we want to display different items sold across each column. For this, click on the Add button next to “Columns” and select “Item” from the dropdown. The Pivot table will display the names of different items along each column, without any repetitions.
So now your table is starting to take shape. You have the sales reps along the rows and item types along the columns.
To populate the individual cells of the Pivot tables, we need the values of total sales made by each sales rep for each item.
Click on the Add button next to “Values” and select “Total” from the dropdown.
You will now see the total sales made for each type of item for a given sales rep.
If instead of total sales you want to see the average sales, click on the dropdown below “Summarize By” under “Total” and select “Average” from the dropdown.
The totals in each cell should now be replaced by the average sales made for each type of item for a given sales rep.
You will also find the “Grand Total”, calculated automatically for each item at the bottom of the Pivot table. The Grand Total for each sales rep is displayed on the rightmost column of the table.
Our Pivot table has already started giving a lot of useful insights. The only thing left to do is ensure that we get the average sales for just the year 2018. For this we use filters.
- To add a filter, click on the Add button next to “Filters” and select “OrderDate” from the dropdown.
- Under OrderDate, click on the dropdown below “Status” .
- Click on “Filter by Condition”
- In our original table, there are only dates for the year 2018 and 2019. Since we want values for just 2018,
- Select “Date is before”.
- Under this, you will see another dropdown next to the word “today”. Click on this and select “exact date”.
- Type the date 01/01/2019 in the input box.
This will ensure that the averages only consider all the dates before 1st Jan 2019 (Which is just all the dates of the year 2018!)
- Click OK to update the Pivot table.
We now have a Pivot table that answers our question: How much sales did each sales rep make for different types of items sold in the year 2018?
Pivot tables make things so much easier when we want to sift through large amounts of data or want to see the bigger picture.
With all of the information we want right in front of us, we can now answer almost any questions we have about our data.
So, go ahead, give it a try, unlock the power of Pivot Tables, and make unlimited discoveries about your data.