You may need to format cells in Excel based on a set of conditions, which is when conditional formatting is useful. You can also use the feature as a shortcut to highlight improper data using certain criteria. The following articles explain what conditional formatting in Excel can do and how to utilize it.

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.

   Alerts About Approaching Due Dates
You may use Excel to track due dates for a variety of purposes. As a due date approaches, you may want that fact drawn to your attention, in some fashion. This tip examines different approaches you can take to getting the alert.

   Answering Questions in Order
It is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions in a specific order, you can use both data validation and conditional formatting, as described in this tip.

   Applying Conditional Formatting to Multiple Worksheets
If you just updated your copy of Excel, 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.

   Automatic Lines for Dividing Lists
When preparing a report for others to use, it is not unusual to add a horizontal line between major sections of the report. Depending on your data, you can make it so that Excel adds the line automatically.

   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.

   Changing Shading when a Column Value Changes
If you have a data table in a worksheet, and you want to shade various rows based on whatever is in the first column, then you can use Excel's conditional formatting capabilities. There are a couple of ways you can choose to do so, however.

   Coloring Identical Company Names
Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the duplicates, as discussed in this tip.

   Comparing Dollar Values in a Conditional Formatting Rule
Comparing values (like is done in conditional formatting rules) can yield some crazy results at times. This tip looks at three ways that you can account for a lower precision in your comparisons than what is natively done by Excel.

   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 for Odd and Even Columns
Setting up conditional formatting can be challenging under some circumstances, but once set it can work great. Unless, of course, you start adding data to what you want analyzed in the formatting formulas and you want them automatically handled. This tip looks at a way you can get such automatic analysis.

   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 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
Conditional formatting can be used to draw attention to all sorts of data based upon the criteria you specify. Here's how you can use conditional formatting to highlight which phone numbers aren't within the parameters you need.

   Conditional Formatting Not Reliably Working
Conditional Formatting can be very helpful in calling attention to cells that meet or fail certain criteria. In an actively edited worksheet, however, it is relatively easy to have Conditional Formatting get out of whack and not work as you would expect.

   Conditional Page Breaks
Need to have your worksheet printout start on a new page every time a value in a column changes? There are a couple of ways you can accomplish the task, as described in this tip.

   Conditionally Formatting an Entire Row
Need to conditionally highlight an entire row based on the contents of a single cell in each row? This tip explains how you can set up the conditional format that will do the trick.

   Conditionally Formatting Cells Containing Dates
Need to know if a particular cell contains a date value? Excel doesn't have a worksheet function to determine this information, but here are some easy workarounds you can apply.

   Conditionally Formatting for a Pattern
Conditional formatting is a great tool you can use to customzie your worksheets. When you want to test whether a value in a cell matches a rather complex pattern, your testing formula can quickly get rather long. Here's an example showing a pattern that requires the matching of only three conditions.

   Conditionally Formatting for Multiple Date Comparisons
When you compare dates in a conditional formatting rule, you need to be careful how you put your comparisons together. Do it wrong, and you won't get the result you expect. Here's how to do it right.

   Conditionally Formatting Non-Integers
The conditional formatting capabilities of Excel are very helpful when you want to call attention to different values according to criteria you specify. The hardest part of using conditional formatting is identifying which condition should be used. This tip examines the different ways you can specify a condition that differentiates between an integer and non-integer in a cell.

   Conditionally Highlighting a Milestone Cell
Sometimes it can be difficult to figure out the exact formula you should use to properly apply a conditional formatting rule. In this tip you discover a good number of formulas that will work in a conditional formatting rule to display when a particular threshold value is exceeded.

   Conditionally Highlighting Cells Containing Formulas
Excel's conditional formatting feature allows you to create formats that are based on a wide variety of criteria. If you want to highlight cells containing formulas, you can use conditional formatting in conjunction with a short macro.

   Conditionally Making a Sound
Need to have a sound played if a certain condition is met? It is rather easy to do if you use a user-defined function to actually create the sound.

   Controlling Data Entry in a Cell
Sometimes you want whatever is displayed in one cell to control what is displayed in a different cell. This tip looks at just such a scenario and provides two approaches for getting the display just the way it is needed.

   Converting Conditional Formatting to Regular Formatting
Conditional formatting allows you to change how information is displayed based on rules you define. What if you want to remove those rules, however, and keep the formatting intact? This tip addresses the issue.

   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.

   Defining a Single Conditional Formatting Condition
Conditional formatting is a powerful tool you can use to dynamically adjust the formatting of your worksheet. This tip explains how you can easily defined a single condition by which formatting can be applied.

   Deleting Conditional Formatting
After you've applied a conditional format to a cell, you may have a need to later delete that format so that the cell is treated (and formatted) normally by Excel. Unfortunately, how you delete conditional formats may not be immediately obvious to you.

   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.

   Diagonal Borders in a Conditional Format
Conditional formatting is a great tool for changing how your data looks based on the data itself. Excel won't allow you to use diagonal borders in your conditional formats, however. Here are some ways you can deal with this limitation.

   Displaying Negative Percentages in Red
Excel includes quite a few different formats you can use for the information in a worksheet. One format that isn't as easy to set up is for negative percentages. This tip explains the two ways you can format those percentages so they appear red, just like you want.

   Finding Cells that Use Conditional Formatting
Conditional Formatting is a great boon to effectively displaying the information in your worksheets. If you want to easily find any cells that use conditional formatting, you can use Excel's native searching capabilities.

   Finding Duplicate Data Across Worksheets
Conditional formatting can be easily set up to check data on the current worksheet. If you want to check data on the current worksheet against data on differing worksheets, it takes a bit more work.

   Handling Negative Numbers in a Complex Custom Format
Custom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex formats that contain multiple conditions, as discussed in this tip.

   Highlighting After-Hours Times
The Conditional Formatting capabilities of Excel are powerful. This tip shows how you can use a simple approach to Conditional Formatting to highlight times that are outside of a desired range.

   Highlighting an Entire Row for the Current Date
It is easy to apply conditional formatting to a cell. What if you want an entire row to be formatted, however, based on the value of a single cell within that row? This tip explains the trick to make this sort of formatting possible.

   Highlighting Cells Containing both Letters and Numbers
Conditional formatting is a great tool for changing the format of cells based on whether certain conditions (rules) are true or not. Determining a formula to use as the basis of your rule can be challenging, such as determining if a cell value contains both letters and numbers. This tip examines some possible solutions.

   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 Greater Than Average Dry Durations
If you need to find whether the duration between two dates is greater than the average of all durations, you'll find the ideas in this tip helpful. Not only can you use conditional formatting, but you can also use a macro to do the formatting.

   Highlighting Values that are 10x a Baseline Value
Sometimes the hardest part of getting your conditional formatting rules to work properly is figuring out the proper way to put your formula together. This tip looks at a way to set an acceptable minimum value for a baseline used in comparisons.

   Moving Cell Borders when Sorting
Sort your data and you may be surprised at what Excel does to your formatting. (Some formatting may be moved in the sort and some may not be.) Here's an easy way to make sure that the formatting you apply is moved whenever you sort.

   Noting Inactivity within a Timeframe
There are many times when you are creating a worksheet that you need to analyze dates within that worksheet. Once such instance may be to figure out whether a certain date is outside some timeframe that you specify. This tip takes a look at how to accomplish such an analysis feat.

   Protecting Conditional Formatting
When you apply conditional formatting to the cells in your worksheet, those rules can seem a bit fragile at times. For instance, they can be overwritten or messed up by copying and pasting information. This tip looks at why that occurs and offers some possible remedies.

   Protecting Your Conditional Formatting Rules
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.

   Returning a Value Based on Text Color
Conditional formatting rules can be used to adjust the way in which information is displayed in Excel, such as the text color. If you want to use a formula to "read" that text color in some way, you are out of luck, however. This tip explains how you can work around the problem.

   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 Conditional Formats Properly
Conditional formatting can be a great tool to get your data looking just the way you need. However, when you sort data that is conditionally formatted, the result may not be what you expect. Here's why.

   Stopping a Conditional Formatting Rule from Breaking into Smaller Ranges
When you paste information into a row that is conditionally formatted, you may end up messing up the rules applied to surrounding rows. If you don't want this to happen, Excel provides a number of ways you can paste your information.

   Turning a Cell Red when a Threshold is Exceeded
Excel provides a great conditional formatting capability that allows you to change how a cell appears based on critiera you specify. It can be a bit confusing, however, to set up conditional formatting rules when dates or times are involved. This tip looks at how you can successfully set up a rule based on a number of accrued hours when compared to a threshold.

   Workbook Loses All Conditional Formatting
Workbooks created in very old versions of Excel can, at times, have issues when opened in later versions of the program. This tip looks at a couple of ways that you can bring the workbook out of the past and into a format that can be used with more recent versions.

   Working with Multiple Conditions
When you apply conditional formatting, you are not limited to using a single condition. Indeed, you can set up multiple conditions, as described in this tip.