How to change an Excel conditional format on the fly
December 13, 2019
Conditional formatting is a flexible and powerful tool in Microsoft Excel, but you can’t change a condition without modifying the underlying rule. Don’t let that stop you—use an input cell. Here’s how.
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.
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.)
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.
Note the category range—you’ll need it in a bit. For our demonstration data, it’s $G$2:$G$46.
Select an out-of-the-way spot for the list. Select B2 on the sheet named Lists.
Click the Data tab and then click Advanced in the Sort and Filter group to display the Advanced Filter dialog.
Under the Action settings, click Copy To Another Location.
For the List range, enter $G$2:$G$46 or Table1[Category] if you’re using a Table object. Or click the Sheet tab and manually highlight the values.
In the Copy To range, enter the anchor cell for the unique list. In this case, it’s Lists!$B$2.
Click the Unique Records Only option (Figure B), and then click OK.
Sort the resulting list if you want the dropdown to display an alphabetized list. Give the list a header cell and format it as a Table Object (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.
Select G1. Then click the Data tab and choose Data Validation from the Data Validation dropdown in the Data Tools group.
From the Allow dropdown in the resulting dialog, choose List.
In the source dialog, identify the category list (Figure D), and click OK. The resulting control is shown in 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:
The last step is to add this conditional format expression as follows.
Select the data set: B5:G49. If you don’t want to highlight the entire row, highlight the column(s) you want to highlight.
On the Home tab, click Conditional Formatting in the Styles group and choose New Rule from the dropdown list.
In the top pane, select Use A Formula To Determine Which Cells To Format.
In the lower pane, enter this formula: AND($D5<=$F5,$G5=$G$1). (The period is grammatical, not part of the formula.)
Click Format. In the next pane, click the Fill tab, choose a color, and click OK. Figure F shows the formula and the format. Click OK to return to the worksheet.
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.
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@example.com.
Microsoft Weekly Newsletter
Be your company’s Microsoft insider with the help of these Windows and Office tutorials and our experts’ analyses of Microsoft’s enterprise products. Delivered Mondays and Wednesdays
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.