Bacon Bits:

When building Subtotals, Excel asks you to select the columns to which you would like to add subtotals. This problem is that Excel gives you a matchbook-size dialog box that allows you to select one column at a time. So if you require subtotals on many fields, you need to click, click, click your way through the list of columns one by one.

Believe me when I say that this gets old Tout de suite.

Because I'm generally a lazy bastard, I've come up with way to apply subtotals on all my fields, while only selecting one field in the subtotal dialog box.

Confused? Don't worry, I am too.

Step 1: Create your subtotals only on one field. In this example I've only applied subtotals to the Revenue field.

Note that the formula bar contains a SUBTOTAL formula. This was automatically inserted by Excel. This formula makes up the core of this trick.

Step 2: Click the Level 2 trigger so that only the totals are showing.

Step 3: Start at the first subtotal, then drag to highlight all the fields which you want to apply your subtotal formula.

Step 4: Press Alt+; on your keyboard to select only visible cells. This will ensure that subtotals will only be applied in the Total rows.

Notice that the active cell (in the screenshot shown here) is still the cell with the first completed subtotal. If your active cell is not a cell with a subtotal, go back to Step 3.

Step 5: Press F2 on your keyboard to go into Edit mode

Step 6: Hold down the Ctrl key while pressing Enter.

Boom! The Subtotal formula will be entered into all the empty cells, effectively applying subtotals to all fields.

If you need different operations for each column,  you can use this technique to get the base Subtotal formulas implemented. Then simply change the function numbers using find and replace.

So to do an Average subtotal, you would simply change SUBTOTAL(9,C2:C7) to SUBTOTAL(1,C2:C7).

The 9 in SUBTOTAL(9,C2:C7) tells Excel to use Sum.  The 1 in SUBTOTAL(1,C2:C7) tells Excel to use Average.

Here are all the SUBTOTAL function numbers.

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Now that's one spicy trick. You're welcome Excel community.

If you're new to Subtotals and have no idea what the hell we're talking about, see this Video

******UPDATE******

Orange_Juice correctly points out that "this will not work where the number of rows in the subtotal are not the same for each subtotal."

This is true.  I have exactly 6 rows in each of the subtotals.  In my example, I was working with structured sales periods, and each Region had 6 periods of data.  This may not always be the case in your dataset.

So I guess this trick is closer to Mild than Spicy.

• Previous Entry: Finding and Replacing Nulls in Access
• Next Entry: Replacing Large Amounts of Data in Access