Jan 122010
 

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.

Incoming search terms:

  One Response to “Group and Subtotal your data in Microsoft Excel 2007”

  1. Wondering if you know how to put subtotals on the adjoining rows where I have two labels in the same row?

    So to explain this graphically I have:

    City Dept
    —— ——–
    Region Manager 1
    Staff 1
    Staff 1 Total
    Manager 2
    Staff 2
    Staff 2 Total

    But want to get it like below if possible:

    City Dept
    —— ——–
    Region Manager 1
    Staff 1
    Manager 2
    Staff 2
    Staff 1 Total
    Staff 2 Total
    I was thinking I need to group the sub totals but playing about with it I couldn’t get it to do anything like what I want, and search on google to no avial.

    Thanks

    Andrew

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>