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 Format that Checks for Data Type
Conditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip examines how to check if either text or numbers have been entered into a cell.

Conditional Formats that Distinguish Blanks and Zeroes
Conditional formatting is a great tool. You may need to use this tool to tell the difference between cells that are empty or cells that contain zero values. Here's how to approach the task.

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 in PivotTables
Conditional formatting is very powerful, and you can use it to dynamically adjust how your data looks. Excel allows you to apply conditional formatting in a PivotTable, but the outcome of that application may not be satisfactory when the table is refreshed.

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
Conditional formatting is a great way to make sure that your information looks a particular way, even if the information changes. If you want to determine the color of cells whose color is based on a conditional format, then you are in for quite a bit of work.

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
Conditional formatting provides the opportunity to get very creative with your formatting. One such creative urge can be satisfied by formatting an input cell so that it shows up in a desired color until someone enters something in it. This can make it easier for users to see where they need to enter information.

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
If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with conditional formatting. This tip shows just how easy such shading can be.

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.