Excel Tips on Conditional Formatting

Excel can be very tricky to use. I learned a lot by taking an Excel training class in Buffalo NY.. However Allen Wyatt provides these great tips on his website for conditional formatting.

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Font Face and Size Conditionally Conditional formatting does not allow you to change the typeface or font size used in a cell. Instead, you need to use a macro to accomplish your formatting goals. It’s easier to do than you think it is by using the information in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Conditional Formatting A few ways to use conditional formatting on cells in your Excel worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Conditional Formatting Based on Date Proximity Creating conditional formatting to turn cells different colors depending on the values entered. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Copying Conditional Formatting Conditional formatting is a very powerful feature in Excel. If you want to copy conditional formatting from one cell to a group of another cells, you might be at a loss as to how to proceed. Copying the conditional formats is actually easier than you think, as this tip describes. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Highlighting Cells Containing Specific Text How to format cells containing specific text differently, so that they stand out from the rest of your worksheet. A great approach that makes certain cells easier to view. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Shading Based on Odds and Evens Given a series of values in a range of cells, you may want to shade the cells based on whether the values are odd or even. Using Conditional Formatting you can make short work out of such a need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Sorting or Filtering by Conditional Format Results Conditional formatting is a powerful feature of Excel. You cannot directly sort or filter based on the results of this special type of formatting, however. Using the results of a conditional formatting test can be achieved, but the easiest way is to change the structure of your worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Conditional Formatting Conditions An explanation of conditional formatting and how to employ it in your Excel worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using AutoFormat The AutoFormat tool can save a great deal of time when formatting tables of data. This tip explains how to use the tool to get the best results for your data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003