SUBTOTAL is a function in Google Sheets that can revolutionize your spreadsheet game. With its versatility and power, you can take your sheets’ functionality to new heights. In this guide, we’ll explore the various applications of the Google Sheets SUBTOTAL function and how it can make your life easier.
The 3 Primary Ways to Use the SUBTOTAL Function
The SUBTOTAL function offers three main ways to enhance your data analysis and reporting:
- Applying essential functions like Sum, Count, and Average to a list of data.
- Creating a reporting selector to view different stats from a single set of data.
- Calculating data with or without hidden rows.
These options may initially seem overwhelming, but fear not! There are ways to leverage SUBTOTAL without memorizing complex function codes. If you’re new to Google Sheets or feeling daunted, consider taking a Google Sheets course to enhance your skills.
Now, let’s dive into how to use subtotals in Google Sheets with practical examples. By the end of this tutorial, you’ll master subtotals and understand their versatility and simplicity.
Download a Copy of Our Example Spreadsheet
To make things even easier, you can download a copy of our Example Sheet and follow along with this guide. If you find the example sheet useful, you may also want to check out our paid templates. Use the code SSP to save 50% at our Gumroad store.
Google Sheets SUBTOTAL Function: Syntax
Before we get started, let’s familiarize ourselves with the syntax of the SUBTOTAL function in Google Sheets.
The SUBTOTAL function requires two or more arguments:
- Function Code.
- At least one range to perform the function on.
Function codes are readily available in Google Sheets. To access them, start typing “=SUBTOTAL.” In the formula help section at the bottom left, click on the “Learn More” button. If the formula help section isn’t visible, look for the blue question mark box or press F1 to open it.
Once you click on “Learn More,” a right-hand sidebar will appear, providing a detailed explanation of each function code. This handy reference will come in handy if you ever forget.
Google Sheets offers 11 built-in functions within the SUBTOTAL function, such as AVERAGE, COUNT, SUM, and more. Additionally, you can modify the function code to tell it to ignore hidden cells by adding 100s to the original codes. For example, 101 represents AVERAGE while ignoring hidden cells.
Now that we have the basics covered, let’s explore how to unleash the power of the SUBTOTAL function in Google Sheets.
How to SUBTOTAL in Google Sheets
You might be wondering why you should use the SUBTOTAL function instead of just using the SUM function. While it’s a valid question, there are situations where SUBTOTAL can be more valuable and effective. One such situation is when you need to add subtotals in Google Sheets.
Let’s say you have the following dataset, and you decide to use the SUM function instead of SUBTOTAL:
In the resulting chart, you’ll notice that the SUM function is used in each total cell and the grand total cell at the bottom of the table.
If you manually add up all the totals, you’ll find that the Sum of the Average Projected Sales and Actual Sales doesn’t match the listed totals. However, each Quarter Total is correct. The SUM function adds all the numbers in the range, including the Quarterly totals, resulting in incorrect calculations.
To avoid this issue, we need to replace all the SUM functions with SUBTOTAL functions. Unlike SUM, SUBTOTAL doesn’t include other subtotal functions, giving us the correct totals. Let’s see how to create subtotals in Google Sheets:
Using SUBTOTAL instead of SUM ensures that the correct totals are displayed in each row. This method eliminates the problem of incorrect calculations caused by including Quarterly totals in the range.
Google Sheets SUBTOTAL Function for Filtered or Hidden Data
Suppose you have a large categorized dataset and want to filter it by month. You also want the total cells to adjust automatically based on the filter. For example, when viewing January’s data, the total cells should reflect that.
Unlike the SUM function, which remains unchanged when data is filtered or hidden, the SUBTOTAL function adapts accordingly.
Consider the example below:
Even when some months are hidden, the total at the bottom of the sheet remains accurate.
To achieve this, follow these steps:
- Step 1: Select the green filter drop-down at the top of the Month column.
- Step 2: Choose the desired month and click OK.
You’ll notice that the data changes, with many rows being filtered. The SUM cells in the table remain unchanged, while the SUBTOTAL cells adjust to show only the unfiltered cells.
Furthermore, if you want to exclude outliers or specific data rows, you can hide those rows. The SUBTOTAL function adjusts accordingly, even when rows are hidden.
How to Use the SUBTOTAL Function to Create a Dynamic Report Function Selector
The most versatile application of the SUBTOTAL function is creating a dynamic report function selector. This feature allows you to quickly change the metrics displayed in the report by combining SUBTOTAL with various functions and operations.
In our example sheet, we can create a dynamic report that displays average, sum, max, and min for the Actual sale column.
The process is relatively simple. Follow these steps:
- Step 1: Create a function table listing available SUBTOTAL functions.
- Step 2: Create a data validation drop-down menu that refers to the function table.
- Step 3: Use the selected function in the SUBTOTAL formula to generate the desired result.
Now, let’s break down the steps:
- Step 1: Create a list for the drop-down menu by right-clicking the cell.
- Step 2: Go to Data > Data Validation.
- Step 3: Click Add rule.
- Step 4: Choose Dropdown (from a range) under criteria.
- Step 5: Select the aggregation column range from the list created in Step 1 and click OK.
Once you’ve created the data validation drop-down, you’ll see an arrow on the cell, allowing you to choose from the available options.
The remaining steps involve writing the functions to get the desired results.
- Step 6: Nest a VLOOKUP function within the SUBTOTAL function to generate the desired result. Use the following formula:
Here’s what this formula does:
- The VLOOKUP looks at the table created in Step 1 and searches for the contents in the drop-down cell (G2).
- Once it finds the correct row, it fetches the result from the second column of the range.
- The fetched number is then plugged into the SUBTOTAL function as the function code.
If you want to account for hidden cells as well, use the following formula:
Your selector is now functional and ready to use. However, if you want to add more advanced features, you can create a checkbox to control the inclusion of hidden cells. This allows users to toggle between showing or ignoring hidden cells.
To achieve this, enter the following formula in the results cell:
Frequently Asked Questions
Why Use SUBTOTAL Instead of SUM?
While SUM is a commonly used function, SUBTOTAL offers additional benefits. Unlike SUM, SUBTOTAL can be used repeatedly in the same column to add subtotals by category without affecting the overall calculation. Moreover, SUBTOTAL adjusts calculations when data is filtered, excluding hidden values.
Is SUBTOTAL the Same as Total?
A subtotal represents the total of a specific set of data in a range, while total refers to the sum of all the data in the range. The SUBTOTAL function in Google Sheets accurately calculates both subsets and the entire dataset simultaneously.
Google Sheets is equipped with powerful functions, and SUBTOTAL is no exception. By mastering the SUBTOTAL function, you can enhance your reporting system and make data analysis a breeze. This guide has provided a comprehensive understanding of how to use SUBTOTAL in Google Sheets.
For more helpful guides and tips, check out our other articles and resources:
- How to Multiply in Google Sheets (Numbers, Cells or Columns)
- Easy Guide: How to Subtotal in Google Sheets
- How to Divide in Google Sheets (Numbers, Cells, or Columns)
- How to Merge Cells In Google Sheets
- How to Apply a Formula to an Entire Column in Google Sheets
- Slow Google Sheets? Easy Ways to Speed Up
- How to Compare Two Columns in Google Sheets
- How to Use SUMIF function in Google Sheets? Examples!
Remember, mastering the SUBTOTAL function opens up a world of possibilities in Google Sheets. Embrace its power and transform your spreadsheets into dynamic tools of excellence.