How to use Excel’s Subtotals feature - Step by Step
At my organization there are many intelligent people. Some of them have been in their field for over 20 years and they are well educated in what they do. However, there’s always a need for me to run reports and correlllate information for them. I usually run a standard report and manipulate the data and spreadsheets accordingly to fit the user’s need. I feel that Excel’s Subtotal feature is one of the most powerful tools to condense and summarize information, without having any programming or report writing skills. Here’s how I do it:
1. Get your Data into Excel.
Typically, getting the standard data is what takes the most time, or the most effort. I often get the question - How much did we sell to customer X and what did they buy summarized by item. The problem is that in a week or two, you’ll get a similar question and you’ll have to run a different version of the report because they want to view it differently. In my example, I’m taking basic sales information and subtotaling by Customer, then subtotaling by Item. You can expand this concept out to other levels and applications, but I feel this is the simplest to explain in generic terms.
For my report, I’ve decided to pull: Invoice Number, Invoice Date,Customer, Item, Price, Qty, Extended (price * qty). I’ve sorted by doing Data->Sort. I chose By customer first, then by item.
2. Create a simple Subtotal
If you’ve never used subtotal before, you’ve been missing out. First, just click on a cell that has data, so Excel doesn’t complain about any errors locating data, Row #2 is fine. Subtotal is located in Data->Subtotal. The window that comes up will ask 1. At each change in, 2. Use function, 3. Add subtotal to, 4. Replace current and a few other options. For this example, we want to first get subtotals by Customer, so choose that for #1. We are doing a simple adding subtotal, so choose Sum for #2. For #3, these are the fields that are getting subtotaled. In this example, we’d want to see the Qty of each item as well as the Extended sales, so choose those 2 fields. In this first case, leave the default of ‘Replace current subtotals’ checked off. See below:
After hitting OK, your data will look like this:
You will see a [1][2][3] in the top left of your window. These options will be used to expand or contract the different levels of view. For now, contract them by hitting #2 and you’ll see it shrink to show you ‘Sales by Customer’. You can also now resort your data. Click on the Extended column where the subtotals are and sort descending to see which customer did the most sales.
3. Create a second level Subtotal
At this point, you probably want to know your top selling items to each customer. In order to do this, expand everything back out by hitting the [3] in the top left of the window. Click on the the first piece of data in the ‘Item’ column, then go to Data->Subtotal. The same options will pop up. You want to chose the below settings:
Notice this time we unchecked ‘Replace current subtotals’. That is how Excel will know to create subtotals within our current subtotals. Your data will now look like this:
As you can see, everything is grouped by customer, and under that, it is grouped by item. This method is very powerful and can provide you with an immense amount of power when analyzing your data. Play around with this a little more, if you like. With this little, simple report, you can get subtotals by item, grouped by month. Sales by month, by customer…etc. As long as you know how to calculate the information you are looking for, manipulating this concept to fit your needs will provide you with a wealth of knowledge.
Digg buttons brought to you by diggZ-ET (WordPress Plugin)
