Francis Hayes, The Excel Addict Microsoft Excel
Spreadsheet Tips From An Excel Addict

by Francis Hayes (The Excel Addict)

Get more FREE Excel tips every week at

Make Your Subtotal Rows Stand Out
(XL2000, 2002, 2003, 2007)

Once you have sorted your data into groups, you can use Excel's Subtotal command to create a subtotal for each group, automatically insert new rows and display subtotals for each line. However, if you're like me, you probably are puzzled as to why Microsoft wouldn't have designed the subtotal rows with formatting to make them stand out from the surrounding data.

Even with the recent release of Excel 2007 they still haven't listened to user complaints.

By default, the subtotal labels are bolded but the numbers are hard to distinguish from the rest of the data.

Highlight subtotal rows in Excel 2003

If you want to make your subtotal rows really stand out, h
ere are the exact steps I use. Once you do this a couple of times, formatting your subtotal rows will be quick and easy.

1) Once your data has been subtotaled, click the small Outline 2 button in the top left corner of your worksheet to collapse your list and show only the subtotals.

Show just the subtotal rows in Excel 2003

2) Click any cell within your table and press CTRL+Shift+* to select your whole table;

3) Click Alt+; to select only visible cells;

Now you format your subtotal rows (CTRL+1). Change the font to bold and add a Fill color. Adding a top border to these cells will also make you data easier to read.

Make your subtotal rows stand out in Excel 2003

4) Click the Outline 3 button to show all of your data.

Now, doesn't that looks much better?

NOTE: If the Outline Controls are not visible on your screen:
In Excel 2007, click the Office button, click the Excel Options button, click the Advanced tab on the left side of the dialog. Scroll down and find Display Options For This Worksheet and select the check box for Show Outline Symbols if an Outline is Applied. In Excel 2003, go to Tools, Options, click the View tab and place a checkmark in the Outline Symbols option.

"Get EXCITED about Excel again!!"

In my ebook you'll learn 101 secrets that most users haven't discovered, even after using Excel for 10 or 15 years.

Are you ready to experience some Excel 'Aha!s' just like Ivan?

Dear Mr. Hayes, First of all, thank you for the wonderful Aha! experiences I had reading your "101 Secrets of a Microsoft Excel Addict." The Shift+drag columns/rows and the Alt+; shortcuts alone made it worthwhile the modest amount you charge for your e-book. Thank you for being such an Excel Addict. Action does generate inspiration. You have inspired me, I'm having fun with Excel once again. Cheers, Ivan

Spreadsheet Tips From An Excel Addict is available only to subscribers of my email newsletter. If this newsletter was forwarded to you and you would like to get your own copy, please visit

Spreadsheet Tips From An Excel Addict
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630