Excel SUBTOTAL Formula Explained

Click OK to let Excel automatically create groups, add subtotals, and insert collapsible outlines. The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database. To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis. As we can see in the screenshot above, the subtotals are inserted as new rows below each Group. When we create subtotals, our worksheet is divided into different levels.

Tips for How to Calculate Subtotal in Excel

The SUBTOTAL Function1in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided. The SUBTOTAL function offers 22 different operations through its function_numargument. This table breaks down the essential components of the SUBTOTALfunction. It shows the function name, basic syntax format, argument details,and return value characteristics. Imagine having a wizard at your fingertips to effortlessly crunch numbers, weaving in and out of data with precision and speed.

This powerful tool quickly calculates totals, averages, and other statistics for subsets of data based on the categories you choose. In this blog post, we will explore how to use Subtotal in Excel so that you can analyze and present your data accurately and efficiently. If you’ve ever worked with hidden rows you’ve probably wanted to find the total of a column excluding what’s hidden.

To remove subtotals, go to the “Data” tab, click on the “Subtotal” button, and then click on the “Remove All” button in the dialog box that appears. Yes, you can subtotal multiple columns at once by selecting the “Add subtotal to” option for each column in the Subtotal dialog box. Make sure to include only the cells you want to sum up. If your data is organized in columns, you can select by clicking and dragging from the first cell to the last. Another way to use the SUBTOTAL function in Excel is by using the Subtotal feature.

• Imagine having a wizard at your fingertips to effortlessly crunch numbers, weaving in and out of data with precision and speed.
• Using this first formula, we can obtain our sum using the number 9 for the first argument to include the hidden data.
• Alright, let’s dive into how to calculate subtotals in Excel.
• Moreover, you no longer need to apply a filter to make the subtotal value obvious.
• When you select the input dataset and click on the From Table/Range command, the Create Table dialog will show up.
• For the sample dataset, you can add the Gadget column into the Rows field to show how different products contribute to the revenue for various product segments.

As an example, you can use a dataset that resembles the above example. Checkmark the checkbox for the column header by which you want to segregate what is subtotal the subtotals. Since we need a subtotal SUM by sales value, select the Revenue column header.

Depending on the information you wish to display in the worksheet, you can switch between these levels. The table demonstrates using SUBTOTAL within a larger calculation. The formulacalculates what percentage the first row is of the filtered total. This table demonstrates SUBTOTAL’s ability to combine different ranges in onecalculation.

We’ll use the Subtotal and Group features to display an average grade for each student. If you enjoyed learning about the SUBTOTAL function, you’d also want to know more about the functions it offers. Enroll in my 30-minute free email course here to get started. Here we are using the complete syntax of the SUBTOTAL function.

• But today, we are going to introduce you to such a versatile function of Excel that will knock your socks off.
• In the dialog box, choose the column you want to subtotal, the function (like SUM), and the column to add the subtotal to.
• Both calculate the sum, but with a slight twist in visibility.
• Let’s use the SUBTOTAL function in the above dataset to get the sum of revenue for different product categories.
• SUBTOTAL 9 opens up to include both visible and manually hidden rows in its calculation.

It’s packed with tools that make managing and analyzing data a lot easier. We now have our rows grouped by student with an average grade for each and an overall average at the bottom. Again, you can use the buttons on the left to collapse and expand the groups. Select the rows, go to Data, and pick “Subtotal” in the Outline drop-down menu.

Excel Data Visualization

When you select the input dataset and click on the From Table/Range command, the Create Table dialog will show up. You’ll find various options, like From Database, From Azure, From Online Services, etc. Hover the mouse cursor on the suitable option to find more choices. For example, the From Database option allows you to import datasets from SQL server databases, from Oracle databases, and more.

How to Extract Numbers from a String in Excel

Congratulations, you now know how to use Subtotal in Excel! This powerful tool is extremely useful for analyzing large sets of data, and can save you a lot of time and effort in the process. Whether you are working with sales data, financial information, or any other type of data, Subtotal can help you gain valuable insights and make informed decisions.