Subtotal Command: A Working Exercise
Excel has the ability
to insert or remove dozens of subtotals with just a single click of a
button. Consider a worksheet containing 10 columns and 100 rows of data.
According to Collins, most CPAs would spend 5 minutes or more inserting
blank rows, wiring formulas, copying formulas, and formatting the data
to generate totals, subtotals, and grand totals, yet Excel’s DATA
SUBTOTAL command will do all of these steps in less than 5 seconds. Go
ahead - give it a try.
If you want to work
with this example Excel file directly, you can download it by clicking
here:
Start With Raw Data
The subtotal command
will work with virtually any data, but the more complex and voluminous
your data is, the better it works. In the example below, we have data
for 4 different types of marketing campaigns that were conducted in
three different cities in 4 different months. For each marketing
campaign, the company has recorded the cost of the marketing campaign,
the number of responses/redemptions, and the resulting sales for each
marketing campaign. A simple formula calculates the net profit or loss
for each campaign.
Next simply place your
cursor anywhere in the data area, and select DATA SUBTOTALS from the
menu. Excel will automatically select all data in the relevant data
range and the following dialog box will pop up:
It works best to have
your data arranged in one contiguous block of data with no empty columns
or rows; however you could still accomplish the same feat even with
empty columns or rows simply by highlighting the relevant data range
before selecting DATA SUBTOTALS.
Press OK, and Excel
inserts the desired subtotal and grand total formulas automatically, as
shown below:
For an added touch, now
select FORMAT AUTOFORMAT… (as shown below)
…and Excel formats your
data for you too.
On the left hand side
you can see that Excel has also created a collapsible outline which
allows you to summarize your data with yet one more click of the button
on the number 2 in the outline bar. The results will look like this:
A closer look at the
Subtotals dialog box shows that you have the option to control which
column is used to create the subtotals, as well as whether the results
presented are SUMs, COUNTS, AVERAGES, MAXIMUM, MINIMUMS, or PRODUCTS.
While I do not go into
a detailed explanation here, this exercise is really impressive when you
add to this the use of Sorting and AutoFilters. And as if that were not
enough, Pivot Tables offer an even more advanced method for achieving
the same result – but Pivot Tables are exponentially better.
If you want to give
this a try yourself, you can download my example Excel 2002 file here:
http://www.exceladvisor.net/sub/creative/web-subtotals.xls
- END -
