As easy as 1-2-3, subtotalling in Excel saves time and avoids errors Image Source : pace
As easy as 1-2-3, subtotalling in Excel saves time and avoids errors

As a trainer I often come across people doing things the very long way round. For instance, a lady had a large list of sales items and personnel which she needed to report on. She spent ages sorting and copying and pasting then adding up sections and creating complicated links to other sheets for the results she wanted to show. When you have a large spreadsheet with a list of items containing text and numerals you may find that the Subtotalling in Excel tool is very handy and all you need.

Note: This Tip is written in Office 2016 so images of tools or ribbons may differ from your version. The features still work the same.

So here is a sample of a spreadsheet you may have. subtotal1

Firstly, sort by the most important heading, in this case I would say “country”. subtotal2

Select the Data Tab.

Select the Subtotalling tool subtotal3

In this dialog box select Country for the “At each Change in”. Subtotal4

Use the right function in this case Sum.

The Add Subtotal to section is what you will be totalling so select a numeric column.

Extended Price is a good choice.

Leave the “Replace current subtotals” checked, if you have a very large data list then maybe the “Page break between groups” would be an advantage, so check that one is you need it. Leave also the “Summary below data” checked as this usually makes more sense of the results. But do try this again without this box checked and see if it suites your needs better.

Select the OK button.

In the example below I have taken a screen shot of the top of the list then another shot of the bottom to show that you get subtotals under each sorted country for the Extended price and at the very bottom you also get the grand total of all extended prices.  Subtotal5


Once you have this first subtotal you can collapse the detail and only see the subtotal figures and the grand total by clicking on the number 2 button just left of the column headings.


Selecting the number 1 button would result in just seeing the grand total. Subtotal8

Now you have this tool in work you can build on it and create more subtotals. I have chosen to add a selection of sales person and calculating on Quantity, with the “Replace current subtotals” NOT checked. Subtotal9

resulting in this.


So you now can see that Subtotalling is a powerful tool that hopefully you will enjoy using.


Please enter your comment!
Please enter your name here