*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:

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

- Click Ok and here you go.

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.

*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:

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

- Click Ok and here you go.

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.

## 24 responses to "Video: Conditional formatting based on another cell"

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.

Hi Imran,

I really appreciate your kind words, we're happy to help!

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

Thank you very much for your feedback, Patrick! We'll do our best to stay helpful.

Really very good job guys for post those valuable trick.

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,

Hello Ali,

Thank you very much for your feedback. If we understand your task correctly and you have a column with dates, then you can select the rows you want to highlight and create a rule with the following formula:

=DAY($C2)>24

Here column C is the one that contains the dates.

Hi Irina,

Thank for the support! This formula highlights days greater than 24 but includes past dates as well. I want this to work for current month only. I applied this formula "=AND(MONTH(Q9)=TODAY(),DAY(Q9)>24)" but it doesn't works. Can you suggest any revision?

Thank you,

Hi Irina,

Thank you, I have figure it out. Here is the formula if anyone need.

=AND(MONTH(Q9)=MONTH(TODAY()),DAY(Q9)>24)

Thank you,

Ali Khan

Thank you for the update, Ali!

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.

Thank you for taking a minute to share your feedback, Agant, we really appreciate it!

Wonderful site, I'm so glad I found it. I print a morning sheet that I want to highlight select rows which already have a value entered. So I need to do conditional formatting with each row. Is there any fast way to copy paste conditional formatting to Rowe's like you can with common formulas ?

Hello John,

Thank you for your feedback.

You can apply the rule to all your rows and add an absolute reference to the column with values you want to check. For example, you can select the range with all your data and create a rule with a custom formula

=$K2<>""

It will highlight the rows that have a value in column K. If your task is different, please describe it in more detail.

Hi - this site has been SO helpful for me!

Unfortunately, I can't seem to get this to work for my specific use case.

I'm trying to format cells in column R based on results in Column Z, where Column Z contains a vlookup.

When I try the simple formula in Cond. Formatting "$Z9>0" it is returning ALL columns, even those that show as nothing in column Z.

The Vlookup formula in Z is "IFERROR(VLOOKUP(E9, KAM_SFG, 14, FALSE)"

Nothing I do seems to make this work. :(

Hi, happy to hear you find our site helpful!

It looks like you can slightly modify the conditional formatting formula to make it ignore blanks, try this way:

=AND($Z9<>"",$Z9>0)

Hope it helps!

Hi Irina, first of all thanks for halpfull videos, and learniing notes.

I have one question about conditional formatting, I'm interested in how can I find, for exampre 10 greater number from larg excell cell ?

thank you !

Hello Giorgi,

Thanks a lot for your kind feedback!

Could you describe your task in more detail: are you trying to compare numbers to a certain cell and find those that are greater by 10 or more, or are you trying to highlight all cells that are greater than 10?

I'll do my best to assist you.

Hello Irina,

I've solved that task, I was interested about compare numbers to a certain cell and find those that are greater by 10 or more, I find it easily at "conditional formatting"

lots of thanks for you!

Thank you very much for the update, Giorgi, great to hear that you found what you needed!

Feel free to contact me if I can help in any way.

How do I condition a specific cell to highlight red, in response to a random cell within a table on a different page highlighting red as well?

Explanation: I have a table on a separate tab that has cells turning red when certain conditions are met. When these cells go red, I need a specific cell on the main tab to go red as well. When no cells are red, I don't want it highlighted red either.

Thanks in advance for your help!

Hello Spencer,

I'm afraid there is no way to refer to a color as a conditional formatting criterion in Excel. The easiest way to go would be using the same condition as you have in your original rule and applying it to your cell in the main table.

Hi, i am looking for a solution i.e

I have a work sheet. one of the cell have .33 +/-.010. Next to this cell when entered value should highlight as below

If value is between 0.3200-0.3225 should be yellow color

If Value is between 0.3375-0.3400 should be yellow color

If Value is More than 0.3400 should be red color

If Value is Less than 0.3225 should be red color

If Value is between 0.3225 & 0.3375 should be Green color.

Similarly i have multiple cells with values in the same sheet. i am looking for a solution with single formula to highlight similarly.

Thanks in advance.

Regards,

Subbu

Hello, please can you help. I'd like the cell to be red if cells in col G are blank and cells in col E have an invoice number (the invoice number is different in each cell. I did use =AND($G4="",$E4="ARI00004") but can you let me know how to not use specific text please?