Feb
1

Video: Conditional formatting based on another cell

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.
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.

12 Responses to "Video: Conditional formatting based on another cell"

  1. Imran Masud says:

    Hey, Irina. Its my pleasure for i have found this awesome blog site. I have learnt many tips & trick from here everyday. Thanks a lot for you & your teammates.

  2. Patrick says:

    Very good posts, You guys are helping almost every Managers from different orgs,..

  3. Swaraj says:

    Really very good job guys for post those valuable trick.

  4. Ali Khan Firooz says:

    HI thank you for the great post. I am wondering to know that how keep rows highlighted for certain period? For example: Payment due date is on 30the, of each month and want the rows remain highlighted from 25 to 30 of the month.
    Thank you,

  5. Agant Patil says:

    Hey, Irina, Thank you very much for all these awesome blogs. I have learnt many tips & tricks from here everyday. Thanks a lot for you & your teammates.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard