Conditional Formatting

Tips, Tricks, and Answers

The following articles are available for the 'Conditional Formatting' topic. Click the article's title (shown in bold) to see the associated article.

Adding Ordinal Notation to Dates
If you use dates in your worksheet, you may want to add an ordinal indicator to the day of the month. This tip shows the best ways to achieve this result.

Applying Conditional Formatting to Multiple Worksheets
If you just switched to Excel 2007, you may have noticed some differences in how the program handles applying conditional formatting to multiple worksheets. Here's some ways you can apply the formatting you need to multiple sheets.

Changing Coordinate Colors
Want to change the colors used by Excel for the column letters and row numbers on a worksheet? You can't do it directly in Excel; instead you need to make an adjustment in Windows itself.

Changing Font Face and Size Conditionally
Conditional formatting does not allow you to change the typeface and font size used in a cell. You can write your own macro to do the formatting change, however.

Conditional Formats that Distinguish Blanks and Zeroes
Conditional formatting is a great boon to Excel users. One use is that you can use it to highlight cells that contain zero values. However, if a cell is blank, Excel also considers that cell to be equal to zero. This tip explains how you should perform your conditional test to get only those cells containing an actual zero value.

Conditional Formatting
A few ways to use conditional formatting on cells in your Excel worksheet.

Conditional Formatting Based on Date Proximity
Conditional formatting can be used to draw your attention to certain cells based on what is within those cells. This tip explains how you can use conditional formatting to change the color of cells based upon how close a particular date is to today's date.

Conditional Formatting for Errant Phone Numbers
If you've got a bunch of phone numbers in a worksheet, you may want to check if they are all within certain bounds. You can use the Conditional Formatting feature of Excel to assist in this checking, as described in this tip.

Conditional Formatting with Data Imported from Access
If the information you import from Access into Excel isn't interpreted just right, it can have consequences on your conditional formatting. This tip explains those consequences and how you can correct for any potential problems.

Conditionally Formatting for Multiple Date Comparisons
The Conditional Formatting capabilities of Excel are very powerful. Getting them to behave exactly as you expect can be a bit tricky, however. This tip describes one common pitfall when setting conditions, and it explains how to get around it.

Copying Conditional Formatting
Conditional formatting is a great feature in Excel. Here's how you can copy conditional formats from one cell to another in a worksheet.

Counting Colors from Conditional Formats
The background of a cell can be colored based either on direct formatting or on conditional formatting. Counting the number of cells that are directly formatted in a particular manner is not that difficult when using a macro. Counting the same cells if they are conditionally formatted is much more difficult, as described in this tip.

Detecting Errors in Conditional Formatting Formulas
If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to find those errors, but the easiest method involves the use of a short macro.

Highlighting Cells Containing Specific Text
If you want to highlight cells that contain certain characters, you can use the conditional formatting features of Excel to help out. Here's how to set up the proper conditional format.

Highlighting Values in a Cell
Excel allows you, through normal editing, to overwrite formulas in a cell with explicit values. Wouldn't it be nice to know where such overwriting occurred, however? You can find out by applying some of the techniques discussed in this tip.

More than Three Conditional Formats
Conditional formatting is a powerful feature of Excel, but it has traditionally been limited to three explicit formats. This tip examines ways around this limitation, along with a look at how the limitation was entirely removed in Excel 2007.

Protecting Conditional Formatting
If you have conditional formatting applied in a worksheet, the formulas in those formats may not be as secure as you would like. This tip explains the problem and shows you what you can do to guard the formulas.

Removing Conditional Formats, but Not the Effects
Conditional formatting is very powerful, but at some point you may want to make the formatting "unconditional." In other words, you might want to remove the conditions on which the formatting is based, but still keep the formatting that you see for those cells. This can only be done via a macro, as described in this tip.

Shading a Cell Until Something is Entered
Using conditional formatting, you can shade a particular cell (or cells) until a value is entered in the cell. This is a handy way for users to tell when something needs to be entered in a particular cell.

Shading Based on Odds and Evens
You can use conditional formatting to add shading to various cells in your worksheet. This tip shows how you can shade cells based on whether a value is odd or even.

Shading Rows with Conditional Formatting
Conditional formatting can be used to shade every other row in a data table. This is handy for those times when it may not be that easy to follow the lines on a printout.

Sorting or Filtering by Conditional Format Results
Conditional formatting is a great feature in Excel. Unfortunately, you can't sort or filter by the results of that formatting. Here's why.

Understanding Color and Conditional Formatting Codes
Using custom formatting, you can format a cell so that different types of values display exactly as you want them to. This tip explains how to create the custom formats and the pertinent formatting codes that are available.

Understanding Conditional Formatting Conditions
An explanation of conditional formatting and how to employ it in your Excel worksheet.

Using Multiple Test Conditions
When creating conditional formats, you are not limited to only one condition. You can create up to three conditions, all for the same cell or range of cells.