Excel.Tips.Net ExcelTips (Menu Interface)

Using Multiple Test Conditions

You are not limited to a single test condition when using conditional formatting. Excel allows you to specify up to three conditions in the Conditional Formatting dialog box. To set multiple conditions, all you need to do is click on the Add button after you are done with each condition. Excel then expands the dialog box and allows you to enter another test and associated format.

This capability comes in handy when you want to test a cell for multiple specific values. For instance, let's say you wanted to apply different formatting when a particular cell's value was equal to either 200, 350, or 500. Excel makes this easy, simply by using multiple conditions.

If you decide to define multiple conditions, each condition is independent of the other. If you want more complex conditions, using Boolean operators such as AND or OR, then you should set up a formula for your conditional test, as described in other ExcelTips.

At some time while you are developing your conditional tests, you may want to delete one of the conditions. Excel makes this easy. All you need to do is click on the Delete button in the Conditional Formatting dialog box. If you have multiple conditions defined, Excel displays the Delete Conditional Format dialog box. Select which of your conditions you want deleted, and then click on the OK button. You can then continue to work in the Conditional Formatting dialog box, as desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2796) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Ajith Nuwan Rathnaweera    11 Sep 2012, 13:20
Results of two subjects are tabulated under two columns. To pass the exam, students have to score more than 40% from each subject. How do you test this condition and display the result as "PASS" OR "FAIL"?
Barry Fitzpatrick    28 May 2012, 12:18
Using the formulas option on each condition you can have multiple conditions, however, you can only have three formatting options.

Also the order of the conditions can be important as the formatting used will be that for the first of the three conditions which are met. E.g. is condition 1 is to show say an orange background if the value is, say, above 500, and condition 2 would show red if the value is above 1000 then then the formatting would be orange for any value above 500 including values of 1000 and above. To make this work properly Conditions 1 & 2 need to be the other way around.
jmj    28 May 2012, 11:59
Conditional formatting is great, BUT...

Everybody should know that sorting a table with conditionally formatted cells will wreak havoc on you table, because conditional formats are linked to the POSITIONS of the cells, and not to the cells themselves!

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.