How to change an Excel conditional format on the fly

It's easy to highlight a record in Microsoft Excel using conditional formatting--usually, the condition is compared to an existing value in the data set. Is this value larger, smaller, or equal to "this" or "that"? You can highlight the value, part of the record, or the entire record. You can even highlight a different value based on another-both in the data set. What you can't easily do is change the condition. I'll show you how to add an input cell in Excel that's referenced in the conditional formatting rule, which allows you to change a condition on the fly-without modifying the actual rule itself.

To implement this technique, you need data, a unique list of filtering values, a data validation control, and a conditional rule. It might sound complex, but it isn't. I assume you know basic features, such as how to insert rows, create a Table object, sort data, and so on.

I'm using Office 365 on a Windows 10 64-bit system, but you can use earlier versions. You can work with your own data or download the .xlsx and .xls demonstration file. This technique isn't appropriate for the browser edition.

LEARN MORE: Office 365 Consumer pricing and features

How to set it up

The simple data set shown in Figure A is formatted as a Table object, and it stores 45 rows of product information (that I copied from the Access Northwind database). I used a Table object because I want the entire technique to be as dynamic as possible, but you don't have to use a Table object. (To create a Table, click anywhere inside the data set, click the Insert tab, click Table in the Tables group, and click OK.)

Figure A

Let's suppose you want to highlight products with a Units in Stock value that is less than or equal to the product's Reorder Level value; however, you don't want to view them all--you want to view the products that meet this condition by categories. In other words, you want to see all the beverages or condiments that need to be ordered.

Built-in filters can't help, and neither can a conditional format, by itself. But you can combine a list control with a conditional format to create a conditional format that's more conditional that it otherwise could be.

How to organize the list

The first thing we need is a control that displays the categories in a dropdown. The selected value will be stored in the underlying cell, and the conditional rule will refer to that cell. Magic (almost)! The list should be a set of unique values. You could enter the category list manually (it's easy enough to glean all of them from our simple data set), or, you can let Excel do it for you, ensuring that you didn't miss one. To do so, you'll use the advanced filter feature. At this point, you can't copy an advanced filtered set to another sheet. The feature only copies to the active sheet (kind of). If you start with the destination sheet instead of the source sheet, it works just fine. That will make sense in a minute.  You can copy the list anywhere you like but choose an out-of-the-way spot. I chose a sheet specifically dedicated to lists of this sort, and it was named appropriately: Lists. We'll copy a unique set of categories from the data set to the Lists sheet as follows.

Figure B

Figure C

The next step is to embed a list control that will display the unique set of categories you just created.

How to control the list

We need a list control that will allow users to select a specific category, and the most logical spot is above the Category header. To that end, insert a few rows above the data set. (Select a row, right-click the selection, and choose Insert. I selected three rows.) With empty rows above the data set, you're ready to add the list control as follows.

Figure D

Figure E

When you select a category from the Data Validation list, that value is stored in cell G1. You'll need to reference this cell in the conditional format rule, which is coming up next.

You might remember I mentioned that the data set and the list were both Table objects--here's why: If you update the list of unique categories, the Data Validation list will update automatically. It's not mandatory for the technique to work, but it certainly is a nice bonus. Now, on to the conditional format rule.

How to use conditional format

The basic requirement is simple: We want to highlight records when they need to be ordered. We can write that as a simple expression:

Units in Stock <= Reorder Level

When the amount in stock is less than or equal to the reorder threshold amount, the expression is true; otherwise, the expression is false. But wait--there's more! We also need to consider the category. We want to highlight only those records where this expression is true, and the record's category value matches the one in G1. We can write this as another true or false expression:

Category=selected value in data validation control

Using the AND operator, we can combine the two conditions to create an expression that returns true only when both conditions are true:

=AND($D5<=$F5,$G5=$G$1)

The last step is to add this conditional format expression as follows.

Figure F

As you can see in Figure G, the selection in the data validation control is Condiments, and the conditional rule highlights two records: Aniseed Syrup and Chang need to be reordered. Use the data validation control to change the category value and watch the rule highlight different (or perhaps no) records.

Figure G

On the fly

Being able to change a value that's evaluated by a conditional formatting rule gives you a lot of flexibility. If you implement this technique, please share your experience in the comments section.

Send me your question about Microsoft Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at [email protected]

Also see

Affiliate disclosure: When you click through from our site or one of our downloads to a retailer or vendor and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay.