Efficient Data Analysis with the SUBTOTAL Function in Excel

Learn how to use the SUBTOTAL function in Excel to perform efficient data analysis. Understand the different function numbers and how they can be used to perform calculations like sums, counts, averages and more. A comprehensive guide to mastering the powerful tool of Excel.


Tired of nonsense pricing of DocuSign?

Start taking digital signatures with BoloSign and save money.

If you want to become an Excel master, then learning how to use the SUBTOTAL function is a must. SUBTOTAL allows you to quickly analyze and summarize your data by performing calculations like sums, counts, averages and more. By understanding how to use this powerful tool effectively, you can save a great deal of time and make data analysis much easier.

The SUBTOTAL function has two arguments: a function number and a range of cells. The function number determines what type of calculation will be performed, while the range of cells indicates which cells will be included in the calculation. For example, if you wanted to calculate the sum of the numbers in cells A1 to A10, you would use the formula =SUBTOTAL(9,A1:A10).

The SUBTOTAL function has 11 different function numbers that can be used for various calculation types, including: - 1: AVERAGE - 2: COUNT - 3: COUNTA - 4: MAX - 5: MIN - 6: PRODUCT - 7: STDEV - 8: STDEVP - 9: SUM - 10: VAR - 11: VARP

When using the SUBTOTAL function, it's important to note that it can ignore hidden rows, which can be useful for excluding data from calculations when needed. This is especially helpful for efficiently analyzing large datasets that contain hidden rows that you don't need to include in the calculation.

Finally, if you're working with a worksheet that has multiple subtotals, you can use the SUBTOTAL function to quickly and easily calculate totals for the entire worksheet. This can be done by using the function number 109 instead of one of the other function numbers.


Paresh Deshmukh

Co-Founder, BoloForms

28 Feb, 2023

Take a Look at Our Featured Articles

These articles will guide you on how to simplify office work, boost your efficiency, and concentrate on expanding your business.