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.

This is fabulous; thank you! Completely clear, worked first time and did what I expected – and I even understand how to apply this to other, similar formatting issues.

“YES” It is very useful

Thanks

Thank you! I have a long spreadsheet where I track production percentages by date, but sometimes it is hard staying on the correct row. I enter the data for the previous workday, so I highlighted that row using the formula =$A4=Workday(Today(),-1). When I open my sheet every day, the correct row is highlighted!

Kelly,

Thanks for sharing your application of conditional formatting…that is very cool and creative…I love it!

Thanks,

Jeff

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

Welcome 🙂

Hi Jeff, great article. Is there any reason that the row would not update after changing the value? I change the value and I need to highlight the cells for it to show. I have one formula to set the color and another to clear the color . Your sample works fine for me but not in my spreadsheet.

Thx

Gerry,

Thanks! If the cell value changes, and the conditional formatting doesn’t update as expected, I would check the following items. First, I would remove the conditional formatting rule that clears the color (as it is not needed it may be confusing Excel). Next, I would double check that the conditional formatting rule uses the proper cell reference style, for example, $D7…that is…absolute column reference with a relative row reference. If it uses $D$7 or D$7 you may not be getting the desired result. Last, I would confirm that you initially set up the conditional formatting rule by using the row reference for the active cell.

Hope these ideas help!

Thanks

Jeff

Hi Jeff Lenning, Thanks for this article. I just need to know how to get highlighted one empty cell in a column (say “B”) when other cell(s) (one or more, say “A” & “C”) in the same raw is filled with some values. And the highlight remains until the cell in “B” is filled with some values. Is that possible? your reply will be very helpful and is highly appreciated. Thanks in advance..

Sure, you can accomplish that by using the AND function in the conditional formatting rule. For example, to format B1 when B1 is empty, A1 is not empty, and C1 is not empty, use the following:

=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”

Above is my formula to concatenate a series of information to arrive at something that reads like this:

Total Beef -2.9% vs LY

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

You would set up the conditional formatting rule based on the value in C8 rather than the concatenated text string. For example, =C8<0.

Thanks

Jeff

Thanks so much for this article. It’s one of the best I have read on this subject and, amazingly, it’s the only one that explains cell references correct. Most blogs (even Microsoft’s own one) state that

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.

Jeff,

Can I conditionally format a cell based on the value of a cell in another tab in the workbook? No problem getting the formatting to work within the same tab.

Nick,

Yep…you can conditionally format a cell based on the value of a cell in a different worksheet by referencing the sheet name in the formula. For example, instead of A1 you would use Sheet1!A1. The sheet name must be enclosed in single quotes if it contains a space, for example ‘Sheet 1’!A1.

Hope this helps!

Thanks

Jeff

Hi Jeff,

great post with fine explanation, thank you!

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

When I copy/paste a value from a cell to another, it drags the CF of the copied cell and applies it in paste destination unexpectedly.

I have found two ways to work this out: one is using indirect cell reference as rule description, another is a VBA Worksheet_Change.

Let’s see the first one.

As indirect cell reference, I entered the formula in Conditional Formatting Rule as follows: ‘=INDIRECT(Sheet3!$D$2)’, where D2 on Sheet3 contains a reference like ‘Sheet3!$C$2’. C2 is the cell in which the actual formula is stored.

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

When I enter the formula directly as rule in CF, it runs perfectly, giving me the desired results always.

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

Thank you in advance, Laszlo

Additionally to above: I have either tried formula ‘=cell(“contents”,Sheet3!C2)’ that also refers to C2 on Sheet3 but this one is dead too.

Hi Jeff, have you had the chance to have a look at what I posted (still cannot see displayed anyway)?

Thank you, Laszlo

Hi Jeff, Great info. I’m looking for something pretty specific. I have cells in column A and B that may be shaded either red/amber/green. I would like cells in column Q to highlight red IF two of the cells in column A and B are both red, side by side. Is there a way to do that?

Thanks,

K

Kristyn,

Rather than do the conditional format rule based on the formatting (red), you may want to consider setting up the conditional formatting formula to format the cells based on the underlying logic that shades the cells red. For example, if the cells are red when they are less than zero, rather than set up the conditional formatting rule based on the cell format (red), set it up based on the formatting logic (less than zero). In the case when the logic is not uniform, but rather applied manually or arbitrarily, you may be able to use the CELL function or a macro.

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.

I have been thinking about taking some advanced classes and will Definitely check out your online option!

I just noticed, your work is marketed as being for CPAs and Accounting Professionals… I’m an HR Analyst, would it not be suited for me?

-Lisa

Los Angeles

You are welcome, I’m glad it helped! Regarding the courses, I believe the Excel topics covered have broad applicability beyond accountants, but the course examples are designed to resonate with accountants. Check out the course descriptions for the exact items covered, and also the free evaluation course to get a feel for how they are set up.

Thanks

Jeff

Hi Jeff,

It’s really helpful topic thanks for sharing. But i have one question regarding this, I have placed some condition on your excel sheet but i am unable to get the desired result. The condition is:

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,

Based on your formula above, my best guess is that Excel is not understanding the date…so…I would recommend either storing the date in a cell or using a date function. For example, if you stored the date 3/15/2015 in cell A1, then, you would update your formula to this:

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

This will help Excel understand the date.

Alternatively, you could use a date function to generate the desired date, like DATE or DATEVALUE. Something like this:

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

Hope these ideas help!

Thanks

Jeff

Thanks for the info Jeff, it’s helped tremendously!! I’ve been able to create what I wanted but now I can’t get it to copy:

I’ve created the conditional format:

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

So if the values in H2 and G2 are not the same H2 has a red fill. What I want to do now is do the same for the rest of my cells from H3:G3 on down the column. The only way I’ve been able to do this so far is to create a rule for each and every cell which gets quite tedious as some of my lists are 50+ items long. Is there a quicker way to just copy that conditional formatting where the cells (within the formula) automatically change with each row?

Jeff,

The key to making this work is using relative references. For example, instead of $G$2 and $H$2 you would want to use G2 and H2.

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

Thanks

Jeff

Hi,

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

I have this very large data and am trying to determine if a product with a later (greater) BB date is posted before one with an earlier (less) BB date on excel. The BB dates are in a column while the posting date is in another column

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

I’ll appreciate your assistance

You could use COUNTIFS to accomplish that. Basically, counting the number of rows above that meet the criteria (a) has the same product id as the current row and (b) has a date that is greater than the current row. If you set up the range references properly then you can fill the formula down. Assuming your product ids are in A, your dates are in B, and the first data row is 2, something like this should work:

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

You could then use conditional formatting based on the new formula column if you wanted to highlight any that are 1 or more.

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,

Need help on below. If Column A = 0 & Column B <150 highlight the complete row in red. how can i achieve this?

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!

You can highlight thee table and apply a conditional formatting rule. Use a formula to determine which cells to format:

Conditional Formatting>New Rule>Use a formula…

Write the formula, locking in the columns since the rule will be applied to every cell and format like you want:

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

I hope this helps:)

Kurt LeBlanc

Finally I can format the text colour/bold etc in one column based on values contained in two or more other columns. Using AND and OR really does the trick. Thanks so much.

Welcome 🙂

Hi Jeff,

do you know a formula or way to get the cell’s format to changed, based on text from a different cell? For example, if cell A1 says “$ AMT”, then I want cell B1 to format in currency. However, if cell A1 reads % PCT, then I want cell b1 to format in percentage. Any help would be much appreciated!

Hi Pat-t

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

When you click to format a cell based on a formula, click “Format” and the number tab is before the default font tab.

Hope this helps!

Kurt LeBlanc

If I have a table of percentages and I want them to be formatted with a two color scale based on the rank of their volume compared to the others (numbers in an identical size table down below) how would I do that?

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 🙂

i have the following datas

column1 column2

row1 a b

row2 d b

row3 a g

row4 d b

row5 d c

row6 n j

i want to find “b” in column 2 and then find the amount in front of “b” in column1 as you see the amounts here are “a” and “d” then after finding “a” and “d” turn the colors of all the row that contain these two amounts for example to red even though the amount in front of “a” and “d” are not equal to “c”

I believe what you want is the OR() with AND(). I don’t understand the last part about not equaling “c” though if you can clarify that. The following addresses the highlighting rule based on the other requirements:

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.

Thank you! Had so much trouble getting my formula to work, and I was missing one key piece of information – that the active cell mattered. I thought I could just type in the formula and correct ranges, with correct absolutes, etc and it would work (It does work like this is google sheets, and defaults to the top-left cell of the range as what excel apparently considers the active cell).

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

Welcome 🙂

I have 2 columns of dates and I want to highlight cells in the second column of dates that are 3 days past (out of window) of the dates in the first column. What conditional formula can I use. I found the greater than function but how do I do greater than plus 3?

Hey Ashley,

To handle your issue, highlight the entire second column and apply a new rule. the new rule should be a formula that compares the second column’s date to 3 days past the first column’s date: =second date>first date+3

Excel puts absolute referencing by default so make sure you notice that, and format it like you want! That should work:)

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

Kurt LeBlanc

Hi Kurt,

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.

When training is due 60 days prior to today the cell turns ‘yellow’, 30 days turns ‘orange’ and if = to or bigger then today it turns ‘red’.

So far this works perfectly.

Now, i need to be able to re-enter an employees name below the list once employee receives the due training and need to maintain this table to provide evidence that employee received training every 2 years.

The problem i have is that i need a formula that will remove the above conditional formating if the new entry of the employee name matches a name above and that the new date in D is bigger then the date above when matched.

Is that possible?

Hi Linda

I’m sorry, but I don’t see a way to turn off conditional formatting, even with a macro…Macros can change traditional formatting if that helps.

Kurt LeBlanc

Many thanks for a good explanation into the conditional formatting functionality – helped me get my head around it for the first time

Ok here’s a better explanation.

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’

I want the column I cells to change when I:

a) Re-enter an employee’s name, the previous entry (of that same employee) next due date weather colored ir not to turn ‘Pink’. This way I know his in process of receiving the training and /or registered. Then,

b) That same previous entry color coded ‘Pink’ needs to be cleared/white when I enter on the new entry row his new certification date.

Oufff hope this clarifies it. Lol

Thanks for all your help!!

Hi,

Guys need urgent help..

I am trying to change row color based on corresponding text in same row but at different cell.

E.g.

Values from Column A selected using dropdown and only for few of the values from the dropdown the corresponding row color should change to “White” and for rest of the values the row color should remain same.

Note- I am selecting values for all rows in Column A using dropdown and above should applicable to all rows in column A

Thanks in advance

Hey Ganesh

This can also be done with conditional formatting but with a formula:) Try this http://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/

Let me know if that helps,

Kurt LeBlanc

I have different colour cell ,in each colour I have given different “name” in front of that colour cell, now I want where ever in that sheet if there is same (cell which have given different “Names”) Colour cell ,when I click on that cell it should show that “name”.

Hey Ganish

I worked on this for a few hours and can’t anyway to retrieve a cell’s color…I can get the color code with a macro, so I tried converting that to a color name with a function. No luck unfortunately. I can get the font name, but not a color.

What I did was write the code:

MsgBox ActiveCell.Font.Name

And assigned that to a button. Then I selected any cell and checked the name with the macro. I’ll put the other code for the color code if you’re interested:

MsgBox ActiveCell.Interior.ColorIndex

Maybe someone knows something I don’t and can help you get EXACTLY what you want, but let me know how this works for you:)

Always happy to help

Kurt LeBlanc

Thank you, thank you, thank you!! I spent hours trying to get conditional formatting to work, I even started looking at troubleshooting pages thinking there’s a bug that was keeping my formula from working. Your very clear, very precise explanation of the active cell and why it is important saved the day! Formatting works and I can move on with my life (well, maybe I’m not that obsessive, but I do hate a problem).

Thank you. I have bookmarked Excel University and it will now be my go-to site for my Excel questions!

You are very welcome! I’m glad the post helped, and am glad you got it working…way to go!

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

You are on the right track:) The ISBLANK() and NOT() functions can only reference one cell, so in your NOT()’S you want to put each ISBLANK() and wrap everything in OR() instead because AND() is only TRUE if everything is TRUE so:

=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

Hello,

Excel is not my strongest point…I am needing to format “If column k is more than column j highlight it red” can you please help me?

Hey Dylan

This is the post Mr. Jeff wrote on the issue you’d like to solve: http://www.excel-university.com/custom-conditional-formatting-rules/

The formula can be any formula that returns a Boolean (True/False) value, so you’d simply have “=k>j”, or something similar, and choose your format.

Let me know how that works for you:)

Kurt LeBlanc