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.

Get a quick email notice when a new Excel article is available

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

Hello,

I think I am doing something wrong!! I keep trying to do this and its not working for me!! I am trying ti get column N drop down boxes to change formatting on regular cells in column O!! The drop down boxes contain USD and EURO and in the cell in column O I want the formatting to change with the drop down list! Example: Column N row 1 is USD I want column O row 1 to change the format to $ sign in front of number, but if it said EURO I want it to change the sign to the euro sign! How would I do that exactly!

Hey Dorothy

The formatting rule must be a Boolean (True/False) test, so what you do is select O1 and conditional formatting>new rule When you do, select the option for using a formula (all these steps per blog). Your formula would check if N1 is a certain text so

=N1=”USD”

You would then select the formatting you’d like and repeat the rule for each different drop-down choice, replacing the text in the formula.

Let me know if that helps:)

Kurt LeBlanc

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

in each table, there are two rows baseline values and right under it the new values obtained during my experiment. Ive tried this every which way and it WILL NOT WORK. i need to compare the obtained new data to the baseline data in the row above it.

Hey George

Sorry you got so confused…

If you select the whole row of new values and start a new rule, create a formula that references the base value (be sure it is a relative reference because it is a locked reference by default) and choose your formatting preference. This should help you fix the issue:)

Let me know if that helps,

Kurt LeBlanc

I just want to say thank you. This was the exact information I needed. I appreciate the clear writing and sensible presentation of the knowledge. My spreadsheet is now highlighted the way I want it! Thanks!

Awesome 🙂

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

Awesome, glad you got it!

Hi, please solve my problem. cell B1 contains 1 to 6 which are display in H1 category wise. F1 contains total value of C1:E1. Now I want to do….. if (B1=6,F1=0,G1>0) true. Again if F1=G1, true. otherwise false. how can I get it together in a single formula.

Sojib, I’d recommend using an AND function. When all arguments of an AND function are true it returns true, otherwise false. Excel also has an OR function which returns true when one or more arguments are true. Hope these help!

Thanks

Jeff

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

Thanks Jeff

Thanks 🙂

I’m pretty new at conditional formatting but although it seems like it should be easy, I’m stumped! I have two columns of %’s such as:

A B C

1 19% 17%

2 18% 22%

3 43% 10%

All I’m simply trying to do is conditional format column C with a colored flag or colored circle icon if A1 is larger than B1 or the other way around…ultimately row C would be nothing but a row of icons. Specifically what I’d like is: If A1 is larger than B1 then a red flag and if A1 is less than B1 a green flag. I appreciate any and all help for a beginner.

pls clarify my doubts:

A B C

item cost price of combo pack

pencil 25

sharpener 35

eraser 45

total 105 110

I am making a combo pack of 1 product with three products and for assuring the price of combo pack should never be less than total cost of individual items.

I just want to compare total of column C5 (110) (total cost of 3 products) with total of column B5 (105) (price of combo pack) and show the B5 column in ‘Red’ if C5 is less than B5.

Pls explain how I can highlight in red if if the total cost is less than price by using conditional formatting.

thanks

Jeff,

I would like to write a conditional script that formats a group of cells based upon the value that I enter into a cell outside of the value.

In essence it accomplishes the look up function but much more quickly. So for example, I have 170 employees in a complex schedule and I want to find the employee with initials MM (I list all staff by their initials in the field. I could, in theory enter MM into a cell that is the “call cell” and that then would highlight any cells in the field that match.

Any help is appreciated.

Mike K

Hey Mike!

I think that I can help you:) Hopefully you are looking for a correct formula like the blog does? Conditional formatting formulas evaluate to a Boolean value (true/false) only. You can have that input cell you mentioned where you would enter the initials you’d like to highlight through your table, and just set it equal to each cell in the table. It would only highlight cells that make the formula TRUE.

To do that, highlight the table, and create a new rule with a formula. Lock the input cell you reference, then make sure there is a total relative reference to the first cell in the selection. Excel will then cycle through each cell to determine which cells return TRUE to that formula:)

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

=$A$1=C1

Hope that helps:)

Kurt LeBlanc

Hi, I’m trying to apply conditional formatting to balances between certain ranges based on the criteria in another column. Eg I need to highlight leave balances for part time and full time staff for 6 week, 8 week and 10 week ranges. The formula I am using for part time hours is =IF($J6=”Part Time”, $K6>240) (6 weeks) which works fine if formatting everything over 240 hours however if I try to apply for the range 240-320 or 320-400 and over 400 the formatting doesn’t change. The criteria for Part Time & Full Time is in column J and the hours of leave are in column K. The formula for Full Time is based on the same principal. I have tried using an AND formula and I’m having the same result. I currently use conditional formatting to highlight on cell values between two ranges () but only for Full Time hours but also need to identify when Part Time hours are over the 6, 8 & 10 weeks balances. Please help.

Hello,

I cannot seem to get this to work. I’m looking to highlight blank cells based on two different text phrases found in another row. For instance, i want the cell to recognize “Sat” and “Sun” and grey out the cells that correlate. However the cells that I’m using as a reference point are technically DATE formulas but display as the day of the week, i can get it to format if i type the phrase in as text but not from the cells with the formula in it. I have it built so that when the year in a drop-down on the workbook changes, so does the days and dates of the months. This is going to be used for attendance tracking and i want to make sure Sat and Sun are grayed out. Because obviously the days of the week change each year i cannot simply just lock out the cells. I hope this makes sense! Please help!

Instead of using the text strings sat and sun, try using the WEEKDAY function to see if the date value is equal to 1 (sun) or 7 (sat).

Hope this helps.

Thanks

Jeff

Dear Jeff,

i need to color a range of cell (ex B1: c11) base on the value of another cell, if i remove the “$” from the rule in formula it may not work properly please help me to solve this

if the is another method please explan

Hello,

How would I highlight the value in column B, if the value in A was “sat” OR “sun”? I wrote =$A2=”sat”,which works for “sat” only but i cant get it to work using both.

Thanks for your help!

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

Thanks for the article Jeff.

I have a question. Is there a way to conditionally protect a column if something is entered in the same row of another column? I don’t even know what to google, but I’m coming up blank.

Thanks!

In the current versions of Excel, conditional formatting doesn’t support cell protection or styles. So to my knowledge, this isn’t something that can be done with the built-in options. One possibility would be a macro, but, I don’t have any sample code to share. I will however add this topic to my “things to blog about” list, because, I think it could be pretty cool.

Thanks

Jeff

Hello Jeff,, I need a help ..

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

Assuming you are using a version of Excel that includes the ISBLANK function, you could select column B, and then use a conditional formatting formula like this one:

=ISBLANK(A1)

Then, select your desired formatting to apply.

Hope it helps,

Jeff

Awesome article, thanks Jeff!

Very well done Jeff!

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

Thank you for sharing.

Bravo! Bravo!

Hi,

I have this budget sheet where Column B is ‘Planned Expenditure’ and column C is ‘Actual Expenditure’. I want the cells where the Actual expenditure is more than planned, to be highlighted in Red. Problem I am finding is how to apply conditional formatting using formula but to cover the range of relative values.

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!

To have the entire rows turn red, highlight the entire table (Be sure that the top-left cell is the active cell.). Then you’ll apply the conditional formatting with a formula comparing the actual to the planned values. The formula is cycled through every highlighted cell, so you’ll want to lock down the column references when you write it, like below:

=$B2<$C2

So when this formula evaluates to TRUE, Excel will apply the formatting. Try it out, and let me know how it goes:)

Thank you,

Kurt

Hi Kurt,

Thank you for your reply but the requirement is slightly different. I don’t need the entire row/column to be highlighted in red but only those specific cells where the actual value is greater than the planned value.

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

So within the same column C, there may be several cells with red and several without red based on their actual values being more than/less than planned values.

Hey Akshatha,

Thank you for letting me know! This will actually be almost the same. All you need to do is highlight the column of cells you want to apply the rule to and enter a formula that tests if the actual values are greater, like below, and add your custom formatting Excel will apply if the test returns TRUE.

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

Hey I read through everything and I still can’t figure out my problem. I am trying to change a single cell in column E when something from a drop down list in column B is selected. For example in cell B3 I select “Supervising” I would like cell E3 to highlight green. but I would like the Cell E3 to change even if cell B6 is changed to supervising.

I hope this make sense.

Hi Michael!

When you’re entering a formula to conditionally format cell E3, try using the OR function as follows:

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

How do I apply conditional format in one cell when there is a drop-down list in another cell? For eg, cell A2 has drop-down list containing items “one, two, three.” I want cell A1 to highlight yellow for “one”, blue for “two” and green for “three.” So if I select item “two” from the drop-down list in cell A2, I want cell A1 to highlight blue. Hope this makes sense. Thank you!

Hi Desiree,

To apply CF in this situation, you would just have to create rules for each color checking the selection, i.e. =A@=”one”, and apply the formatting. You would have to do that for each color. Excel doesn’t allow you to create one rule to apply 3 colors:/

I hope that helps!

Thank you,

Kurt

I am filling in text in both Columns D and G. I want to shade the cell in column D, only when the respective cell in Column G is blank AND the cell in Column G is blank. How would I go about this?

Good day,

I am attempting to conditional format a cell based on 2 different cell references. Say column C3 is a date 1-Oct-18 and column AR has either OPEN GROUND BOARD INVESTIGATION, OPEN AVIATION BOARD INVESTIGATION, OPEN UAS BOARD INVESTIGATION, OPEN AGAR, OPEN AAAR, OPEN UASAR or CLOSED which are added into column AR by use of a drop down menu. What I am trying to do is based on the date in column C3 and having any one of the open statuses in column AR to have a color change in column C for 15 days and a different color for 30 days beyond the date in column C. I know I’ll have to use 2 Conditional Formats, one for each rule but that is fine. Also when CLOSED is selected in Column AR I would like for the color changes to disappear. I hope this is clear enough, and thank you in advance for your help.

Good Morning Jeff. I need to ask about formatting a cell with number. I want it to show up something different then what i entered. I know how to mask out the info in a cell by using ;;; . What i want to do and unsure how to this if possible is if I type in 2313, I want it to show up 2435 (yes adding 122 to that number i entered and same for every cell in a designated column). I want to do this in formatting if possible without doing a reference to another column or formula for the output. Is this possible.

Look forward to your response.

Worked great to create the colored filled columns that I desired. Is there a way to ‘lock’ the conditional formula, so that when I move cells around by cutting and pasting, the colored columns remain where they were initially created instead of moving to the new pasted address?

Hi.

I have a staff roster that uses colors to highlight the different roles that staff are rostered in for the day. The master roster is a simple table that has a column with staff names, the days of the week across the top, and then colored cells to show what day they are working and the role they will be in.

On a different worksheet, I have a daily view of the same roster broken down into 15 min blocks. The color of each staff member’s role is spread across the day – showing the hours they are working and when their breaks are.

What I was hoping to do, is have the daily roster automatically reflect each staff members role color by coloring the background of the cell with their name. (referencing this from the master roster). I will need it to auto adjust if I happen to change any role colors in the master roster.

This will help me quickly fill in the daily roster in the right role colors without having to manually reference the master for the right role colors to use. Is there a formula for this?

Linda

Dear Jeff, I tried all that you recommended but could not get the right highlighting. The concept is if the value is between 4 and 6, it turns to be acceptable, However, if it is out of that range, it is not acceptable. Here is my formula IF(AND(4=A3),”OK, ACCEPTALE”,”OUT OF RANGE, FAILED”) this is my formula.

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

Hello I need help with the following: I have formatted (Sum) a cell to equal zero when it is a negative after add 2 other cells together. But now I want to take that the cell that is formatted to zero and add it to other cells but using zero as the number instead of the correct number before formatting to zero. See below

A1 5 A2 -15 A3 sum A1:A2 equals -10 but formatted to zero because its a negative number A4 10 A5 10 A6 5 A7 sum A3 thur A7 equals 25 but because of the A3 equaling -10 before formatting to zero my sum equals in A7 is 15 instead. How can I format it to read A3 as a zero?

This is a great article! I particularly liked your discussion on using cell references in the formula for the active cell. You said, “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.”

I have stumbled SO many times with the formatting not giving me the picture I expected because the formula as applied over the range did not lock down the active cell to evaluate.

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

Thanks so much! I had to use multiple conditions, and it is something that I have not needed to do in a while… so I forgot the exact syntax to use. As soon as I saw you example I said “yes, that’s it!”!! This was a little different as I had created a drop down list using wing ding fonts… for an empty box and a checked box. My project file has a list of requested fixes/enhancements, so I wanted a simple way to indicate what was done. The CF was a requirement as it is then easier to see which items have not yet been completed.

Great article and has really pointed me in the right direction! I just had a slightly different specification.

I have a table with three columns.

(A) Actual Revenue

(B) Budgeted Revenues

(C) Difference

I want to format my actual revenue column with colors based on the difference column. If the difference < -2000000, then I want to format it a deep shade of green.

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?

Jeff, I could use some help. I have a dollar amount in cell A1. In Cell B1 I will enter a date when the the Amount in A1 gets submitted. In Cell C1 I will enter a date when the Amount in A1 gets paid. How do I color format it so that A1 is red when submitted and turns green when paid? Any help you can give me would be great. I think I am having issues with how to enter the date in the formula. I’ve tried “mm/dd/yy” and several other options, but nothing works. Thank you!

I resolved my issue, thanks!

Thanks a lot sir,

It was very useful and productive.