Video: Conditional formatting based on another cell

Natalia Sharashova by , updated on

See how you can create custom rules to color your data by any conditions.

Conditional formatting based on another cell: video transcript

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.

Apply conditional formatting if another cell is blank

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:

  1. First of all, select the range that you want to highlight, this will save you some steps later. Make sure you start with the top-left record and omit the header row. Converting the range to a Table is a better option if you plan to apply the rule to new entries in the future.
  2. Click on Conditional formatting at the top and choose "New rule". You need the last item: "Use a formula to determine which cells to format".
  3. Now you can enter your custom condition and set the desired format.
    • A fill color offers the quickest way to see our data, so let's pick one and click ok.
    • The formula to find the rows with blanks in column A is =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=""

  4. Click Ok and here you go.

Highlight rows if another cell is blank

Excel conditional formatting based on another cell value

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.

Conditional formatting formula for multiple conditions

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)
Excel-conditional-formatting-formula

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.

Formatting based on another cell text

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.

Things to remember for your custom rules

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.
Excel-conditional-formatting-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