What is Excel SUBTOTAL formula and 5 reasons why you should use it

I use SUBTOTAL all the time, could not live without it.

Another neat feature about SUBTOTAL is that you will be able to collapse/expand sections of your data set with the +/- buttons that magically appear to the left of the rows containing a subtotal which was created with the Data menu (Excel 2003).

I have been using the subtotal formula for years. It’s especially useful when you have very large lists. Instead of putting your SUBTOTAL function at the bottom of each row, place it at the top of the columns that you want totals, counts, averages, etc.

If you frequently extract data from transaction systems, for example, billing records – the subtotal function placed at the top of you data (above the headings) is a particularly useful way to summarize data in conjunction with filtering.

The only problem with SUBTOTAL is its inability to perform a SQL COUNTD (Count Distinct) function. This can be a big problem if you want to summarize (for example) billing records that include invoice line-item details.

Using Excel’s AVERAGE subtotal function will not result in the correct answer. It will compute the average of all of the rows in the list. In SQL, COUNTD effectively deals with this problem. I’ve read about some exotic methods that address this problem. All of them are a bit of a pain to implement.

A nice feature of using the Subtotal is that you can nest subtotals. Simply remove the check from the checkmark out of Replace Current Subtotals.

I LOVE subtotals!!! When I discovered them, really sped up a lot of my accounting work in a second. Thanks for this blog I share it with everyone I work with

Great post. I’ve never used SUBTOTAL much myself as I tend to jump straight to Pivot Tables, however your post has ensured I will be exploring it a bit more!

I love your posts and have learned so much from them. I am always eager to get the next one. My only suggestion is that you slow down the animation on the examples. They go so fast it is hard to quickly understand what you are trying to show us. Other than that…great job.

I have used subtotals in the past, but never dreamed they were so versatile. I never could figure out how to add subtotals while ignoring other subtotals in the range. Wow! This is great.

Great work on this XL stuff. I’ve been using SUBTOTAL for years, and especially together with Filters, they add powerful productivity to a spreadsheet full of too many numbers. I think a clarification is needed for this line: “So, for example, =SUBTOTAL(9,A1:A10) will give us the sum of all values in A1:A10, provided none are filtered(more on this filtering thing below).”

Actually, while it is true if nothing is filtered, =SUBTOTAL(9,A1:A10) is terrific in a filtering situation: as you add filtering, it does indeed change to reflect the filtered values. That is its strength. And as Dan said, put those SUBTOTALS at the top of the tables, above the Headers for example (with at least one blank row between them!), and they are then obvious to the reader, and already labelled.

As to using the “109″ function, to ignore “hidden” row data, I stay away from it, for two reasons: one, I don’t like a “Total”, or “SUBTOTAL”, showing that the reader cannot check on their own – if they find it doesn’t add up, their confidence in the entire spreadsheet is broken; two, I think sometimes when setting and resetting filters that these hidden rows become unhidden.

hth
Steve

I really like to use it to create reports for teachers’ testing data because it also will create a page break (when using the menu). I can download an entire school’s data, subtotal and print for each teacher in a matter of minutes . . . .

COL A COL B
a 100
a 50
SUBTOTAL 150
NOT A SUBTOTAL 1000
b 125
b 25
SUBTOTAL 150
c 145
c 5
SUBTOTAL 150
GRAND TOTAL 1450 ADDS EACH SUBTOTAL
FORMULA PLUS THE “NOT A
SUBTOTAL” ROW.

USE WITH CAUTION; THE “GRAND TOTAL” IS A SUBTOTAL FORMUA
FOR ALL OF THE ROWS. THE RESULT OF 1,450 IS CLEARLY MISLEADING.

Controlling the ‘Type of Total’ variable with option buttons is a great way of saving real estate on Dashboards & adding a small wow-factor to end user functionality.

“type of total” is a parameter to SUBTOTAL this working i cant do it pls. teach me

Thank you for this article. I use COUNTIFS and SUMIFS for most of my work as you can have tons of them and it won’t slow down the calculations like the DBSUM and DBCOUNT, etc always seem to do . Have not really explored SUBTOTAL but like what has been brought to our attention. I would be really, really be pleased to see the COUNTIFS and the SUMIFS idea extended to MIN, MAX, AVERAGE, STDEV, PRODUCT, and VAR. (filters in the formula, not because the database is filtered and rows hidden) Again, Thank you for this article.

I too did not used it before.
But as you have highlighted these good points about SubTotals I intend to use it more and more.
Thanks.

Tables such as shown in 5 with nested subtotals are frequently used to summarize data

For a small data set, one quick way of generating this is the use of ‘alt + =’ keyboard shortcut.

instead of manually typing formulas or using the wizard nested sum can be generated

The best part is when ‘alt + =’ is used for the grand total it adds up only the subtotals and not all the figures.

Hi, great post, but can you explain in a bit more detail how number 3 works? Or else provide a sample file?
Thanks in advance.