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.
Select the Data Tab.
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.
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.
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.
resulting in this.
So you now can see that Subtotalling is a powerful tool that hopefully you will enjoy using.