How to Create Pivot Tables in Google Sheets

Welcome to the world of Pivot Tables!

Pivot Tables are an efficient and powerful way to analyse large amounts of data and make meaningful insights.

With Pivot Tables, you can quickly summarise and arrange your data in a way that is easy to read and understand, even without touching a single formula or function.

You can also filter, sort, and group your data to create charts and graphs that can help you make quick decisions. With Google Spreadsheets Pivot Tables, you can take your data analysis to the next level!

You might be thinking, if pivot tables are so good, why do I even need to learn all the other stuff? The good news is pivot tables make light work of big datasets, so they’re great for getting quick insights with minimal effort.

The bad news is that pivot tables have limitations, and we’ll touch on these later. But, as much as it pains me to say it, pivot tables remind me of the old Nokia 3210 from the 1990s!

This little gem of a phone was great; I’m sure anyone who owned one would agree. You could get the job done just fine, whether it was making a phone call, sending a text message or playing a cheeky game of snake!

Without a doubt, the Nokia 3210 is a relic that demands a special place in our hearts, but when you compare this to an iPhone, Samsung Galaxy or Google Pixel, well, you get the point; they're a world apart!

If you want to do anything useful with spreadsheets, you will need to master formulas, but there is always room for a Nokia 3210 in our lives, even if it's only for a game of Snake!

Pivot tables have come a long way over the years. They used to be very clunky and restrictive, but they are actually enjoyable - if that’s the right word - because they are so easy to use.

Let’s take a look at the syntax.

Syntax

The syntax for a pivot table in Google Sheets does not follow your regular formula syntax, but it’s helpful to know what’s considered.

1. Data: This is the source data you want to use in the pivot table. AKA, the dataset.

2. Rows: This field will group the data by rows.

3. Columns: This field will group the data by columns.

4. Values: This field will aggregate the data within the pivot table.

5. Filters: This field will filter the data within the pivot table.

6. Sort: This field will be used to sort the data within the pivot table.

That’s all well and good, but it’s best to see it in action.

Let’s take this table below. The actual table has 30 rows of data, but honestly, with pivot tables, you can have hundreds or even thousands of rows, and this thing won’t break out a sweat.

Looking at this, you might wonder where to start. The good thing is that Google Sheets makes things as easy as possible. Just place your cursor anywhere in the dataset; mine is just in cell A1 right now.

Click on the Insert menu and choose Pivot Table.

This little window below will pop up to confirm the data range, which in my case, is:

Sheet1!A1:J30

You can then choose if you want to see the pivot table on a new sheet (selected by default) or click ‘Existing sheet’ to choose where you want to place it.

We’ll stick with adding this to a new sheet, as I find this is usually the best option for pivot tables. A new sheet will be created just for the Pivot Table.

You might have a dedicated sheet with multiple pivot tables for analysing results at a glance, in which case choosing the existing sheet would be the best bet.

Either way, once you click the create button, you’ll be taken to this new sheet with a blank canvas to build the pivot table like this.

In the sidebar, you’ll see the pivot table editor on the right-hand side of the screen. If you close this by accident, hover your mouse over the table until you see the Edit button; clicking this will open the Pivot Table Editor.

If you’ve never used pivot tables before, you might feel intimidated by the very thought of them. People often hold pivot tables and the notable VLOOKUP function in very high regard, so they tend to have an air of enigma about them, but they are really not that bad; in fact, this is where most people’s journey begins in the Spreadsheet world.

Think of these two as the gateway drug to hardcore functions and formulas.

Once you experience the power, you’ll want more, so this is a great place to begin.

Anyway, back on track. Looking at the pivot table editor, in the left column, you’ll find the areas mentioned in the syntax earlier and on the right is a list of all your columns from the original dataset.

You can play around with the Suggested options in the Pivot Table Editor; I just chose the Average of Gross Consultancy for each Country (The third suggestion), and immediately my table was transformed with the data.

In the pivot table editor, these options were chosen automatically when I chose the third suggestion to give you a feel for how this works.

To clear these out, click on the grey cross to reset everything. You can, of course, use these suggestions if they work for you, but for the most part, you’ll want to build your pivot tables to view the metrics you’re interested in.

There are two ways to build a pivot table with your metrics; these are your column headings from the original dataset. First, you can click and drag the metric from the right-hand side of the pivot table editor and drop it into either rows, columns, values or filters; see below.

Alternatively, as shown below, you can simply click the ‘Add’ button to choose a metric from the drop-down list that appears.

Deciding what to put where depends on what you’re looking to achieve, but generally speaking, you will want text-based columns positioned in the rows and columns. So, for example, you may wish to show all instances of the United Kingdom from the country column grouped together as rows and a particular type of product grouped together as columns so you can visualise what products are sold to which countries.

Finally, you can aggregate your data by adding value-based columns to the value metrics, including prices, profits, the number of units sold, and so on.

Setting up the metrics like this would build a pivot table that looks like this below.

You can see that the countries are listed as rows on the left of the table, and products are listed along the top as columns. This is because all the aggregated value-related data, which in this case is the sales price, sit in the table's main body.

It makes the process of identifying and understanding your data quick and easy.

For example, from the table above, we can see that the best-selling product is the Carretera product, which is most prevalent in Germany with sales of £276.48.

One of the great things about Pivot Tables is the flexibility to change your metrics with just a few clicks.

For example, let’s assume we want to keep the rows and columns the same, but instead of viewing the Sales Price as Values, we want to see the profit. But,

again, this is just two clicks to switch over.

All we need to do is click the cross on the Sales Price for Values and then drop in the Profit instead.

Now our table values reflect the profit for each product split by country. This is the one thing I find most useful with Pivot Tables, as it allows you to quickly visualise the data and decide if this is something you can utilise or not. If it's not, you can switch things around with just a few clicks.

Order and Sort by

You can order the results in your table ascending or descending for rows and columns. Since my metric is the Country and this is set to ascending order, the countries in the table will be alphabetically ordered, as you can see in the above table. If we were to change this to descending, we would see the United States of America at the top and Canada at the bottom.

The Sort by option is sorting the data, in my case, by the Country, but we could change this to 'SUM of Profit in…' instead.

This will allow you to choose one of your column headings to sort by.

Let’s choose the Grand Total and also switch the order to Descending.

Now you can see the greatest profit sits at the top in the Grand Total column. The Countries are no longer sorted alphabetically since we’re sorting by profit in Grand Total.

You can do the same for columns if you wish to bring them into ascending order, for example. The products are now sorted with the least profit coming from the product ‘Paseo’ on the left of the table and the greatest coming from ‘Carretera’ on the right.

The Order and Sort by are great little tools to help to visualise your data better.

When it comes to the Values, you have a few more options at your disposal. Right now, this is set to SUM, but you can change this to COUNT, COUNTA, AVERAGE, and MAX, to name a few. You can also show the results a default, % of the row, % of the column or % of the total.

The best thing to do is play around with these to see what works for you. Remember, you can always change it; you can’t break anything.

Filters

You’ll also notice there is an option to filter the results. Again, this is straightforward; you can drag in one of your metrics, such as Country and then include only those you’re interested in.

You can add as many filters as you need to drill down the results in your table to see only what you want to see.

Dataset

I need to address the elephant in the room with Pivot Tables, as it’s often overlooked. If you recall, when we first started, we defined the range of data by selecting all of the data in our original dataset, which was:

Sheet1!A1:J30

If your data changes, in other words, if you add new columns or rows, the Pivot Table won’t automatically update to include that data.

This is the most frustrating and fundamental flaw of pivot tables. Google, you need to address this drawback!

With that said, Pivot Tables are great if you want to analyse a dataset that won’t change, but if this dataset is dynamic, then forget Pivot Tables, as you’ll have to update the range to account for the new data constantly.

Alternative Solutions

You would be hard-pressed to find an all-in-one solution to replace Pivot Tables. Still, it is entirely possible to achieve the same thing using functions and formulas to get the same results and, believe it or not, more flexibility.

I use Pivot Tables to check the data and find the information I want to extract from an extensive dataset. Then, once I know what I want to summarise from the data or how I want it laid out, I typically build the same thing using a combination of other functions and formulas.

Doing this allows you to ensure your summary table is dynamic so that it changes automatically as new data is added to the main dataset, whether that be new columns or usually more rows.

For example, you can see below that the top table was created with a Pivot Table, but the table beneath this was created using a combination of the SUMIFS function and the SUM function, plus a few others.

=SUMIFS(Sheet1!$J$2:$J$30,Sheet1!$D$2:$D$30,$B13,Sheet1!$E$2:$E$30,C$12)

Although the countries and products are in a different order to the Pivot Table, all the values in the table are the same. Admittedly, it took a bit more effort to achieve this, but it would allow for flexibility when used with open-ended ranges like this:

=SUMIFS(Sheet1!$J$2:$J,Sheet1!$D$2:$D,$B13,Sheet1!$E$2:$E,C$12)

Without a doubt, though, my favourite would have to be the QUERY function in Google Sheets.

You can see I’ve recreated this table a third time using the QUERY function, which is a single function for the whole table.

={QUERY(Sheet1!A1:J30,"SELECT D, SUM(J) WHERE D is not null GROUP by D PIVOT E"),QUERY(Sheet1!A1:J30,"SELECT SUM(J) GROUP by D LABEL SUM(J) 'Grand Total'");TRANSPOSE(QUERY(Sheet1!A1:J30,"SELECT SUM(J) GROUP by E LABEL SUM(J) 'Total'")),QUERY(Sheet1!A1:J30,"SELECT SUM(J) LABEL SUM(J) ''")}

The QUERY function might look complicated, but believe me, once you’ve mastered it, dare I say it, you may never use a Pivot Table again : 0

As is often the case, you can generally achieve the same thing in Google Sheets with multiple approaches.

First, however, you need to remember that you have to start somewhere, and Pivot Tables is definitely an excellent place to begin.

Try this out for yourself using this Pivot Tables Template as your starting point.