Do You Still Need Excel Subtotals?

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 Subtotals 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 Excel Subtotals 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.

________________________

Save

3 Responses

1. David says:

I still use subtotal feature when I receive data with blanks between rows and due to the construction of the worksheet, can’t delete those blanks. I can select the data | subtotal feature | For each change in Name, subtotal sales.. Subtotal feature ignores the blank rows.

2. Jim Palmer says:

Thank you for the very clear explanation of the Aggregate function. Your website is extremely informative and an excellent resource. I respectfully suggest that it is likely not a good idea to ignore errors. I think the sum of something containing #N/A should be #N/A. For instance if sales data for East, West, North and South were 10, 15, 20, #N/A it would be better to show that the sum is #N/A than to mask the fact the data is incomplete. Excel users could use IF, ISNA or IFERROR functions to ensure all of the inputs variables within the range the Subtotal or Aggregate refer to are valid.