• Highlight Subtotals for Easy Reading

One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.

Assume that you have a list of data with Date, Vendor and Amount columns. After going to Data --> Subtotals and selecting to Sum the Amount for every change in Date, you’d end up with a subtotaled list like this:

Let’s assume that you’d like to see the subtotals in the Amount displayed in bold with a line on the top and bottom.
• Start by highlighting Column C
• Excel 2003: Go to Format --> Conditional Formatting --> Change the dropdown to read “Formula Is”
• Excel 2007 and higher: Go to Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format
• Enter the formula =Right(\$A1,5)=”Total”
• Click Format

At this point you’ll see the familiar “Format Cells” dialog box where you can choose what properties you’d like used to display the data in your cells. On the Font tab select Bold, and on the Borders tab click the top and bottom border of the cell. Once done, click OK until you are back at the spreadsheet.

There are two things that you need to understand to be able to construct effective conditional formats:

1. When constructing formulas, the references are always based from the active cell. In the case of our example we selected column C which meant that the active cell became C1. This is why we looked at \$A1 to see if it contained “Total”. In cell B2 and C2 we’d be looking at \$A2 and so on down the list. The active cell is usually the first cell in your selection, and can be identified as its background is always clear, compared to the other cells in the selection which have shaded backgrounds.
2. All formulas are evaluated to see if they are TRUE or FALSE. So in our example we checked to see if the right 5 characters = “Total”. If so, the conditional format is applied, if not it isn’t. As you can see from the second image this test would have returned false on A8, but returns true on A9.

Conditional formatting is an incredibly useful tool that is not restricted to working with subtotals, and can be applied to anything that lives within the spreadsheet grid, including Pivot Tables. Just a few examples of where I use conditional formats are: Highlighting the largest or smallest numbers in a list, highlighting invalid data that has been entered into a worksheet, colour banding alternate rows to make large spreadsheets easier to read and highlighting row(s) in large data sets.

This tool has existed in Excel for many versions, but went through a large upgrade in Excel 2007. Until then you were limited to using only 3 conditional formats in any cell, but with the release of Excel 2007 this limitation was removed. You can now use as many formats as you wish, with the only limit being the memory in your computer. Other additions in Excel 2007’s conditional formatting toolset include Data Bars, Icon Sets, Heat Maps, overlapping formats and the ability to stop any further formats from being applied once one is triggered.

Used properly, conditional formats are a truly powerful feature in Excel, and well worth exploring if you haven’t done so already.

Download a copy of the sample workbook so you can try it yourself!

This article was originally contributed to CMA Update Magazine - Summer 2009

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

• Recent Forum Posts

Blank worksheets

I have been developing vba code to produce an attendance register for a 600 member organisation.

My workbook has six worksheets:
...

reglarh Yesterday, 05:29 PM

Sorting Alphanumeric Characters using Excel VBA

Hi all! Would appreciate help on writing a sorting algorithm using Excel VBA. I have a data file. Each row is a question. Each column corresponds to a...

NMT Yesterday, 04:45 PM

How do I change "Command text" in "Connection Properties" in a Database query

Record a macro either of you creating such a query in Excel from scratch, or (preferably) of you adjusting the command text in connection properties of...

p45cal Yesterday, 02:06 PM

Query to Custom Function Guidance

Welcome to the forum!

Please post the code, not a picture of it....

AliGW Yesterday, 01:49 PM

Query to Custom Function Guidance

Hi, I am looking for some help with converting my query to a custom function, in order to retrieve all files in a folder, but I am unsure which part of...

scott_od Yesterday, 12:13 PM