Excel Subtotals (And Subtotals (and...
Subtotals are powerful way of summarizing data in Excel, but they require
some caution. When thinking about Excel subtotals, it’s best to think about
how a person would manually create subtotals: start with a pile of paperwork
and put it all in order. If you wanted to subtotal by month, you would group
all of your data by months. If you were subtotaling by customer, you would
divide up all of your receipts and invoices by customer. If you wanted month
AND customer, you’d have to divide everything into piles of months, and then
sort each pile by customer.
The same process applies to subtotals in Excel. It’s important to use the
Sort function and sort data before trying to subtotal it. In the example
above, we wanted to sort by month and customer. So in Excel we would sort our
data using Month as the Primary key, and Customer as the secondary key shown
below.
After sorting, go to Data and click Subtotals. You’ll see the dialog box
below. As our first subtotal, choose to insert a line At each change in:
Month. Excel will force a line between rows whenever the Month changes, and on
that row will place the Sum of all Receipts and Payments.
We’re not done yet, since we also want a breakdown by customer within the
month. Go back to Data, Subtotals and bring up the dialog box again. This
time, choose At each change in: Customer and be sure to clear the checkbox
marked Replace current subtotals circled in red.
You’ll see your data broken down by customer and by month with subtotals
and grand totals at the bottom.
You can keep going with finer and finer subtotals but don’t forget that
all of your data must be sorted appropriately before subtotaling!