Creating Subtotals in a List in Less than a Minute

One of my favorite features in Excel is the subtotal feature. So quick, so easy!

Please note: This method only works if you have not turned your list into a table by using Insert Table on the Insert Ribbon, which is a whole other topic.

To start, you need a list (remember, standard list, not a table) you would like to summarize data with subtotals for at least one category. Such as sales data that contains sales dates, regions, products, quantities and product sales.

To get the subtotals, you first need to sort the table by the column you to summarize, let’s say region. This is important because Excel will need to have the data grouped together to get the correct subtotal for the group. And, the way to get the data grouped together is to sort it.

Once your list is sorted correctly, you can then add your subtotals like this:

1. Put your active cell in the list. It doesn’t matter where; it just needs to be in the list somewhere.

2. Click on the Data Ribbon and then click on the Subtotal button on the far right.

3. After you tell the Subtotal box what category to subtotal, tell it which function to use. You have several to choose from, you aren’t limited to just a ‘sum’ subtotal!

4. Then you tell the Subtotal box which column(s) to subtotal. In my case I will subtotal City.

5. If you had previous subtotals you could replace them with a check mark. We don’t have any previous subtotals so we’ll remove the check mark, if there is one.

6. You can print each category on its own page if you’d like.

7. If you don’t put a check mark in Summary below data, the subtotals will go at the top of the category instead of at the bottom.

Excel also adds grouping bars on the left so you can easily reduce and expand all or individual groups. If you click on the 2 on the top left, it will group all the city totals together so you can see them at a glance.

It is amazing how this little tool makes life so easy!

Remember, this only works if you don’t convert your list to a table, which is a whole other blog post on another day!

If you are interested in knowing more about Excel for your department or company, fill out the contact me form and let’s discuss the best option for you.

Until the next post, ~live life, not work!