Subtotal

Using the Excel wizard to subtotal data can come in handy, and the final presentation is pretty nice, too. It has recently fallen out of favor with me personally because I've discovered and fallen in love with pivot tables. I say that in real life, too. I love pivot tables.

Regardless, here's how you do it:
Step 1: Sort the data based on the column that has the data that you want to subtotal. Let's say you have a list of SKUs (Stock Keeping Units), their associated A,B,C code, along with their current value in inventory. You want to subtotal based on the ABC code, so sort by the column that contains that data. Now, highlight your data.
Step 2: Select Data-->Subtotals. You will get this dialogue box:


You can make plenty of changes here. But for this simple subtotal, I like what it's telling you it's going to do. At each change in ABC, it is going to sum up the total in the Value Column. You can see here that if your list isn't sorted, it's going to add a subtotal to the value column every time there's a change to the ABC. (That would be very bad...) Since I have it sorted right, I expect to there to be three subtotals. You can use the following subtotaling functions:
SUM
COUNT
AVERAGE
MAX
MIN
PRODUCT
COUNT NUMS
STDEV
STDEVP
VAR
VARP

Here's what your subtotaled data will look like:

Click on the + or - sign to expand or collapse data. Here's what it looks like all collapsed.

You can also use the 1, 2 or 3 in the upper left corner to show and hide data. Hit 1 to collapse it completely, 2 to show the subtotaled information and 3 to show all the data along with the subtotals.

There is more to subtotals. Lots more. Let's say you have a much more complex set of data. You want to add subtotals to your subtotals. You can do that with the wizard. Highlight your already subtotaled data, and then choose data-->subtotals once again. This time, uncheck the box that says "Replace Current Subtotals." That will add another layer of subtotals. And, the 1,2,3 you see currently in the upper left corner expand to 4 and 5. I don't know how many times you can do this. But, my advice would be limit it to 3 or 4. (Did I say 3 or 4? I meant to say 2.) Otherwise, you've gotten too complex for your subtotals anyway.

You can also do different types of subtotals on one report, though you have to leave the comfort of the wizard for that.

If you click on the subtotal function, you will see that it's a simple formula:
=SUBTOTAL(9,C2:C30)

The thing that makes this one sum up C2:C30 is the 9. If you replaced the 9 with a 1, it would average the data. Replace it with a 4, and it returns the max value. See below for more of what you can do with the subtotal function.
1: Average
2: Count
3: Counta
4: Max
5: Min
9: Sum
(See Excel help for the complete list. Choose topic -> Subtotal

When using the wizard, you can only have it do 1 function per column. You do not have the option of summing the inventory value and counting the SKUs when you use the wizard. What I have done to get around this and still use the wizard is to tell it to sum the Item and the Value. Once it does it's thing and subtotal my data, I highlight the column containing items, and then do a ctrl-F to Find all cells containing:
subtotal(9,

Then, I replace that with
subtotal(3,

There may actually be a better way to do this. If you know of it and feel like sharing, please do. In the meantime, this works!

Happy subtotaling!!!