Adding multiple subtotals in Excel

Apr 28, 2022 | Excel Hints and Tips

Subtotals are always a popular topic on my training sessions and is often a feature even the regular Excel users have missed out on.  Even if you have used them before are you aware that you can add more than one subtotal should you need to. 

Adding the first subtotal 

  1. Sort the data into order based on the column you wish to subtotal on. 
  2. Click in the data (or select it including the headings if it contains any blank rows or columns) 
  3. Select Subtotals from the Data tab 
Subtotal icon

4. Insert a subtotal at each change in the column that has been sorted 

Subtotal dialog box

5. Choose the function required from the following: Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevP, Var and VarP.
6. Choose the Column to add  subtotal to 
7. Select OK 

Adding a second subtotal 

If you stick with the standard tick box options at the bottom of the dialog box you will replace the original subtotal applied with the new one.  Follow these steps to keep them both. 

  1. Click in the data as before 
  2. Repeat steps 2 to 6 as before 
  3. Untick the check box Replace Current Subtotals 
Subtotals

That’s it!! 

NOTE: if you add a second subtotal and then tick the check box for Page Break Between Groups the page breaks will fall in the wrong place.  This is not something you have done wrong.  This works perfectly if only 1 subtotal has been applied.  Don’t panic!!  It is easy to move the page breaks by changing the View to Page Break Preview and then dragging the solid blue horizontal lines to the correct position. 

Keyboard Shortcut 

We all love a good keyboard shortcut but there isn’t a specific one for Subtotals.  However, the use of the ALT key will always give you a keyboard method to access any option on the ribbon so it can be used here too. 

Press ALT once then follow the sequence of letters on the ribbon and icon.  In this case: 

ALT then A then B 

NOTE: Unlike normal shortcuts the keys are not all held down together but are pressed in turn. 

So there you have it, how to add multiple subtotals into an Excel spreadsheet. 

Further Reading

If you’ve enjoyed reading about adding multiple subtotals in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.