Better Reporting: How to Use Google Sheets Pivot Tables, Column Stats, Explore, & QUERY Function to Wow Your Team

Published: 2021/9/15 | Updated: 2022/12/20 | David Benaim
How to use Pivot Tables in Google Sheets for better reporting

[get-meta-box style=”blue-box”]

As a manager, perhaps you’ve wondered how to take your financial reporting to the next level or how to amp up your data analysis skills. Maybe you’ve got an important meeting coming up, or you’d simply like to wow the executives on your next run-through. Whatever the reason, this tutorial is sure to help. We’re going to look at some of Google Sheets’ most useful features to transform your reporting. That includes Pivot Tables, Column Stats, Explore, and QUERY. Best of all? You’ll have the confidence to use them in your own work, starting today.

You can follow along with us by downloading a copy of this sample spreadsheet and following our step-by-step instructions.


This article is part of our tutorial series, where we often invite spreadsheet experts to share their expertise in thoughtful, actionable ways that managers can start using today. If you’re more comfortable in Microsoft Excel, don’t miss 5 hacks with Excel Power Query, or how to build an automated time tracking report with Power Query.

Google Sheets’ Pivot Tables – The main feature for analyzing data 

Often juggling multiple projects at any one time, managers certainly feel the pressure when it comes to financial reporting. You’re probably used to writing and rewriting formulas, but what if there was a better and more convenient way to present your data? In this section, we’ll show you how to create a Pivot Table in Google Sheets in three quick steps. We’ll also share some tricks to analyze your data with ease.

Before we dive in, let’s cover some essentials. 

Creating a Pivot Table in Google Sheets

Now that you know the benefits of Pivot Tables, let’s go through the three steps to creating your own. Remember, you can follow along with the sample spreadsheet, noting each of the tabs as we go.  

Step 1. Create a blank Pivot Table

To select your source data, highlight the cells you want to include, or click anywhere inside your chosen tab to capture everything. You can find this under the ‘Source Data’ tab on the sample spreadsheet. For this example, select cells A1 to G131, then click Data > Pivot Table, as shown in the screenshot below.

Under data, you'll find the option to create a pivot table in Google Sheets
After selecting cells A1 to G131, click Data > Pivot Table.

A dialogue box will appear and ask if you want this Pivot Table to be input into a new or existing worksheet. For the latter, be sure to choose which cell you would like to start from (for example, cell A1). For these examples though, we will input the Pivot Tables next to the source data in new tabs (for example, cell I1 as below).

A dialogue box for pivot tables in google sheets will ask if you'd like to insert data in a new sheet or an existing sheet
The dialogue box will ask if you’d like the Pivot Table to be inserted into a new or existing worksheet.

The ‘Pivot Table Editor Pane’ appears on the right-hand side, and the blank Pivot Table placeholder on the left. You can find this in the ‘Blank Pivot Table’ tab. Google Sheets automatically offers a few suggested Pivots to try, but for this tutorial, we’ll be creating examples from scratch.

Pivot table editor in Google Sheets offers pivot table suggestions
The ‘Pivot Table Editor Pane’ on the right-hand side automatically offers a few suggested Pivots to try.

Step 2. Add your data – part one

To include the data you want to analyze, simply click ‘Add’ and choose the fields you need. Let’s assume you’d like to calculate the total sales from each of the sales reps. 

For this, you would add ‘Sales Rep’ under ‘Rows’ as shown in the screenshot below. 

Click ‘Add’ and choose the fields to include the data you’d like to analyze. For our example, choose ‘Sales Rep’ under ‘Rows.’

Step 3. Add your data – part two

Now add ‘Total Sales’ under ‘Values’ as shown in the screenshot below.

Under values, we can also choose to add total sales while creating a pivot table in google sheets
Click ‘Add’ and then ‘Total Sales’ under ‘Values.’

The screenshot below shows you what your Pivot Table should look like (on the right), next to the relevant source data (on the left). You can also find this example in the ‘Pivot 1 – Sales by Rep’ tab.

Here you can see the source data on the left and the Google Sheets pivot table on the right
Here you can see the source data on the left and the Pivot Table on the right.

And there you have it. Three straightforward steps to creating your own Pivot Table in Google Sheets with no formula in sight!

More Ways to use Pivot Tables

Of course, there are many more features to explore, and Pivot Tables can vary in complexity. Did you know that with our add-on for Google sheets, you can combine your timesheet data with the power of Google Sheets and these very reporting features we’re diving into? Let’s explore some other ways to present our data through Pivot Table examples, using the sample spreadsheet as we go.

Multiple Fields

For a multi-layered report, you may want to add more fields and display more data in the one table. Let’s say you would like to compare the country against your original calculation. 

For this, you would use the same fields as ‘Pivot 1 – Sales by Rep’ and add ‘Country’ under ‘Columns,’ as shown in the screenshot below.

To display more data in one table, simply add more fields. Here, add ‘Country’ under ‘Columns.’

The screenshot below shows you what your Pivot Table should look like (on the right), next to the relevant source data (on the left). You can also find this example in the ‘Pivot 2 – Sales by Rep by Country’ tab.

Here is the source data on the left, and the Google Sheets pivot table with multiple fields on the right
Source data is on the left, and the Pivot Table is on the right.

Aggregation methods

Totaling numbers is most common, but sometimes it’s more interesting to review averages, counts, or percentages of our data. Perhaps you need to know the average sales in each location or the count of items by product for an inventory report, where the value is less important than a physical count. Sum, average, and count are different types of ‘aggregations.’

Using our example from ‘Pivot 2 – Sales by Rep by Country,’ let’s see the difference between summarizing by ‘SUM’ and summarizing by ‘AVERAGE.’ 

Under ‘Values’, click ‘SUM’ and choose ‘AVERAGE’ from the list, as shown in the screenshot below.

One aggregation method in google sheets' pivot tables is founder under VALUES > SUM > AVERAGE
Under ‘Values,’ click ‘SUM’ and choose ‘AVERAGE.’

The screenshot below shows you what your Pivot Table should now look like (on the right), next to the relevant source data (on the left). See this example in the ‘Pivot 3 – Average Sales’ tab and compare it to ‘Pivot 2 – Sales by Rep by Country’. 

Source data on the left with a pivot table calculating averages on the right
Source data is on the left, and the Pivot Table is shown on the right.

Another handy aggregation is ‘COUNTUNIQUE,’ which you might use for counting the unique values in a list of data. This is different from ‘COUNTA,’ which just counts the rows, including any duplicates). 

Note: Excel doesn’t have this feature, so you’ll only find it in Google Sheets. 

Using a new example, let’s say you’d like to know how many sales reps you have for each country. ‘COUNTA’ will display the number of times that sales rep is mentioned in the data. ‘COUNTUNIQUE’ will ignore the duplicates and give you the accurate number. 

To do this, under ‘Rows’ click ‘Country’, then under ‘Values’ click ‘Sales Rep’ and choose ‘COUNTUNIQUE’ from the list.

The COUNTUNIQUE function will ignore duplicates to give you an accurate in pivot tables in google sheets
Under ‘Rows’ click ‘Country’, then under ‘Values’ click ‘Sales Rep’ and choose ‘COUNTUNIQUE’ from the list.

The screenshot below shows you what your Pivot Table will look like (on the right) alongside how this compares to ‘COUNTA’, with the relevant source data (on the left). You can also find this example in the ‘Pivot 4 – COUNTUNIQUE vs COUNTA’ tab.

A pivot table in google sheets with COUNTA and COUNTUNIQUE
A look at source data on the left and the Pivot Table on the right.

Column Stats – Google Sheets’ latest feature and how it can help you

Managers don’t always have time to create Pivot Tables to identify key trends, statistics, and anything that may stand out as a financial risk. They’re also responsible for ensuring the data their team is collecting is accurate. As you’ll know all too well, one small but undetected mistake can have disastrous results. In this section, we’ll show you how to utilize this brand new feature to check your data in a matter of seconds.

Note: Excel doesn’t have this feature, so you’ll only find it in Google Sheets. 

Using Column Stats to grab an overview of your data

Let’s go through the two steps for using Column Stats to get a quick glance at your data without having to create a Pivot Table. For this feature, follow along with the sample spreadsheet using the ‘Data for Column Stats’ tab. Note that it includes some deliberate errors to better help you understand how it works. 

Step one. Choose your column

First, decide which column you wish to view. For this example, we will use the ‘Product’ column C. Click Data > Column Stats, as shown in the screenshot below.

Find Column stats in Google Sheets by clicking the column you'd like to work with, then Data > Column stats
After choosing which column to use, click Data > Column Stats.

Step two. Consider the results

The values for this column will now be displayed on the right-hand side ready for analysis, as shown below.

The results of column stats shows up on the right-hand side in Google Sheets
The column will now be displayed on the right-hand side.

Let’s say you’d like to know what types of products are currently being sold. This column breakdown allows you to see the list of products and if any incorrect data has made its way into your spreadsheet. In the example below, you can see ‘Mrror’ is an item that should not be included in this column.

It's easy to spot data errors with the column stats function in Google Sheets, like "Mrror" shown ehre
This column breakdown gives you a quick glance of all of the products and allows you to easily spot any incorrect data.

You can now toggle between the other columns to see their stats on the right-hand side, as shown below.

By toggling on different columns on the left, the Column stats feature on the right will show you the data for each one
You can toggle between other columns to see their stats on the right-hand side, too.

This breakdown of values for column E allows you to see the average and sum total of sales at a glance. All of this data and without any need for formulas or Pivot Tables!

Explore – Google Sheets’ hidden feature and how it can work for you

With a consistently busy work schedule, every manager is keen to know the latest ways to save time and improve their efficiency. While Column Stats and Pivot Tables help you create and upgrade your reporting, what happens when you need answers immediately and you’re not sure how to present them?

That’s where Google Sheets’ Explore feature comes in. Whether you’re struggling with preparing financials in an attractive audience-friendly format, or you need to identify the trends in the last quarter’s sales, this one-click wonder will be your new best friend. In this section, we’ll show you how to use the Explore feature to instantly gain insight from your data with the impressive power of AI.

So what’s so great about Explore? The Explore feature allows you to ask questions about your data as it automatically analyzes everything in your spreadsheet, making visualizing and presenting the information even more straightforward. It pre-suggests Charts and Pivot Tables for your data so you can save time from manually creating them yourself.

Using the Explore feature to ask those big questions

As the quickest of the three features we’ve discussed in this tutorial, Explore offers an effortless way to examine large datasets in just one click.

Let’s walk through the various ways Explore can help you find the answers you need to enhance your reporting further. For this feature, you can follow along with the sample spreadsheet using the ‘Source Data’ tab.

To launch the Explore feature, select the data you’d like to focus on (for example, A1 to G131) and click on the ‘Explore’ icon at the bottom-right of your screen, as shown in the screenshot below.

The Explore button can be found in the bottom right-hand corner of Google Sheets
Select the data you’d like to analyze, and click on ‘Explore’ at the bottom-right of your screen.

Explore will then display a list of suggestions on the right-hand side, including ‘Answers,’ ‘Formatting,’ ‘Pivot Tables’, and ‘Analysis,’ as shown in the screenshots below.

Explore offers options like Answers, Formatting, Pivot Tables, and Analysis
Explore offers a list of suggestions on he right-hand side, including ‘Answers,’ ‘Formatting,’ ‘Pivot Tables’, and ‘Analysis.’

To see more suggestions, click ‘MORE’, as shown below.

By clicking More in Explore, Google Sheets will offer even more options
Click ‘MORE’ to see more suggestions.

When you find something you want to use, click the ‘Insert’ icon, as shown below.

Once you've decided what you'd like to use with Google Explore, click the insert icon to use it.
Click the ‘Insert’ icon once you’ve fond something you’d like to use.

Using the search function under the ‘Answers’ section, type in the questions you have about the data. Let’s imagine you’d like to know the following;
1) “What is the sum of total sales for Ireland?”.
2) “Who are the top 3 sales reps by total sales?”
3) “What are the numbers of units sold by country?”

The screenshots below show how these questions would be displayed as answers, complete with suggested ways to present and analyze the data.

Three examples of questions and answers found through Google Sheets' Explore function
Three examples of questions displayed as answers using the ‘Explore’ feature.

QUERY – Google Sheets’ most advanced function and how you can master it

When you’re working with financials, it’s easy to get frustrated with the amount of data in one spreadsheet, especially when you only need to look at specific subsets. Imagine you have a worksheet with thousands of figures, but you would like to generate a report with just a couple of products to see their sales progress. Maybe you also need to include the sales person and the location but you need them in a different order from the source data. QUERY is the formula way to do this.

Note: Excel doesn’t have this feature, so you’ll only find it in Google Sheets. 

Using QUERY to transform your reporting 

Let’s go through the step by step process for using QUERY as a beginner. For this function, you can follow along with the sample spreadsheet, noting each of the tabs as we go.  

QUERY uses the following format; =QUERY(data, query, [headers]). Entering [headers] is optional as Google will usually guess which ones to use.

Step one. Enter your source data range

First, click in an empty cell in your worksheet and type ‘=QUERY’, then ‘(‘ and then highlight the data range you’d like to use (for example, A1 to F131), as shown in the screenshot below.

First, select the range of data you'd like to use in Google Sheets
Click in an empty cell and type ‘=QUERY’, followed by (, and then highlight the data range you’d like to use.

Step two. Enter your query in speech marks – SELECT

Let’s assume you’d only like to analyze the units of products sold by the sales reps. For this, you would enter the ‘query’ as “select B,D,E”, (referencing the column letter) as shown in the screenshot below.

Click columns B, D, and E to narrow down the data and only analyze sales rep, product, and units sold in Google Sheets.
Type “select B,D,E” to analyze the units of products sold by sales reps.

Now click ‘enter’ on your keyboard.

The screenshot below shows you what your QUERY should look like (on the right), next to the relevant source data (on the left). You can also find this example in the ‘QUERY 1 – SELECT’ tab.

An example of QUERY in Google Sheets on the right side
The source data is on the left, and the QUERY is on the right.

You can also retrieve any combination of columns in any order you like (even non-sequential) for example “SELECT D, F, C, A” would work just fine.

Step three. Enter your query in speech marks – SELECT and WHERE

Let’s say you’re only interested in the units of wheels sold by the sales reps. For this, you would enter the ‘query’ as “select B,D,E where D = ‘Wheel’”, as shown in the screenshot below.

Retrieve any combination of columns in any order you like while using QUERY in Google Sheets
Enter the ‘query’ as “select B,D,E where D = ‘Wheel’”

Now click ‘enter’ on your keyboard.

The screenshot below shows you what your QUERY should look like (on the right), next to the relevant source data (on the left). You can also find this example in the ‘QUERY 2 – SELECT + WHERE’ tab. 

What your QUERY data should look like (on the right) in Google Sheets, compared to the source data (on the left)
Source data is on the left, and the QUERY is on the right.

You can see that WHERE can refer to any column, not just the ones in your output table (e.g. WHERE C=”UK” would return just those rows).

Step four. Enter your query in speech marks – SELECT and WHERE and AND

Let’s say you’re only interested in the units of wheels sold by the sales reps when the units sold is greater than 5. For this, you would enter the ‘query’ as “select B,D,E where D = ‘Wheel’ and E>5”, as shown in the screenshot below.

Using QUERY in Google Sheets, there are many ways to analyze and look at data.
Enter the ‘query’ as “select B,D,E where D = ‘Wheel’ and E>5” to see the units of wheels sold by the sales reps when they sold more than 5.

Now click ‘enter’ on your keyboard.

The screenshot below shows you what your QUERY should look like (on the right), next to the relevant source data (on the left). You can also find this example in the ‘QUERY 3 – SELECT + WHERE + AND’ tab.

A Google Sheets screenshot showing source data on the left, and the QUERY results on the right.
Here’s a look at the source data on the left, and the QUERY on the right.

Although QUERY goes a lot further with more functions like ‘PIVOT’ and ‘ORDER BY’, the majority of the time, SELECT and WHERE provides the required breakdowns for financial analysis. Say goodbye to feelings of frustration and ‘copy and paste’ horror stories; With this nifty, robust feature, you’ll be a master of data manipulation in no time.

In this tutorial, we’ve discussed four of Google Sheets’ expert reporting and analysis features; Pivot Tables, Column Stats, Explore, & QUERY Function. Whether you’re a novice still learning the ropes or a veteran of all things spreadsheets, we hope these quick wins help you upgrade your financial reporting and get you that all-important ‘wow’ from the team. 

Is there a feature we’ve discussed in this tutorial that you’d like to learn more about? Let us know in the comment section below.

David is a Microsoft MVP for Excel, PowerPoint, and other Office apps, but he also appreciates when competitor apps (like Google Sheets or Zoom!) are stronger). Apart from his YouTube channel, David also regularly writes articles and gives webinars on these programs. David has trained more than 2,000 people in Excel, and he runs Xlconsulting, a Cambodia-based technology consulting firm.

Comments

Related posts

How to Calculate Project Profitability Using Time Tracking Data: Everything You Need to Know [Excel Tutorial]

Published: 2022/7/19 | Updated: 2022/7/19 | Andrew Childress

If you bill clients based on the time you spend, here’s a pop quiz: which projects make the most money? ...

Read more

How to Automate Employee Cell Phone Data Usage Analysis with Google Sheets (Free Template)

Published: 2019/12/3 | Updated: 2019/12/3 | Brian Wochele

There’s no arguing the importance of having a good handle on your employee cell phone data usage, nor the many ...

Read more

The Complete Guide to Annual Compensation Reviews Using Google Sheets

Published: 2020/2/19 | Updated: 2020/2/19 | Andrew Childress

Annual compensation reviews are a must. Reviewing and resetting employee salaries help you ensure that your employees are paid fairly. ...

Read more