Group and Subtotal your data in Microsoft Excel 2007

If you're new here, you may want to subscribe to Windows Reference RSS feed Thanks for visiting!

Most of us use Microsoft Excel as a simple spreadsheet than taking advantage of its other potential usage. One such potential usage for Microsoft Excel 2007, is the use of Sub-Totals function which allows you to perform simple mathemetical operations like Sum, count, Average etc based on a common criteria.

Let’s take simple example, say you enter your daily expenses on a nice simple spreadsheet or even import from an external systems CSV or TXT file. As you would expect, the entries are not organised for it to make any sense of your spending. Here, Excel can help you organise this data and provide you with a subtotal based on where your spending goes.

I here use a example, where I note how much I spend on a day at different stores like Tesco, ASDA or TopShop and use Excel to subtotal and provide me with my spending on each of these stores and give me a grand total on my entire expenditure.

First Step – Custom Sort

Sort the data by selecting all the cells with entries on it with/without its label row using the “Custom Sort” function under “Sort & Filter”. Here you can see, I have two levels of sorting. One at the description level where I enter the store name and another level is by date of spending. This can be in anyway you prefer.

And, now you can see all my spending ins Asda, Tesco and Topshop togethor.

Next Step – Subtotal

Again, select all cells including the label row and click Data tab and then “Subtotal” under “outline“.

Here,

choose “Description” under “At each change in”
a function like “Sum, Average etc” under “Use”
“amount” under “Add subtotal to”

And, optional items like “Summary below data” and click OK. And, there you see the subtotals and the grand total for each of the shop.

As, I say, customize it to your needs and can save lot of time and hassle in organising your data.