In a
previous article, I went over sorting and filtering information in Excel. These two features help you to better organize your
information in a useful way. But, those two features alone can’t answer every
data analysis question. Here are some examples: How much have you spent in your
equipment line for the month of September? Now, how much of that equipment line
was spent by IT? How much by grounds? How much has the Administration
department spent on payroll? Or, how many transactions have been processed by
the IT department? Believe it or not, by using Subtotals, with just a few
flicks of your mousing wrist, you can get answers to
all of these questions and more.

Data analysis series

This
data analysis series consists of these articles:

  • Part 1: Sorting
    and Filtering
  • Part 2: Subtotals
  • Part 3: Pivot Tables
  • Part 4: Simple Graphs
  • Part 5: Advanced graphing and PivotCharts

There’s
a reason that I waited to introduce subtotals until part 2. While this part is
fairly short, subtotals rely heavily on sorting in order to work their magic. So,
if you aren’t familiar with sorting, read the previous article before
continuing. I’ll be using the same budget spreadsheet for the examples in this
part.

Subtotals and sorting

Subtotals
work in a particular way. Basically, when you subtotal, you’re telling Excel
that “when a certain category changes, to total the preceding category”.
For example, suppose you want to get the total expenditures in each category in
your budget spreadsheet. In this case, you would first need to sort your
spreadsheet by the category column so that the subtotal function would be able
to work its magic. Take a look at Figure
A and then Figure B. In Figure A, I did not sort the
spreadsheet by the category column before applying subtotals. In Figure B, I did take this step. See the
difference?

Figure A
This is something of a mess and is basically useless.

Figure B
This example makes a lot more sense and gives you the information you were
looking for. Namely, you’re getting a subtotal for each category.

In Figure B, I’ve told Excel that, every
time the category changes, I want Excel to give me a total of the amounts in
the previous category. For example, when the category changed from Consulting
to Equipment, Excel did a quick calculation and added up all of the records
that had consulting as a category, and came up with a total amount spent of
$8,674.54.

Before
you can create your own subtotals, sort your spreadsheet on the column you want
to analyze. In this example, we were analyzing the category column of the
spreadsheet. If, for example, you wanted to see total spending by department,
you would need to sort by that column instead.

Creating subtotals

With
your spreadsheet appropriately sorted, you can get to the task of getting
subtotal information. With any cell in your data selected, go to Data |
Subtotals. A window similar to the once shown in Figure C appears.

Figure C
The subtotal window contains only a few options, but is quite flexible.

You
need to provide information for each question on this screen:

  • At each
    change in:     This should always match your sort order. This is the column on
    which you want to perform some data analysis.
  • Use
    function:     What do you want to do when the category changes? In the example
    used earlier, you saw the effect that the “Sum” function had on the
    spreadsheet. It added (or summed) the Amount column from the previous category.
    You actually have quite a number of options here. You can add up a column,
    count the number of entries in the previous section (useful if you want to find
    out how many transactions have been processed by the IT department, but you don’t
    have any interest in the amount of those transactions, for example), and can
    perform more advanced operation such as standard deviation. You could also
    identify the lowest and the highest values in a particular column. For example,
    suppose you want, for each department, to produce a report that indicates what the
    largest expenditures were. Using the “Max” function, you could
    accomplish this goal.
  • Add
    subtotal to:     To which column would you like to apply a subtotal. In the
    example I showed you before, this is the Amount column. For each category, you
    saw a total amount.
  • Replace
    current subtotals:     You can apply more than one subtotal rule to a
    spreadsheet. When this option is selected, when you create a new subtotal, the
    old one is removed first.
  • Page
    break between groups:     In Figure B, I showed you the summary view of the
    subtotal application. In reality, you can also opt to see all
    of the source data with the subtotal interspersed where appropriate. Enabling
    a page break between each group is a useful way to be able to quickly print the
    resulting subtotal report. For example, suppose you want to provide each
    department’s manager with a list of his expenditures and a subtotal for those
    expenditures. A page break between each department could come in handy.
  • Summary
    below data:      In short, a grand total.
  • Remove
    all:     Remove all of your subtotals. Does not remove anything except the
    information added by the subtotal function.

    • After
    you get done choosing your subtotal configuration, click OK.

Subtotals example

The
best way to understand subtotals is to see them in action. For this example, I
will create three subtotals. The first one will total the amount column for
each department. The second one will add a subtotal for each category within
each department. The third one will simply count the number of transactions
handled by each department.

Why do
I need to use multiple subtotals? While you can add multiple subtotals at the
same time by selecting multiple columns in the “Add subtotal to”
window, you cannot do so if you want to use different functions for each
subtotal, or of you want a different break point. In this case, I’ll be using
the Sum function on the Amount column for both of the first two subtotals and
the Count function on the Date column for the third subtotal. For the Count
function, it doesn’t really matter which column you choose as long as there is
information present. Also, between subtotals, I’ll be clearing the “Replace
current subtotals” option so that the subtotals are cumulative and do not
erase one another.

Before
I go to the Subtotal window (Figure D),
I’ll sort the spreadsheet by Department and Category. The resulting sheet is
shown in Figure E.

Figure D
This will sort my budget spreadsheet first by department and then by
category.

Figure E
The sorted spreadsheet.

In the
subtotal window, I will specify that, each time the department changes, I want
to apply the Sum function to the Amount column. For this first subtotal, I will
leave the “Replace current subtotals” box enabled. Figure F shows you the Subtotal window
configuration and Figure G shows you
the result.

Figure F
Add a subtotal for the department change.

Figure G
Note that there are now subtotals for each department.

The
second subtotal counts the number of transactions for each department. I’ll
leave the “Replace current subtotals” box blank and, this time, will
indicate that I want to count the Date column every time the department changes.
Figure H shows you the subtotal
window and Figure I shows you the
cumulative result of both subtotals.

Figure H
Add a subtotal to count the transactions in each department.

Figure I
Note that there are now two subtotal lines between each department. Once is
labeled “Count” and one “Total”.

The
third subtotal is similar to the first, but this time, I’ve selected the
Category column and have cleared the “Replace current subtotals”
option. Figure J shows you the
subtotal window and Figure K shows
you the cumulative result of all three subtotals.

Figure J
Add a subtotal for the category changes.

Figure K
It’s a little messy, but there are now subtotals between each category,
too. You’ll see how to clean this up a bit.

Cleaning up your subtotal view

As Figure K clearly shows, too many
subtotals, while potentially useful, can muddy the waters a bit. As you started
adding subtotals, you might have noticed the addition of some controls at the
left-hand side of your spreadsheet.

Figure L
You can use these controls to clean things up a bit.

At the
top of the highlighted section in Figure
L, notice the numbers in boxes. In this case, you see the numbers 1 to 5. If
you had only a single subtotal, you would see only the numbers 1 to 3, but as
you add subtotals, another number added. Since we have three subtotals in this
example, there are five boxes. Below each of these numbers, notice that there
are lines, minus signs and small dots that happen to line up with each of the
numbers across the top. All of the numbers in boxes, as well as the minus signs
and, as you’ll soon see, plus signs, can be used to control what level of data
you see. As such, Excel calls these controls “level controls”.

Again,
the best way to explain this feature is to show you a couple of examples. What
you see in Figure L is a full five
levels of information, which shows you all of your subtotals plus all of the
supporting data. Now, suppose you want to see just the subtotals without the supporting information. To do this,
click the second to last level control. In this case, you would click the number 4. Take a look at Figure M to see the result.

Figure M
Notice that the supporting data no longer appears but that you now see all
of your subtotals.

This
looks a lot better! Without all of the supporting data muddying things, you can
get a better look at the information you really wanted. You still see all three
subtotals. Note that you see a category subtotal for each department as well as
a department subtotal and a count of the number of transactions in each department.

As you
click lower levels, you’ll get less in-depth information and more of an
overview of the data. In this case, the numbers 2, 3, and 4 correspond to the
subtotals you created. So, when you click 4, you get just all three of your
subtotals alone. If you click 3, you’ll do away with the third subtotal
information—in this case that was the breakdown by category in each department
(See Figure N).

Figure N
The level 3 view removes the category subtotal and leaves you with the
first two subtotals you created.

Likewise,
the level 2 view removes the subtotal that counted the number of transactions
in each department and leaves you with the department breakdown (Figure O) that you saw after we created
the first subtotal in this example.

Figure O
The level 2 view removes the transaction count subtotal and leaves you with
just the first subtotal you created.

Finally,
no matter how many subtotals you create, the level 1 view always shows you the
same thing: the grand total for your work (Figure
P).

Figure P
The level 1 view shows you the grand total information for your
spreadsheet.

Look
back at Figure O and notice the plus
and minus signs below the view levels. This is the level 4 view and, below the
number 4 box, you see all plus signs while everything at the higher levels is a
minus sign. This is Excel’s way of letting you take a look at only the information you really want to
see. For example, suppose you want to see some level 5 detail, which would show
you all of the supporting information for a particular level but you don’t want
to see it for every single department or category.

To see
level 5 detail for just a particular category in a particular department, click
the corresponding plus sign. For example, suppose you want to see the detail
for Administration Supplies. Click the plus sign to the left of row 16 (see Figure Q) to get this information.

Figure Q
In this figure, you see just the detail related to Administration’s
supplies purchases. Note that each detail row has a dot under the level 5 view
heading. This signifies that you are looking at a level 5 view for this section
of the spreadsheet.

What is possible

With
subtotals, you can really start to analyze information far beyond that possible
with sorting and filtering. In then next article, you’ll use this same
spreadsheet to learn how to create graphs.