Excel has a SUBTOTAL function, which ignores hidden or filtered rows. There is a Subtotal feature too, that quickly groups your data, and adds one or more rows of subtotals. Do we still need these Excel subtotals though, now that we have pivot tables and the AGGREGATE function?
The SUBTOTAL Function
When you have a list of data, the SUBTOTAL function can calculate a total that ignores filtered rows. In Excel 2007 and later, it can also ignore rows that were manually hidden.
That’s helpful when you’re filtering a list on one or more columns, and want to see the totals for the visible rows only. For example, in the screen shot below,
- SUBTOTAL shows the sum for the West region only, for a total of 300
- SUM includes the hidden rows that have the East amounts, for a total of 700
The AGGREGATE Function
If you have Excel 2010 or later, the AGGREGATE function is a more powerful version of SUBTOTAL.
- It has 19 functions, compared to the 11 functions in SUBTOTAL
- There are 8 options for what to ignore, compared to the 2 options in SUBTOTAL
This video shows the difference between the SUBTOTAL and AGGREGATE functions.
Why Keep Using SUBTOTAL?
Since AGGREGATE is more powerful, why would you keep using SUBTOTAL? I can think of a few reasons – do you know of any others?
- Compatibility – AGGREGATE won’t work if you need to share your files with anyone using Excel 2007 or earlier
- Habit – If you’re used to the SUBTOTAL function, it’s easier to just keep using it, instead of learning a new function
- Ease of Use – It’s easy to insert the SUBTOTAL function below a filtered list – just select a cell, and click the AutoSum button. Excel automatically inserts SUBTOTAL for you, with the Sum function (9) selected.
Excel Subtotal Feature
Excel has a Subtotal feature too, that lets you quickly group data, and show one or more levels with subtotals. In the next screen shot, the list is sorted by Region, so I can show a Sum at the end of each Region’s data.
Excel creates an outline for the list, and automatically inserts a SUBTOTAL function in each subtotal row. You can click the outline buttons at the top left, to see just the grand total, or all the totals, or the details and totals.
Why Keep Using the Subtotal Feature?
When pivot tables were introduced, long ago, I thought that people would use those, instead of the Subtotal feature. But some people love Subtotals, and keep using them.
Here are my guesses as to why – do you have any other reasons?
- Details – It’s easy to show or hide the details, if you’re analyzing the totals
- Changes – While troubleshooting, you’re working with the live data, and can quickly change a record, to correct a total
- Habit – Like the SUBTOTAL function, it’s easier to use a familiar tool, than to learn a new one
Subtotals By Month
If you are still using the Subtotal feature, here’s a trick that you might not know.
Recently, Bill Jelen discovered that you can group by months, without adding any extra columns to the data. In the past, Bill always created a formula to format the dates, and grouped on that column. Now he simply formats the date cells, and it works just as well, without extra column.
Watch Bill’s video to see both methods – the new technique starts about the 2:00 minute mark of the video.
________________________
