Achieve better information organization with subtotals

by Scott Lowe MCSE | Nov 21, 2005 9:42:00 PM

Tags: PRODUCTIVITY, Scott Lowe MCSE, spreadsheet, Microsoft Excel, Figure B, category column

Takeaway: Sometimes we take for granted what users know. While Microsoft Excel may be familiar to you, if you are on a help desk you will have to explain how the application works to users that fall within a vast range of skill level and knowledge. This series of Excel tutorials can make that training much simpler. This installment of the series shows you how to create and use the built-in Excel subtotal functions.

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.

3 comment(s)