by Natalia Sharashova, updated on
See how you can create custom rules to color your data by any conditions.
There is no doubt that conditional formatting is one of the most useful features in Excel. Standard rules let you quickly color the necessary values, but what if you want to format entire rows based on a value in a certain cell? Let me show you how you can use formulas to create any conditional formatting rule you like.
Here is one common task: I want to highlight the rows with a blank ID. Let's begin with the steps for creating a custom rule:
=A2=""
. But that's not all. To make sure the rule is applied row by row, you need to make the reference to the column absolute, so enter a dollar sign before column A:
=$A2=""
If you wanted to always look at this particular cell, then you'd fix the row as well, making it look this way: $A$2=""
Now let's move on and see how we can find those book titles that have 10 or more in column E. I'll go ahead and select the book titles because this is what we want to format, and create a new Conditional Formatting rule that uses a formula. Our condition is going to be similar:
=$E2>=10
Pick the format and save the rule to see how it works.
As you can see, these are simple rules where you can enter any value that interests you. Where the value is, is not important. If it's in a different sheet, then simply make sure you include its name into your reference.
Let's move on to the cases when your condition concerns two different values. For example, you may want to see the orders that have a high priority and over 8 in the quantity field.
To change an existing rule, pick Manage rules under Conditional formatting, find the rule and click Edit. To make sure several conditions are met, use function "AND", then list your criteria in brackets and remember to use quotes for text values:
=AND($D2="High",$E2>8)
If you want to make sure at least one condition is met, then use OR function instead. Replace the function, now it will read: highlight the row if the priority is high or if quantity is over 8.
Here is another function you will appreciate if you work with text values. The task will seem tricky if you want to look at the cells that contain the key word along with anything else. If this is your case, you will need to use the Search function, and here is how it will look. Select the records to color, create a rule, and enter:
=SEARCH("Urgent",$F2)>0
Note that If you enter more than 1, then you'll get the cells that start with this text instead.
You can use practically any formula as a condition for highlighting your data. In one of our previous videos, we covered how to identify duplicates with the help of Conditional Formatting, and you can find some more great formula examples in our blog post on this topic.
Before you go formatting your table, let me quickly go over some typical mistakes that may not let you get the results you expect.
First of all, remember about the difference between absolute and relative cell references. If you want to check each cell in a column, enter a dollar sign before the name of the column. To keep checking the same row, add the dollar sign before the row number. And to fix the cell reference, in other words, to keep checking the same cell, make sure you have a dollar sign before both: the column and the row.
Then, if you see that your rule is applied just to one row or cell, go back to Manage rules and make sure it applies to the correct range.
When you create a rule, always use the top-left cell of the range with your data for the formula and omit the header row to avoid shifting the results.
As long as you keep these points in mind, Conditional Formatting formulas will do wonders to your data. If you still have troubles getting it to work for you, please share your task in comments, we'll do our best to help you.
Table of contents