Conditional formatting is a flexible and powerful tool in Microsoft Excel, but you cannot 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 mark a record in Microsoft Excel using conditional formatting – the condition is usually compared to a value in the data set. Is this value greater, smaller, or equal to ‘this’ or ‘that’? You can highlight the value, part of the record, or the whole record. You can even distinguish a different value based on another – both in the data set. What you cannot easily do is change the state. I’ll show you how to add an input cell in Excel that is referenced in a 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 a unique data and list of filter values, a data validation control, and a conditional rule. It may seem complicated, but it isn’t. I assume you know the basic features, like how to insert rows, create a table object, sort data, etc.
I’m using Microsoft Excel in Office 365 on Windows 10 64-bit, but you can use earlier versions. You can work with your own data or download the .xlsx demo file and . xls. This technique is not suitable for the browser version.
Learn more: Office 365 Consumer pricing and features
How to set it up
The simple data set shown in Figure A It is formatted as a table object, and stores 45 rows of product information (which I copied from the Access Northwind database). I used a Table object because I want the whole technique to be as dynamic as possible, but you don’t have to use a Table object. (To create a table, click anywhere within the data set, click the Insert tab, click Table in the Tables group, and then click OK.)
Suppose you want to highlight products with a Units in Stock value that is less than or equal to the Product Reorder Level value; However, you don’t want to display them all – you want to display products that meet this requirement by categories. In other words, you want to see all the drinks or condiments you need to order.
Neither the built-in filters, nor the conditional formatting per se can help. But you can combine a list control with a conditional format to create a conditional format more Conditionally it otherwise could be.
The first thing we need is a control that displays the categories in a dropdown list. The specified value will be stored in the base cell, and the conditional rule will refer to that cell. Magic (almost)!
The list must be a set of unique values. You can enter the list of categories manually (it’s easy enough to collect them all from our simple data set), or you can let Excel do it for you, ensuring you don’t miss any of them. To do this, you will be using the advanced filtering feature. At this point, you cannot copy an advanced filtered set to another sheet. The feature is only copied to the active sheet (sort of). If you start with the destination sheet instead of the source sheet, it will work fine. That would make sense in a minute.
You can copy the list anywhere you like but choose a place out of the way. I chose a sheet specifically dedicated to lists of this type, and it was aptly named: Lists. We will copy a unique set of categories from the data set to the list sheet as follows.
Note the range of categories – you’ll need it in a bit. For our demo data it’s $2: $46.
Select a location away from the list. Select B2 on the sheet named Lists.
Click the Data tab, then click Advanced in the Sort & Filter group to display the Advanced Filter dialog box.
Under Action Settings, click Copy to another location.
For List Range, enter $G$2:$G$46 or Table1[Category] If you are using a table object. Or click on the Sheet tab and specify the values manually.
In the Copy To range, enter the anchor cell for the unique list. In this case, it’s lists! $B$2.
Click the Only unique records option (Figure B), and then click OK.
Sort the resulting list if you want the dropdown to display an alphabetical list. Give the list a header cell and format it as a table object (Figure C).
The next step is to include a list control that will display the unique set of categories you just created.
We need a list control that allows users to select a specific category, and the most logical point is above the category header. To this end, enter a few rows above the data set. (Select a row, right-click the selection, and choose Insert. You’ve 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 drop-down menu in the Data Tools group.
From the Allow drop-down list in the resulting dialog, choose List.
In the Source dialog, select Categories List (figure d), and click OK. The resulting control appears in Figure E.
When a category is selected from the data validation list, this value is stored in cell G1. You will need to refer to this cell in the conditional formatting rule, which will then appear.
You may remember that I mentioned that the dataset and the list were both table objects – and here’s why: if you update the list of unique classes, the data validation list will be updated automatically. The technique doesn’t have to work, but it’s definitely a nice bonus. Now, let’s move on to the conditional formatting rule.
How to use the conditional form
The prerequisite is simple: we want records to be highlighted when they need to be requested. We can write this as a simple expression:
Units In Stock
When the amount in stock is less than or equal to the reorder limit amount, the expression is true; Otherwise, the expression is wrong. But wait there is more! We also need to consider the category. We want to highlight only those records where this expression is true, And The log class value matches the value in G1. We can write this as another true or false expression:
Class = value specified in the data validation control
Using the AND operator, we can combine the two conditions to create an expression that returns true Just When Both The conditions are correct:
The last step is to add this conditional formatting expression as follows.
Select the data set: B5: G49. If you don’t want the entire row to be highlighted, 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 drop-down list.
At the top, select Use a formula to select the cells to format.
At the bottom, enter this formula: and ($D5 . (The dot is a grammar and not part of the formula.)
Click Format. In the next pane, click on the Fill tab, choose a color, and click OK. shape and Shows the format and format. Click OK to return to the worksheet.
as you see in shape g, the selection in the data validation control is spice, and the conditional highlight two records: anise syrup and chang need to be rearranged. Use the data validation control to change the value of the category and watch the rule highlight different records (or maybe not).
On the fly
The ability to change a value that is evaluated by a conditional formatting rule gives you a great deal 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 is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at email@example.com.