How To Use The Subtotal Function in Excel

The Subtotal function in Excel is a versatile tool that allows users to perform various calculations on data while excluding hidden or filtered rows. This guide will provide an in-depth look at the SUBTOTAL function, its syntax, and practical examples to help you master it.

What is the SUBTOTAL Function?

The Excel SUBTOTAL function is designed to return a calculated subtotal in a list or database. Unlike standard Excel functions, SUBTOTAL allows users to ignore hidden or filtered rows when performing calculations. It can execute various functions, such as SUM, AVERAGE, COUNT, and more, making it incredibly useful for dynamic reports and data analysis.

Why Use the SUBTOTAL Function?

Here are a few benefits of using the SUBTOTAL formula in your Excel workbooks:

SUBTOTAL Function Syntax

The SUBTOTAL function syntax is as follows:

=SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num: This is required and determines the type of calculation you want to perform (like SUM, AVERAGE, etc.).

• ref1: This is the first range of cells you want to calculate the subtotal for.

• ref2, ...: Optional additional ranges for which you want to apply the subtotal formula.

21 Function Numbers And Their Uses

Excel provides several function numbers to perform specific calculations. Here’s a handy table showing different function numbers and their corresponding actions:

These function numbers give you flexibility. For example, if you want to SUM visible rows only, you have to use 9. If you want to SUM all rows, including hidden ones, you have to use 109.

How to Use the SUBTOTAL Function

Follow these simple steps to start using the Excel SUBTOTAL formula:

Step 1: Open Your Excel Worksheet

• Launch Excel and open your worksheet containing data.

Step 2: Select an Empty Cell

• Click on an empty cell where you want your subtotal value to appear.

Step 3: Start Typing the Formula

• Type =SUBTOTAL( into the selected cell.

Step 4: Choose Your Function Number

• Decide which function you want to use by selecting a function number from the table above. For example, use 9 for summing visible cells.

Step 5: Specify the Range

• After entering the function number, add a comma and select your data range.

• Example: =SUBTOTAL(9, A2:A10) will sum all values in cells A2 through A10.

Step 6: Close Parenthesis and Press Enter

• Complete your formula by closing the parenthesis: =SUBTOTAL(9, A2:A10).

• Hit Enter to calculate your subtotal.

Step 7: Using Multiple Ranges (Optional)

• You can subtotal multiple ranges by separating them with commas.

• Example: =SUBTOTAL(9, A2:A10, B2:B10).

Subtotal Feature: Filtering Data with Ease

One of the best uses of the SUBTOTAL formula is when working with filtered data. Suppose you have a long subtotal list of products and you apply a filter to display only items above a certain price point. The SUBTOTAL formula will adjust automatically, calculating only the visible, filtered rows.

Aggregate Functions in Excel

While the Excel SUBTOTAL function is sometimes compared to aggregate functions, the key difference lies in its ability to ignore hidden rows. The Excel aggregate function can also summarize data, but SUBTOTAL is a more dynamic tool for working with filtered datasets.

Summary Functions in Excel

The SUBTOTAL function serves as a summary function that simplifies data analysis by allowing users to quickly calculate totals without affecting filtered data sets. This feature is particularly valuable when working with large datasets where visibility into specific entries is crucial.

Common Mistakes to Avoid

When using the SUBTOTAL formulas, be mindful of these common mistakes:

1. Incorrect Function Numbers: Ensure you choose correctly between including or excluding hidden rows.

2. Not Refreshing Formulas After Filtering: Always refresh your formulas after changing filters.

3. Ignoring Nested Subtotals: If there are other SUBTOTAL formulas within your specified ranges, they will be ignored to prevent double counting.

Practical Examples of Using SUBTOTAL

Here are some practical examples of how to use the Excel SUBTOTAL function effectively:

1. To calculate the sum of visible sales data:

   =SUBTOTAL(9, B2:B100) // Includes hidden rows =SUBTOTAL(109, B2:B100) // Excludes hidden rows

2. To find the average of visible sales data:

   =SUBTOTAL(1, C2:C100) // Includes hidden rows =SUBTOTAL(101, C2:C100) // Excludes hidden rows

3. To count visible entries:

   =SUBTOTAL(2, D2:D100) // Includes hidden rows =SUBTOTAL(102, D2:D100) // Excludes hidden rows

Conclusion

The Excel SUBTOTAL function is a powerful, dynamic tool that simplifies data analysis and summary functions. By mastering its syntax and understanding how to apply it in real-world scenarios, you can significantly improve your data management skills.

Whether you are calculating subtotal rows or working with complex filtered lists, SUBTOTAL is your go-to formula for efficient data handling. Give it a try today, and watch your Excel workflow become faster and more accurate!

Here are 5 suggested reads that will help you up your Excel game:

• How To Add A Row In Excel (And Delete It Too!)
• SUMIF Function In Excel: Syntax, Examples, Best Practices
• Transpose In Excel: Convert Rows To Columns & Vice A Versa With Ease
• How To Unhide Columns In Excel: Explore Basic & Advanced Methods
• How To Find And Remove Duplicates In Excel (Step-by-Step Guide)