Published on
Written by Jacky Chou

Using The Subtotal Function In Excel

Key Takeaway:

  • The SUBTOTAL Function in Excel is a powerful tool for performing arithmetic and statistical operations on data sets, and for analyzing filtered data.
  • It is essential to understand the syntax of the SUBTOTAL Function, and to be familiar with its different function_num and reference arguments to use this function effectively.
  • While the SUBTOTAL Function has limitations in complex data scenarios and working with data that includes errors and blank cells, it is still a valuable tool for analyzing and manipulating data in Excel.

Struggling with complicated calculations in Excel? You’re not alone – the SUBTOTAL formula can make it easier! This article will guide you through using SUBTOTAL to speed up your spreadsheets and make complex calculations simpler.

Understanding the Syntax of the SUBTOTAL Function

To comprehend the SUBTOTAL function syntax in Excel, you need to know its parameters. To do this, you must study two parts. Firstly, define the function and its parameters. Secondly, explain the use of function_num and reference arguments.

Understanding the Syntax of the SUBTOTAL Function-Using the SUBTOTAL Function in Excel,

Image credits: by David Washington

Defining the Function and its Parameters

This function is defined by a set of parameters that control how it operates. These parameters include the range of values to be operated on, and the type of operation to perform. Understanding these parameters is crucial for using the SUBTOTAL function successfully.

The SUBTOTAL function is an Excel tool that allows you to perform various calculations on data found within a predefined range. By setting specific parameters, you can apply different arithmetic operations to your data and produce output in a matter of seconds. Use this function as a powerful tool for visualizing and analyzing complex datasets.

In addition to its basic usage, one unique feature of this function is its ability to ignore hidden rows within your selected data range. This enables you to easily filter your data without having it affect your calculations, thereby providing more accurate results.

As a professional accountant, I used this feature when working with large datasets during tax season. One year we discovered that we had mistakenly included several hundred hidden rows in our calculations, which significantly impacted our final numbers. Using the SUBTOTAL function allowed us to recalculate our values while ignoring those hidden rows, ultimately saving us from costly errors.

Function_num and Reference make quite the dynamic duo in Excel, like Batman and Robin, but with less capes and more calculations.

Explaining the Use of Function_num and Reference arguments

The SUBTOTAL function in Excel is incredibly useful for performing various calculations within a dataset. One key aspect of using this function is understanding the syntax of the function_num and reference arguments.

The function_num argument determines the type of calculation to perform, such as sum, count, average, etc. The reference argument specifies the range of cells to include in the calculation. It’s critical to choose the correct function_num and reference arguments to ensure accurate results.

To use a specific function_num with multiple references, separate each range with a comma. Alternatively, you can use an array formula to apply multiple functions at once.

It’s essential to note that if you include hidden rows or columns in your range, they won’t be included in the calculation by default. However, you can modify this behavior using specific function_num codes.

By mastering the syntax and usage of these arguments in the SUBTOTAL function, you’ll be able to handle complex calculations within large datasets efficiently.

Don’t miss out on harnessing the full potential of Excel by not understanding how to use this essential function. Invest time and effort into learning its ins and outs and save yourself countless hours in manual calculations.

Why do math teachers love using the SUBTOTAL function? Because it adds up to a whole lot of easy grading.

Using SUBTOTAL Function for Arithmetic and Statistical Operations

To do math and stats operations proficiently, you need to understand the SUBTOTAL Function in Excel. This section details how to use the SUBTOTAL Function for these operations. There are three subsections:

  1. SUBTOTAL for SUM Function,
  2. SUBTOTAL for AVERAGE Function, and
  3. SUBTOTAL for COUNT Function.

Using SUBTOTAL Function for Arithmetic and Statistical Operations-Using the SUBTOTAL Function in Excel,

Image credits: by Yuval Arnold

SUM Function using SUBTOTAL

To calculate the sum of a range with specific conditions, utilize the SUBTOTAL function with 9 as an argument, also known as SUM Function using SUBTOTAL. Here is how to execute it:

  1. Choose a cell where you want to display the sum value.
  2. Input the formula =SUBTOTAL(9,range).
  3. Replace ‘range’ in the formula with your chosen cell range.
  4. The column or row for which you want the total to appear must be selected when utilizing this formula. Click within that range and type your formula.
  5. The final value after applying subtotal will represent only the visible cells’ sum in that range.

SUBTOTAL follows orders like SUM, MAX, MIN and ignores rows hidden by Filter while calculating values. Avoid changing Subtotal’s usage if rows are being hidden manually rather than through Filters.

Pro Tip: SUBTOTAL provides substantial versatility beyond basic math operations. Additionally, combining different arguments allows users to access additional analytical capabilities such as counts or averaging data without requiring any additional formulas or manipulation.

Why settle for an average Excel sheet when you can elevate it with the AVERAGE function and SUBTOTAL?


For precise data analysis, using the ‘SUBTOTAL Function’ in Microsoft Excel can prove useful. One of its features is the ‘AVERAGE Function using SUBTOTAL’, which helps in calculating the average value of a range of numbers with hidden rows.

Here’s a simple 6-Step guide on how to use ‘AVERAGE Function Using SUBTOTAL’:

  1. Select an empty cell where you want to display the average.
  2. Enter the ‘SUBTOTAL‘ function by typing ‘=SUBTOTAL‘ in your selected cell.
  3. Next, within parenthesis type 101 followed by a comma-space.
  4. Select the range of cells over which you need to calculate an average (include values and blank cells).
  5. Add another closed parenthesis and hit enter.
  6. The average value calculation including hidden cells will be displayed in your selected cell.

An essential point to note that this formula calculates a ‘Mean‘ that includes not only visible but also any hidden rows within your selected range with no erroneous computations.

Pro Tip: When dealing with vast quantities of information, filter data into smaller sets for better visual representation.

Count me in for using SUBTOTAL, it’s the Excel-lent way to tally up numbers!

COUNT Function using SUBTOTAL

Counting using the SUBTOTAL Function is an essential feature in Excel for evaluating and performing business analytics.

  1. Select the range of data by which COUNT needs to be applied, then click on ‘Subtotal’ from the ‘Data’ tab.
  2. In the ‘Function’ drop-down list, select COUNT and also specify whether you want it to count empty cells or not.
  3. Press OK and all subtotals with COUNT function will appear accordingly.

Using the COUNT Function in SUBTOTAL has different variations, such as excluding hidden rows or error values.

It is noteworthy that SUBTOTAL ignores other SUBTOTAL function nested within itself. (source: Microsoft Excel Help)

Filtering through data has never been easier, thanks to the SUBTOTAL function. It’s like having a personal assistant that knows all the numbers.

Using SUBTOTAL Function to Analyze Filtered Data

Analyze filtered data with SUBTOTAL in Excel. Use the function to get quick insights. You can use it with autofilter or advanced filter. Enjoy the benefits of SUBTOTAL for precise and customized results.

Using SUBTOTAL Function to Analyze Filtered Data-Using the SUBTOTAL Function in Excel,

Image credits: by Harry Duncun

Filtering Data and Finding Subtotals using SUBTOTAL

To effectively analyze filtered data in Excel, one can use the SUBTOTAL function. It allows users to apply mathematical operations on visible cells only, ignoring those that have been hidden by filters. Below is a 4-Step Guide on how to Filtering Data and Finding Subtotals using SUBTOTAL:

  1. Select the range of data that needs analysis.
  2. Click on the ‘Data’ tab from the ribbon and select ‘Filter’ from the ‘Sort & Filter’ group.
  3. Apply filters to this range of cells by selecting relevant columns.
  4. Choose a cell in an adjacent column, insert ‘=SUBTOTAL’ formula and choose a function type (e.g., SUM, COUNT) from a list of available functions.

It’s worth noting that the first argument of all SUBTOTAL formulas should be between 1-11 or 101-111 if calculated within hidden rows. This represents applying either single calculations (1) or multiple calculations (100). Overall, using Excel’s SUBTOTAL function saves time and enhances accuracy when analyzing filtered data.

To avoid recalculations, it’s possible to add new rows or columns with qualified results while preserving filter settings. This flexibility offers valuable insights into large datasets users could miss otherwise.

Don’t let raw data remain untouched due to its overwhelming size. Mastering techniques such as filtering data is vital for making informed decisions. Save time and gain valuable insights today by using Excel’s powerful features to your advantage through SUBTOTAL functions!

Filtering data has never been so fun – SUBTOTAL Function, the life of the party!

Using SUBTOTAL Function with AutoFilter and Advanced Filter

The SUBTOTAL Function in Excel can be effectively used with both the AutoFilter and Advanced Filter options to analyze filtered data. Here’s a guide on how to use it:

  1. Select the range of data that you want to filter
  2. Apply either AutoFilter or Advanced Filter options from the Data tab
  3. Create a formula using the SUBTOTAL function, which considers only visible cells and not hidden ones
  4. Select the range where you want to display the results of your analysis
  5. Enter the formula into the first cell of this range and apply it to all cells below

By using this method, you can easily get a total count or sum of only visible cells when working with large datasets. It also helps avoid manual errors in calculations.

Additionally, it’s worth mentioning that combining Subtotal with Conditional Formatting can help visually highlight specific patterns within your filtered data.

In a similar instance, my colleague once spent hours manually filtering through a large dataset in Excel for her project. However, she realized she could have simply used the SUBTOTAL Function along with AutoFilter to swiftly zoom into only relevant entries – earning herself time for more important tasks. Why settle for a half subtotal when you could have a grand total?

Limitations of the SUBTOTAL Function in Excel

To grasp the constraints of Excel’s SUBTOTAL Function, these two sub-sections offer assistance:

  • Understanding Limitations in Complex Data Scenarios
  • Working with Data that includes Errors and Blank Cells

Limitations of the SUBTOTAL Function in Excel-Using the SUBTOTAL Function in Excel,

Image credits: by Adam Jones

Understanding Limitations in Complex Data Scenarios

When dealing with complex data scenarios in Excel, it is important to always keep in mind the limitations of the SUBTOTAL function. This powerful tool can be incredibly helpful when calculating subtotals and working with filtered data, but there are situations where it may not provide accurate results. Certain types of calculations and functions, such as formulas that reference other ranges or arrays, may cause errors or lead to incorrect outputs. It is crucial to thoroughly test and analyze your data to ensure the validity of your results.

In addition, one key limitation of using the SUBTOTAL function is that it only works on numeric values. If your data contains text or other non-numeric values, you may need to convert these before using the SUBTOTAL function. Furthermore, if your data contains multiple types of calculations or formulas within a single column or range, you may need to use a combination of functions and filters to achieve accurate results.

Despite its limitations, the SUBTOTAL function remains an essential tool for analyzing and summarizing large sets of data in Excel. By understanding its strengths and weaknesses and carefully evaluating your specific data scenarios, you can use this function effectively and efficiently.

One true history about the limitations in complex data scenarios is that they have led many businesses and individuals into costly mistakes due to inaccurate calculations or misinterpretations of their results. However, by taking a proactive approach to testing and analyzing your data using a variety of tools and techniques (including the SUBTOTAL function), you can avoid these pitfalls and make informed decisions based on accurate information.

When it comes to working with faulty data and empty cells in Excel, just remember: the SUBTOTAL function can’t fix stupid.

Working with Data that includes Errors and Blank Cells

When working with data that contains both errors and blank cells in Excel, it may pose limitations when using the SUBTOTAL function. The function can only count visible cells that are not hidden or filtered out. Thus, if there are errors or blank cells in the range, the function will not work correctly.

To overcome this limitation, one approach is to use the IFERROR function and apply it to each cell in the range. This will help to replace errors with zero values and thus ensure that the SUBTOTAL function can perform accurately. Another alternative is to replace blank cells with zero values using the IF function.

It is important to note that applying these functions might alter the original data. Hence, before making changes, duplicate a copy of the original dataset as a backup.

Facts About Using the SUBTOTAL Function in Excel:

  • ✅ The SUBTOTAL function can perform a range of calculations on a selected range of data in Excel, including sum, average, and maximum/minimum value. (Source: Microsoft)
  • ✅ The function can exclude hidden rows or error values in the selected range. (Source: Exceljet)
  • ✅ The function can be used in combination with other functions, such as IF and COUNTIF, to perform more complex calculations. (Source: Ablebits)
  • ✅ The function automatically updates its calculation when new data is added to the selected range. (Source: Excel Easy)
  • ✅ The function can be used to improve the performance of large Excel files by reducing the number of calculations. (Source: Excel Campus)

FAQs about Using The Subtotal Function In Excel

What is the SUBTOTAL Function in Excel?

The SUBTOTAL function in Excel is a versatile tool that helps you calculate subtotals in lists and databases. It can perform up to 11 different calculations, including average, count, max, min, and standard deviation, among others. You can use it to create summary reports, analyze data, and more.

How do I use the SUBTOTAL Function in Excel?

To use the SUBTOTAL function in Excel, first select the range of cells that you want to calculate subtotals for. Then, select the cell where you want to display the result and type “=SUBTOTAL(” followed by the calculation code (1 to 11) and the range of cells. For example, to calculate the average of a range of cells, use the code 1, like this: “=SUBTOTAL(1,C2:C10)”.

What are the advantages of using the SUBTOTAL Function in Excel?

There are several advantages to using the SUBTOTAL function in Excel, including:

  • It can perform multiple calculations in one formula, saving time and effort.
  • It automatically excludes hidden and filtered cells, which can be useful for complex datasets.
  • It can be used with other functions and formulas to create more complex calculations.
  • It can help you create better and more accurate reports and analyses.

Can I use the SUBTOTAL Function in Excel with pivot tables?

Yes, you can use the SUBTOTAL function in Excel with pivot tables. When you add a calculated field to a pivot table, Excel automatically uses the SUM function as the default calculation. However, you can change this by selecting the field and choosing “Value Field Settings” from the PivotTable Tools menu. From there, you can select the calculation type you want, including the SUBTOTAL function.

What are some common mistakes to avoid when using the SUBTOTAL Function in Excel?

Some common mistakes to avoid when using the SUBTOTAL function in Excel include:

  • Using the wrong calculation code (1 to 11) or range of cells.
  • Forgetting to exclude header and total rows from the range of cells.
  • Including cells that are hidden or filtered, which can affect the accuracy of the calculation.
  • Not updating the range of cells when you add or remove rows or columns from the dataset.

How can I troubleshoot problems with the SUBTOTAL Function in Excel?

If you encounter problems with the SUBTOTAL function in Excel, you can try the following troubleshooting steps:

  • Check if you’re using the correct calculation code (1 to 11) and range of cells.
  • Make sure to exclude header and total rows from the range of cells.
  • Check if there are any hidden or filtered cells that should be excluded from the calculation.
  • Verify if you’re using the correct syntax and if there are any typographical errors.
  • If none of the above works, you can try using the SUM function instead and see if you get the correct result. This can help you narrow down the problem.

Related Articles

Inserting A Row Or Column In Excel

Key Takeaway: Inserting a row in Excel is easy: Select ...

Inserting And Deleting Rows In A Protected Worksheet In Excel

Key Takeaway: Inserting and deleting rows in a protected worksheet ...

Incrementing References By Multiples When Copying Formulas In Excel

Key Takeaways: There are two types of references in Excel ...

Leave a Comment