After Hours

10+ tips for working with Excel's Subtotal feature

Excel's Subtotal feature can save you tons of time, especially if you know a few efficiency tricks.

Excel's Subtotal feature is geared for efficiency. You can get the same results manually, but Subtotal is quicker. These 10 tips will help you get the most from the feature once you learn the basics.

1: Use consistent values

The Subtotal feature evaluates groups, so in order for subtotaling functions to return the correct results, you must use consistent values. Each group's value must be the same across all records. For instance, you wouldn't want to enter Baked Goods & Mixes, Baked Goods and Mixes, Baked Good & Mixes, and so on. The value must be identical. Otherwise, Excel will evaluate slightly different entries as separate groups, and the subtotals will be incorrect for your needs.

2: Always sort first

Because the Subtotal feature evaluates groups, you must sort your data first. Specifically, sort by the column that contains the grouping value before you execute the Subtotal feature.

3: Remove subtotals

Eventually, you might want to remove the subtotaling rows and the levels pane to the left of the sheet. To do so, click Subtotal in the Outline group and then click the Remove All button at the bottom of the resulting dialog box.

4: Hide the subtotal level buttons

While the Subtotal feature is active, Excel displays a pane to the left that shows three viewing levels:

  • 1 displays grand totals
  • 2 displays subtotal rows
  • 3 displays everything

To temporarily hide (and unhide) this pane, press [Ctrl]+8. You can hide the pane without removing the subtotaling rows.

5: Subtotal multiple fields

You can add subtotals that use the same function to more than one column, at the same time. Figure A shows the settings required to return an average for both the Unit Price and Units In Stock fields, shown in Figure B.

Figure A

Check more than one field to view additional subtotals in the same row.

Figure B

A single row of subtotals is efficient.

6: Subtotals in multiple rows

You can combine different functions by running two different subtotal tasks. You must uncheck the Replace Current Subtotals option when inserting subsequent rows or you'll end up with only the current settings. Figure C shows the settings for adding a second subtotaling row for summing the units on order, shown in Figure D.

Figure C

Uncheck the Replace Current Subtotals option.

Figure D

Use subsequent subtotal tasks to add different subtotaling functions to the same subset.

7: Copy only subtotals

You can review subtotals without the detail records by clicking 2 in the subtotal levels pane (#4). However, if you attempt to copy only the subtotals, Excel will copy the subtotaling rows and the detail records. Fortunately, Paste Special offers an easy workaround. To illustrate, click 2 to hide the detail rows in any subtotaling example (we'll work with the data in Figure B). Then, use Paste Special as follows:

  1. Select the subtotaling subset, C6:G62, shown in Figure E.
  2. Press [Alt]+;. This is the equivalent of pressing [F5], clicking Special, selecting the Visible Cells option, and clicking OK.
  3. Press [Ctrl]+C to copy only the selected visible cells to the Clipboard.
  4. Select a target cell and press [Ctrl]+V. As you can see in Figure F, Excel copied only the subtotaling rows. (The pasted set doesn't skip row numbers indicating hidden records.)

Figure E

After hiding the details records, you can copy the subtotaling rows.

Figure F

Copying just the visible records is the trick to copying only the subtotaling rows.

8: Format subtotals

You might have noticed that the subtotaling results get a bit lost in the details. The easiest way to distinguish those subtotals is to format them, but doing so to each one individually would be tedious and inefficient. Using the same feature used in #7, you can quickly format only subtotals, as follows:

  1. Press 2 in the Subtotal pane to the left to hide the detail records.
  2. Press [Alt]+; to select only the visible cells.
  3. Apply the format. (Use can also apply styles this way.)
  4. Press 3 in the side pane to review all the records. As you can see in Figure G, only the subtotaling rows reflect the formats you applied in step 3.

Figure G

Select the visible cells before applying formats and styles to subtotaling rows.

9: Subtotaling with tables

You can't use the Subtotal feature with Excel tables (the grownup version of lists) or with lists. But you can still get the information you want by converting the table to an ordinary range. To do so, right-click the table, choose Table from the resulting shortcut menu, select Convert To Range, and then click Yes to confirm this action. In Excel 2003, choose Lists from the Data menu and then select Convert To Range. At this point, you can apply the Subtotal feature as you normally would. When you're done, convert the range to a table.

10: Understanding SUBTOTAL()

When you use the Subtotal feature to insert a subtotaling row, Excel inserts a SUBTOTAL() function. You can insert this function yourself, but the Subtotal feature is more efficient when working with large amounts of data. Understanding this function is important to getting the right results quickly. SUBTOTAL() has two arguments: a constant that determines the mathematical evaluation and the range it evaluates. Table A lists the constants you need to know.

Table A

Constants for the SUBTOTAL() function

11: Count items in a group

Most subtotals evaluate values based on categories in another column -- when the value in column A changes, evaluate the corresponding value in column B. Some users don't realize that they can use this feature on a single column. Figure H shows the subtotal settings for the results shown in Figure I. All the settings point to the same column, Category.

Figure H

Set both data settings to the same column -- the Category column.

Figure I

Counting the records in a group requires evaluating the same column.

12: Remove text

When you insert a subtotaling row, Excel uses the word Total, Average, and so on, in a descriptive label. Deleting that label is as easy as deleting any other string. Here's the tip: After using the Subtotal feature, you can use the data as you would any other data. To remove Average from the subtotaling row labels, use the Find And Replace feature, as follows:

  1. Select the column by clicking the column's header (in this case, that's column G).
  2. Press [Ctrl]+H to open the Find And Replace dialog.
  3. Enter Average in the Find What control.
  4. Leave the Replace With control blank, as shown in Figure J.
  5. Click Replace All (or use Replace and Find Next to view each instance).
  6. Click OK to confirm and then click Close. Figure K shows the subtotal labels without Average.

Figure J

To replace a string with nothing, leave the Replace With control blank.

Figure K

Use other features with subtotaled results, just as you would other data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
jbenton
jbenton

The hidden values thing in table A can prove very useful I used to memorise all the options and their values till I realised 1. They're in alphabetical order 2. the first six are the same as the options available when you right click the status bar (aka bottom border of Excel window) NB I think item 7 uses Go To Special, not Paste Special

jbenton
jbenton

NB this does NOT work when rows are hidden as part of an autofilter

ssharkins
ssharkins

I actually wrote that one up in one of my 10 things I learned from readers pieces. :) Thanks for chiming in!

Editor's Picks