When you want to format a cell based on the value of a different cell, for example to format a report row based on a single column’s value, you can use the conditional formatting feature to create a formatting formula. This post explores the details of formatting a cell or range based on the value in another cell.

## Objective

Here’s an example that will allow us to put this feature into context. Let’s say that you have an invoice listing and your objective is to identify the open invoices. Here is a screenshot of our sample invoice listing:

Since this is Excel, there are many ways to accomplish any given task. One way to identify the open invoices is to simply sort the list by the Status column so that the open invoices appear in a group. Another way is to filter the listing to show only the open invoices. These techniques are fairly straightforward, so, let’s explore another method. We’ll highlight the transaction rows with cell formatting…or, more precisely, a conditional formatting formula.

## Conditional Formatting

Using conditional formatting, it would be pretty easy to highlight just the Status column. It would be simple because the cells we are formatting are the same cells that have the values to evaluate. That is, we would be formatting a cell based on the value within that cell. To perform this, we could simply highlight the Status column, and the use the following Ribbon command:

- Home > Conditional Formatting > Cell Rules > Equal To

In the Equal To dialog box, we could enter the word “Open” and pick the desired formatting and click OK. Excel would then apply the formatting to the cells within the Status column that are equal to Open. While this technique is easy, it does not meet our goal which is to highlight the entire transaction row, not just the Status column.

To highlight the entire transaction row requires us to format a cell based on the value in another cell. That is, we want to format the TID, Date, Status, CustID, and Amount columns based on the value in the Status column. Considering a single cell for a moment, we want to format B7 based on the value in D7. This means that we want to format a cell, B7, based on the value in a different cell, D7. Expanding this to the entire row, we want to format B7:F7 based on the value in D7.

Excel makes it easy for users to format a cell based on the value of that cell, and the built-in conditional formatting rules use this logic. When we want to format a cell based on the value in a different cell, we’ll need to use a formula to define the conditional formatting rule. Fortunately, it is not very difficult to set up such a formatting formula.

Let’s highlight the entire transaction listing (B7:F36) first, and then open the conditional formatting dialog box using the following Ribbon icon:

- Home > Conditional Formatting > New Rule

The New Formatting Rule dialog box has many choices, allowing you to, for example, format a cell based on the value, if it contains a value, the top or bottom ranked values, values that are above or below average, and unique or duplicate values. At the bottom of the list we find the option we need. We want to use a formula to determine which cells to format.

The formatting formula needs to be set up so that it returns a true or false value. If the formula returns true, then the desired formatting is applied. If the formula returns false, the formatting is not applied.

The key thing to understand about writing the formula is that the active cell is the reference point for the formula. Since this concept is absolutely critical, I don’t want to just skip through it, I want to unpack it for a moment.

You want to write the formatting formula as if you are writing it into the active cell and use the appropriate cell references and reference styles, such as absolute, relative, and mixed. If you can visualize the idea that you are writing the formula into the active cell, and the formula will be filled through the selected range, then writing the formula becomes easier. The formula you write will not be used to compute the cell value, rather, it will be used only for formatting.

Let’s assume that when we selected the entire transaction range, B7:F36 that the active cell is B7. When you select a range, there is still a single active cell. Check out the screenshot below to see that the range is selected, yet, B7 remains the active cell:

With this idea in mind, it is easy to write the conditional formatting formula now. We need to write the following formula in the New Formatting Rule dialog, as if we were writing it into cell B7:

=$D7="Open"

This simple comparison formula returns true when D7 is equal to Open, and thus, the desired format will be applied. Let’s take a quick look at the cell reference for a moment. We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Here’s why. Remember we want to pretend that we are writing the formula into the active cell, in this case, B7. If we were only formatting B7, then, we could have used a relative reference D7, or an absolute reference $D$7, or a mixed reference. The reference style wouldn’t matter because the formula was used in a single cell only and was not filled anywhere.

However, the moment that we fill a formula down or to the right, we need to be careful to use the proper cell reference styles. In our case, as the formatting formula is filled right, we don’t want the column reference D to change. That is, for all cells, we want to reference the Status column, column D. To prevent Excel from changing the column reference as the formula is filed to the right, we lock it down with the dollar sign, resulting in $D. As the formatting formula is filled down throughout the selected range, we want to ensure that the row reference is updated accordingly. When formatting B7, we want to look at the Status column within the same row, or D7. However, when the formula is filled down to row 8, we want to format B8 based on the value in D8. Since we want Excel to update the row reference, we’ll leave it relative and not use the dollar sign to lock it down.

Here is a screenshot of the formatting dialog box with the formula:

Once the formula is entered, you simply use the Format button to specify the desired format.

Once the conditional formatting is applied to the range, the resulting worksheet is shown below:

Bam…we got it!

## Other Considerations

In the formula above, we hard-coded the value, “Open” however, we could have easily placed this value into a cell, and then referenced the cell either by name or A1-style notation. In addition, besides just determining if the cell value is equal to a value, other comparison operators are supported, for example, greater than (>) and less than (<). We could have highlighted all rows where the value is greater than $5,000 by using the following comparison formula:

=$F7>5000

Since the formatting is applied based on a formula, we can get very creative and use worksheet functions. We could format alternating worksheet rows by using the MOD and ROW functions. Or, we could highlight old transactions by computing the difference between the transaction date and today’s date with the TODAY function. Indeed, this formatting formula provides many fun options.

## Multiple Conditions

We can also use the logical AND and OR functions in case we want to consider multiple conditions. For example, formatting those rows where the status is open and the amount is greater than 5000 by using the following formula:

=AND($D7="Open",$F7>5000)

Since the AND function returns true when all of its arguments are true, the formatting is applied only when the status is open and the amount is greater than 5,000.

If we wanted to format the row if either condition was met, then we’d want to use the OR function instead of the AND function since it returns true if any single argument is true.

## Conclusion

Having the ability to format a cell based on the value of another cell is quite handy. The key is to imagine that you are writing a formula into the active cell. The formula needs to use the appropriate cell reference styles (absolute, relative, mixed) so that as the formatting formula is filled throughout the selected range, the proper cells are considered. The sample file below has the conditional formatting rules included, so feel free to check it out.

Hope this helps, and remember, Excel rules!

## Sample File

Here is the sample file in case you’d like to open it up and reference it.

FormattingFormula

“YES” It is very useful

Thanks

Kelly,

Thanks,

Jeff

Thanks, Jeff. This is great. I have the perfect spreadsheet to experiment with this. Great tip!

Welcome 🙂

Thx

Gerry,

Hope these ideas help!

Thanks

Jeff

=AND($B1=””,$A1<>“”,$C1<>“”)

This AND function returns true when all three arguments are true, and triggers the formatting rule.

You can easily adapt this basic formula as needed, for example, to add other cells in the analysis.

Hope this helps!

Thanks

Jeff

=”TOTAL “&’1_Category Trends’!B8&” “&TEXT(‘1_Category Trends’!C8,”##.#%”)&” VS LY”

Total Beef -2.9% vs LY

How can I get conditional formatting to make this red if it’s less than 0?

Thanks

Jeff

cell references are relative to the top-left most cell in the Applies to range. You wrote “that the active cell is the reference point for the formula” – I tested this and you’re spot on! If I start selecting cells from the bottom (OK, most people wouldn’t do that, but still) and follow MS rule I end up with incorrect results. Starting my formula from the active cell I’m fine though. Someone should perhaps tell Microsoft, unless I missed something? Anyway, this article deserves much more attention. Well done!! 🙂Thanks!! Glad it was helpful 🙂

Wow! This was very helpful to me. Worked like magic. Thanks.

Nick,

Hope this helps!

Thanks

Jeff

Hi Jeff,

I am struggling with a bug-like feature of MS Excel when using CF.

Let’s see the first one.

There is only one single problem: nothing happens when I apply this method as CF.

Could you please kindly give me a hint where the error is, and how to proceed to fix it?

Thank you in advance, Laszlo

Thank you, Laszlo

Thanks,

K

Kristyn,

Hope it helps!

Thanks,

Jeff

Thanks,

Jeff

THANK YOU SO MUCH for the Conditional Format based on another cell!!!!!

You wrote it so it’s very easily understood. Really appreciate the sample file, too.

-Lisa

Los Angeles

Thanks

Jeff

Hi Jeff,

Date should be >= 03/15/2015 and Amount should be >=5000

Formula which i have written is =AND($B2>=03/15/2015,$E2>=5000)

Thanks in advance

Sudarshan.

Sudarshan,

=AND($B2>=A1, $E2>=5000)

This will help Excel understand the date.

=AND($B2>=DATE(2015,3,15), $E2>=5000)

Hope these ideas help!

Thanks

Jeff

I’ve created the conditional format:

Cell Value $G$2 | (format red fill) | =$H$2

This should enable you to copy the conditional formatting down…hope it helps!

Thanks

Jeff

condition for cell A, (background fill) for a range of between 1 to 5 in cell B.

Any ideas? Thanks

How do you tell there was a First Expired First Out (FEFO) breach?.

I have been doing this manually and will like to have a formula that is faster.

I’ll appreciate your assistance

=COUNTIFS(A$2:A2,A2,B$2:B2,”>”&B2)

Hope it helps!

Thanks

Jeff

Thanks a lot for this article. It was very useful. So well articulated. Thanks once again.

Thanks this helped my manager.

Hello,

A B C

Y 115 OK

Y 87 OK

0 115 OK

0 0 OK

0 115 OK

0 0 OK

0 0 OK

Y 110 OK

Y 110 OK

Hey Ahmad!

Conditional Formatting>New Rule>Use a formula…

=AND($A1=0,$B1<150)

I hope this helps:)

Kurt LeBlanc

Welcome 🙂

Hi Jeff,

Hi Pat-t

Yes you can format cells with a number type:) You will just need to do a new rule for each type.

Hope this helps!

Kurt LeBlanc

Consider the following example please.

If A1 = “xxxx” Then format is $00.00 into cell B2

If A1 = “yyyy” Then format is 00000 into cell B2

If A1 = “zzzz” Then format is 00-00 into cell B2

This sort of formatting in my sheet works if i manually put in the number or string.

However if I copy a number into A1 with a previous format like 0-000 then the format does not work.

then apply the format of that cell to be $ or 0000 or 00-00 ?

Appreciate all of you and the author to bring this up.

danny

Hey Adam,

Excel doesn’t seem to apply the rule to both tables in conjunction. Can you connect the tables?

Hope this helps!

Kurt LeBlanc

Nice! This will come in handy. Thanks, Jeff!

Welcome 🙂

column1 column2

row1 a b

row2 d b

row3 a g

row4 d b

row5 d c

row6 n j

You would select the entire data set. Then

Conditional Formatting>New Rule

>Highlight based on a formula and use:

=AND($B2=”B”,OR($A2=”A”,$A2=”B”))

then select your desired format.

Anyway, thanks for highlighting that point – my formatting is working now!

Welcome 🙂

Hey Ashley,

Let me know if it doesn’t and you need more help,

Kurt LeBlanc

I have an ‘initial and recurrent training’ table of all employees for a specific course.

Column A has employees names.

Column D has dates when recurrent training is due.

So far this works perfectly.

Is that possible?

Hi Linda

Kurt LeBlanc

Column A has ‘Employee Name’

Column H has ‘Certification Date’

Column I has ‘Next Due Date’

‘Next Due Date’ cells have this formula:

=If(H2=””,””,Edate([@CertificationDate],36))

Reasonning: When certification date is entered, it adds the next due date which is every 3 years.

Also, the same cell is conditionnally formatted so that it changes color when:

60 days prior to turns ‘Yellow’

30 days prior to turns ‘Orange’

Equal to or past due date turns ‘Red’

Oufff hope this clarifies it. Lol

Thanks for all your help!!

Hi..I am Sk Raquib..i have a problem in excel..the question is :

District Product Qty sold/kg

Howrah Rice 150

Hooghly Wheat 400

Burdwan Sugar 350

Howrah Rice 300

Que:- Highlight the District where maximum quantity supplied of Rice Supplied.

Hi,

Guys need urgent help..

E.g.

Thanks in advance

Hey Ganesh

Let me know if that helps,

Kurt LeBlanc

Hey Ganish

What I did was write the code:

MsgBox ActiveCell.Font.Name

MsgBox ActiveCell.Interior.ColorIndex

Always happy to help

Kurt LeBlanc

Thanks,

Jeff

i have a problem

Trying to color one cell if any of the a range of adjacent cells are populated?

tried this and did not work

rule formula:

=AND(NOT(ISBLANK($I8:$AG8)))

applies to: =$H$8

HELP

Hey Jim

=OR(NOT(ISBLANK($H$7)),NOT(ISBLANK($I$8)),NOT(ISBLANK($H$9)),…each adjacent cell)

that should yield the desired result. Let me know how that works out!

Kurt LeBlanc

Hey Dylan

Let me know how that works for you:)

Kurt LeBlanc

For example here’s my range:

A B

10 9 (LESS THAN RED)

11 12 (GREATER THAN GREEN)

11 13

12 9

8 9

8 7

11 10

12 13

Hey Dorothy

=N1=”USD”

Let me know if that helps:)

Kurt LeBlanc

Im literally so confused by this article. I have a bunch of tables vertically.

Hey George

Sorry you got so confused…

Let me know if that helps,

Kurt LeBlanc

Trying to highlight cell if value is more than 5 of the previous values.

Ex.

100

250

251

255

265

250 and 265 should be highlighted

Awesome 🙂

Greetings, I am trying to format cells in column J based on the values of cells in columns A and J.

What more do I need to do?

=AND($A3=”Risk”,LEFT($J3,1)=”L”)

Thanks.

Can some one help me on this how do I do with using any excel formulas.

Day High Dates

14 13,14,21,22,23

27 16,27

28 16,17,27,28,29

16 16,27,28

31 08,30,31

Great explanation!! I have been trying to figure this exact issue for last two days. Thanks!

Awesome, glad you got it!

Thanks

Jeff

Great write up. Helped me out ages ago and I’ve returned to it often.

Best site I have come across for explaining things in plain English.

Thanks Jeff

Thanks 🙂

A B C

1 19% 17%

2 18% 22%

3 43% 10%

A B C

item cost price of combo pack

pencil 25

sharpener 35

eraser 45

total 105 110

thanks

Any help is appreciated.

Mike K

Hey Mike!

If the input cell is A1 and the table is C1:I50, the formula would be

=$A$1=C1

Hope that helps:)

Kurt LeBlanc

Hope this helps.

Thanks

Jeff

Dear Jeff,

if the is another method please explan

Hi Jeff,

Many many thanks in advance!

Hello,

Thanks for your help!

You could use the OR function, which return true when either condition is true.

Thanks for the article Jeff.

Thanks!

Thanks

Jeff

In excel, when cell ‘A’ is blank, I want cell ‘B’ to be turned to different color,, Please advise.

=ISBLANK(A1)

Then, select your desired formatting to apply.

Hope it helps,

Jeff

HI Jeff Lenning

Is it possible?

Hi,

=COUNTIFS($A$2:$A$75800,$A2,$B$2:$B$75800,$B2)>1

Thanks!

Thanks for the article Jeff, it is of great help.

How can I do the conditional format?

Thanks,

Derok

Awesome article, thanks Jeff!

After searching the web for a while, I found that you have the best explanation for this purpose.

Thank you for sharing.

Thank you for your help!

Jeff,

Thanks,

Thanks!

Line 1 has a serie of dates

Line 3 is a formula outputing numbers of remaining task to do in time

Bravo! Bravo!

G’day Jeff,

Thanks,

James

Hello Jeff

Fantastic article, however I have a question I cannot solve myself and cannot find a solution for.

Any assistance please?

Many thanks

Ryan

B – Planned C – Actual

4000 5000 => Here, this cell should turn red as Actual > Planned

5000 3000 => Here, this cell should not turn red as Actual < Planned

Imagine there are hundreds of such entries.

Any help in this would be greatly appreciated 🙂

Hi Akshatha!

=$B2<$C2

Thank you,

Kurt

Planned (Column B) Actual (Column C)

4000 (Cell-B1) 5000 (Cell – C1) => Here, this cell C1 should turn RED as Actual > Planned

5000 (Cell-B1) 3000 (Cell – C2) => Here, this cell C2 should NOT turn red as Actual < Planned

Hey Akshatha,

=C1>B1

Excel will go through every cell and run its test. Let me know how that works!

Thank you,

Kurt

That worked! Yayy! Thank you very much!

I hope this make sense.

Hi Michael!

=OR(B3=”Supervising”,B6=”Supervising”)

That way it should light up no matter which cell is changed to Supervising:)

Thank you,

Kurt

Extremely detailed and very helpful. Thanks!

Hi Desiree,

I hope that helps!

Thank you,

Kurt

Hi Jeff,

Thanks for the tutorial blog.

I have a sheet wherein I will be calculating the time spent by my team member.

Thanks in advance.

Derok

I have a problem that am not able to resolve……..

O P Q R S T U V

141431 1.07 133 0.50 291 2.42 58 0.71

Columns P,R,T & V are the %age change over the previous period.

That is to say if

If Col P = 4, col R=3, Col T=2, and Col V=1, then highlight in one colour.

And if

If Col P = 4, col R=5, Col T=6, and Col V=7, then highlight in a different colour.

But if

Col P = 4, col R=5, Col T=2, and Col V=7, then No highlight.

Seems like a bit much to ask, but would really appreciate your assistance.

Look forward to your response.

Linda

I want when the value turns acceptable to be green and when unacceptable to be Red. Please help!!!

Attaching your example worksheet allowed me to recreate these on my system. Thank you for providing!

Attaching your example worksheet allowed me to recreate these on my system. Thank you for providing!

I have a table with three columns.

(A) Actual Revenue

(B) Budgeted Revenues

(C) Difference

What I have so far is: =$C$1< -2000000

then I format it a deep shade of red. It doesn't work though. Any advice?

I resolved my issue, thanks!

Thanks a lot sir,

It was very useful and productive.

I used this formula

Me.Range(“C1”).Interior.Color = Me.Range(“A1”).Interior.Color

End Sub

Works great. Very clear. Thanks a lot. I wouldn’t have figured it out without this.

http://www.excel-university.com/comparing-lists-with-conditional-formatting/

Thanks

Jeff

If cell A1 contains specific text “monthly”, then highlight cell B1. How do you do this?

Let’s say I have a worksheet in which all the cells are protected in column O.

My column D has drop down menu to select from @ 58 list.

How we can achieve this.

Looking forward and thanks in advance.

I have struggled in the past with the formula based Conditional Formatting, so this blog was great!

Can you help please?

Hi Jeff,

Awesome glad it helped!

Thank you.