Software

Quickly format subtotaling rows to increase your productivity

Use this easy selection trick when you apply formatting to subtotaling cells or rows to save yourself some time.

Excel's subtotaling feature is a great tool for inserting subtotaling formulas in into your data. The down side is that the subtotal rows are often difficult to discern from the rest of the data. Formatting just the subtotaling rows, one by one, could be a tedious task. Fortunately, you won't have to do that - the tool you need for quick formatting is already in place. I'm talking about the outline symbols Excel displays when you add subtotals. Using the outline symbols, you can quickly format all the subtotaling cells with just a few clicks.

Before I can show you how to put those outline symbols to quick formatting use, you need some subtotaling rows. The example sheet shown above uses data from the Products table in the Northwind database. You can use any data range you like, but in this example, I'll display the average of each category as follows:

  1. First, sort the data range appropriately. In this case, I sorted by the Category column because I want to average the price of the products in specific categories.
  2. Once the data's sorted, select any cell in the data range.
  3. Click the Data tab and then click Subtotal in the Outline group. In Excel 2003, Subtotal is on the Data menu.
  4. In the resulting dialog box, choose Category from the At Each Change In dropdown.
  5. Select Average from the Use Function dropdown.
  6. Check the Unit Price field in Add SubTotal To.
  7. Click OK, and Excel will display the average unit price for each category (see the first sheet above).

At this point, you have subtotaling rows, but the information gets lost. Formatting is the answer. In this case, I added a background color and border to the subtotal in the Unit Price column (see above), as follows:

  1. Select the column that contains the values or cells you want to format. In this case, that's C5:C66 - select the whole range, including the detail values.
  2. Collapse the outline by clicking the 2 symbol in the outline pane. Doing so will hide the detail records and display only the subtotaling rows. Don't change the selection.
  3. Press [F5] to display the Go To dialog.
  4. Click Special (at the bottom of the dialog).
  5. Click the Visible Cells Only option, and click OK.

At this point, you can apply the appropriate formatting. In the case of this example, I chose a color from the Fill Color dropdown and a border from the Outside Borders dropdown; both are in the Font group on the Home tab. To see the results, click the "3" symbol to expand the records. The subtotaling cells in column C are easy to spot!

I showed you a similar trick earlier in the summer, but I used it to copy subtotals. This trick isn't a copying technique, it's a selection technique. Anytime you want to do anything to subtotal cells or rows, use this selection method first. That way you can act on all of the subtotaling cells at the same time.

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.

2 comments
alecpjd
alecpjd

Nice tip to select visible cells to format, but the formatting is lost when the sub-totals are cleared. Is it possible to format sub-totals automatically (without using conditional formatting)?

jbenton
jbenton

this can be achieved quickly by using the keyboard shortcut alt-;

Editor's Picks