Conditional formatting is a flexible and powerful tool in Microsoft Excel, but you cannot change a condition without changing the underlying rule. Don’t be stopped – use an input cell. Here’s how.
Image: Mariakray, iStock / Getty Images
It is easy to mark a record in Microsoft Excel using conditional formatting – usually the condition is compared to an existing value in the dataset. Is this value greater, smaller or equal to “this” or “that”? You can mark the value, part of the record or the entire record. You can even highlight another value based on another – both in the dataset. What you can’t easily do is change the situation. I will show you how to add an input cell in Excel referenced in the conditional formatting rule, which allows you to change a condition directly – without changing the actual rule itself.
To implement this technique, you need data, a unique list of filter values, a data validation check, and a conditional rule. It may sound complicated, but it is not. I assume you know basic functions, such as how to insert rows, create a table object, sort data, and so on.
I use 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 is not suitable for the browser version.
LEARN MORE: Office 365 Consumer prices and functions
How to set it up
The simple data set displayed in Figure A 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 entire technique to be as dynamic as possible, but you don’t have to use a Table object. (To create a table, click anywhere in the dataset, click the Insert tab, click Table in the Tables group, and then click OK.)
We create a dynamic conditional format to highlight specific records in this data set.
Let us assume that you want to mark products with Units in inventory value that is less than or equal to the order level value of the product; however, you do not want to view them all – you want to view the products that meet this condition by category. In other words, you want to see all the drinks or herbs that must be ordered.
Built-in filters cannot help and a conditional format in itself cannot either. But you can combine a list control with a conditional format to create a conditional format that is more conditional than usual.
How to organize the list
The first thing we need is a control that displays the categories in a drop-down list. The selected value is stored in the underlying cell and the conditional line refers to that cell. Magic (almost)!
The list must be a series of unique values. You can enter the category list manually (it is easy enough to collect them all from our simple data set), or you can have Excel do it for you so that you don’t miss it. Use the advanced filter function for this. You cannot currently copy an advanced filtered set to another sheet. The function only copies to the active sheet (type of). If you start with the target sheet instead of the source sheet, it works fine. That makes sense.
You can copy the list to any location, but choose a remote location. I chose a magazine specifically devoted to these types of lists, and it was given the correct name: Lists. We copy a unique set of categories from the dataset to the list sheet as follows.
Note the category range – you need it a bit. For our demonstration data, this is $ G $ 2: $ G $ 46.
Select a remote location 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 box.
Click on Copy to another location under the action settings.
For the list range, enter $ G $ 2: $ G $ 46 or Table1 (Category) if you use a Table object. Or click on the Sheet tab and mark the values manually.
In the Copy to range, enter the anchor cell for the unique list. In this case it is Lists! $ B $ 2.
Click on the Unique records only option (Figure B) and then click OK.
Sort the resulting list if you want the drop-down list to display an alphabetical list. Give the list a header cell and format it as a table object (Figure C).
Identify the original list and the unique list location.
The advanced filtering function of Excel makes a unique list of category values.
The next step is to embed a list control that displays the unique set of categories that you have just created.
Manage the list
We need a list control that allows users to select a specific category, and the most logical place is above the category heading. To do this, 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 dataset, you are 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 list in the Data Tools group.
In the dialog box that appears, choose List from the Allow drop-down list.
Find the category list in the source dialog (Figure D) and click OK. The resulting check is shown in Figure E.
Create a data validation control filled with the category values.
This list control displays a unique list of categories.
When you select a category in the Data Validation list, that value is stored in cell G1. You must refer to this cell in the conditional formatting rule that follows.
You may remember that I said the dataset and the list were both table objects. This is why: If you update the list of unique categories, the data validation list is automatically updated. It is not mandatory for the technique to work, but it is certainly a nice bonus. Now continue with the conditional formatting rule.
Use conditional formatting
The basic requirement is simple: we want to mark records when they have to be ordered. We can write that as a simple expression:
Units in stock <= Reorder Level
If the number in stock is less than or equal to the amount of the reorder threshold, the expression is true; otherwise the expression is false. But wait – there is more! We must also consider the category. We only want to mark those records where this expression is true and the category value of the record matches that in G1. We can write this as another true or false expression:
Category = selected value in data validation check
With 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 final step is to add this conditional formatting expression as follows.
Select the data set: B5: G49. If you do not want to mark the entire row, mark the column (s) that you want to mark.
On the Home tab, click Conditional Formatting in the Styles group and choose New Rule from the drop-down list.
In the upper pane, select Use a formula to determine which cells you want to format.
Enter this formula in the lower panel: AND ($ D5 <= $ F5, $ G5 = $ G $ 1). (The period is grammatical and is 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 format. Click OK to return to the worksheet.
Set the conditional format to be applied when the expression returns true.
As you can see in Figure G, the selection in the data validation management is Spices, and the conditional line marks two records: anise syrup and Chang must be re-ordered. Use the data validation to change the category value and see how the line marks other (or perhaps no) records.
The conditional format marks products per category that must be re-ordered.
On the fly
If you can change a value that is evaluated by a conditional formatting rule, you get a lot of flexibility. If you implement this technique, share your experience in the comment section.
Send me your question about Microsoft Office
I answer questions from readers when I can, but there is no guarantee. Do not send files unless requested; initial requests for help arriving with attached files are deleted unread. You can send screen shots of your data to clarify your question. Be as specific as possible when contacting me. For example: “Solve problems with my workbook and solve the problem” will probably not get an answer, but “Can you tell me why this formula does not produce the expected results?” power. State the app and version that you are using. I do not receive any reimbursement from TechRepublic for my time or expertise in helping readers, nor do I request reimbursement from readers who I help. You can contact me via [email protected]
Weekly newsletter from Microsoft
Be Microsoft’s insider for your business with the help of these Windows and Office tutorials and the analyzes of our experts on Microsoft business products.
Delivered on Monday and Wednesday
Disclosure of Affiliate Parties: When you click through from our site or one of our downloads to a retailer or vendor and purchase a product or service, we may earn partner commissions. This helps support our work, but it doesn’t affect what we cover or how, and it doesn’t affect the price you pay.