Conditional Formatting in Excel

by Scott Manning on April 21, 2003

Conditional Formatting is one of the features that takes Excel from a simple spreadsheet tool to an application builder. Depending on the value of a cell, you can change the formatting of that cell. Formatting in cells can also be conditional based off of the values in another cell or the results of a calculation. This is done dynamically saving the user the trouble of changing the format themselves.


I use conditional formatting in Excel to track things like invoicing. With conditional formatting, I can tell at a glance which clients of mine owe money and which ones are paid up. I also use different coloring for charges and deposits.

Less than, greater than, and equals a number
In this tutorial, we will set up cells to be highlighted based on whether or not they are less than, greater than, or equal to zero. We’ll make cells change colors based off of this condition.

Let’s get started.

1. Open a new Excel file.

2. Type in some sample numbers in a column. In my sample, I have typed 0, -1, and 1. When we’re finished with the tutorial, our sample file will show the conditional formatting in action immediately. In column C, I have typed descriptions on which color the cells should be.

excelconditionalforamtting-beforesample (7k image)

3. Select the cells you want to be affected by the conditional formatting. In this example, I have selected B2, B3, B4, C2, C3, and C4.

excelconditionalformattining-selectcells (8k image)

4. Go to Format > Conditional Formatting…

excelconditionalformattining-menu (3k image)

The Conditional Formatting box will appear.

excelconditionalformattining-settingitup1 (7k image)

5. In this box, change Condition 1 to “Formula Is”.

6. Then type “=$B2=0″ without the quotation marks. This is saying that if the cell in this row that resides on the B column is equal to zero, then the conditional formatting will be applied.

7. Click Format… to specify the format applied when the condition is true. The format can include font, cell color, borders, and more.

In this example, I set the formatting to color the cell blue.

8. Click Add >> to add another row in the Conditional Formatting box.

9. Repeat steps 5 through 7 twice. Specify different formatting options and use the following formulas: “=$B2<0″ and “=$B2>0″. When you’re done, your Conditional Formatting box should look like the sample below.

excelconditionalformattining-settingitup (10k image)

10. When you are done, click OK and your Excel file should look like the example below.

excelconditionalforamtting-lessthansample (7k image)

If the formatting didn’t work, go over the steps to determine what you did wrong. If you get really lost, you can download the sample:

Other possibilities
The possibilities are almost endless with conditional formatting. Instead of using “Formula Is”, you can use “Cell Value Is”. From there, you can specify what the cell value should be equal to, should not be equal to, should be between, etc.

Sadly, you are limited to only three possible conditions on any given cell.

If you are going to be using alpha characters instead of numeric characters, the alpha characters must reside within quotation marks in your formula. For example: =$B2=”Scott”.

There are the basics of conditional formatting in Excel. If you use this tutorial in a project, post your story here. You might spark some ideas in other readers.


{ 60 comments… read them below or add one }

1 Eileen April 27, 2011 at 3:11 AM

Is it possible to conditonally format a cell when it has a calculation in it. Let’s say I have an AVR calc for several lines. Can I conditionally format that cell to say if the the average is greater than 5 color the cell green?

Reply

2 Scott Manning April 27, 2011 at 7:39 AM

Eileen, it is possible. If you follow the same instructions on this page, Excel will conditionally format a cell with a calculation the same way it will format a cell with a static number.

Reply

3 jitendra May 31, 2011 at 6:13 AM

thanks

Reply

4 Kevin June 11, 2011 at 11:14 PM

What formula can I use when I want to have a cell look at 2 other cells and pick the higher (or lower) of those two?

Thanks!

Reply

5 Scott Manning June 12, 2011 at 12:26 AM

Kevin, the following assumes that the two cells are B1 and B2.

=MAX(B1:B2)

Reply

6 Scott Manning June 12, 2011 at 12:27 AM

Use the following to return the lowest value.

MIN(B1:B2)

Reply

7 Kevin June 12, 2011 at 12:34 AM

Thanks, Scott.
That was exactly what I was looking for.
If I want to just select individual cells and find the largest or smallest, just add a comma between then different cells or control-alt-select each one.

Reply

8 Scott Manning June 12, 2011 at 12:40 AM

Kevin, if the cells are connected, then you can use the following code, which will use B1, B2, and B3 in the formula.
=MAX(B1:B3)

If the cells are not connected, then you will need to separate the groups of connected cells with a comma. The following will use B1, B2, B3, and C5 in the formula.
=MAX(B1:B3,C5)

Reply

9 Kevin June 12, 2011 at 9:39 PM

I have another roadblock that is tying me up.

=IF(M142<25%,M142,25%)

I need to also somehow reference that if M142 is zero, then the new cell value is 25% minimum.

Any suggestions?

Reply

10 Saurav Baidya June 14, 2011 at 6:21 AM

Hi Scott!

Thanks a lot! It is just great! Since yesterday night I was trying to change cell background colour based on condition. I have a question, if I have a conditional statement, is it possible to get the result in form of binary or Boolean value 0 or 1?

Reply

11 Scott Manning June 14, 2011 at 9:08 AM

Saurav, if I am understanding you correctly, then yes, it is possible. However, you would not use conditional formatting for the job. Instead, you would use a formula in another cell.

For example, if your value is in A1 and B1 will display the Boolean value, your formula may look like this:

=IF(A1>10,1,0)

This formula looks at A1 and determines if the value is greater than 10. If the value is greater than 10, the formula displays 1. Otherwise, it displays 0.

I hope that helps.

Reply

12 Saurav Biadya January 5, 2014 at 7:51 PM

Dear Scott,

It works! Thanks for the guidence!

Kind regards,
Saurav

Reply

13 Dale June 17, 2011 at 11:54 AM

How can I format a cell so that it equal anumbe r…example

If cell C is 53 or above I want cell D – to show the number 23.
If Cell C is 53 or less I want Cell D to be 16

Reply

14 Scott Manning June 17, 2011 at 12:03 PM

Dale, you can use the following formula to do what you are asking.

=IF(C1>=53,23,16)

Reply

15 Dale June 17, 2011 at 12:20 PM

Thanks alot

Reply

16 Kevin June 23, 2011 at 11:13 AM

I have a workbook with links between pages. The first page is my “Input” page where the information is imported from another program. That info there then fills in specific cells on different pages based on the layout desired by the client. On my client page, I need the cell to read the “input” cell as a date, but then reference the cell as either OPEN or CLOSED if there is a date shown that is before today’s date. Something along the lines of:
=IF(Input!V2=<TODAY,CLOSED,OPEN) … but that didn't work.

Any suggestions out there?

Reply

17 Scott Manning June 23, 2011 at 3:48 PM

Kevin, when you reference TODAY, you need parentheses afterward. In addition, non-numeric values require quotation marks around them.

=IF(Sheet2!V2=TODAY(),"CLOSED","OPEN")

I hope that helps.

Reply

18 Dale June 24, 2011 at 3:02 PM

I have a spread sheet that has a list of six peoples names in Column B and a list of items they sold in Column E

At the top of the spread sheet I each persons name . I want to have the names of the spread sheet populate based off of how many items each person sold.

Example,,, if Tom sold 8 items I want The sell beside Toms name at the top of the sheet to be filled in with the number 8.

I know i can filter to find this out, but I want to have each persons totals show at the top as well. Please help

Reply

19 Scott Manning June 24, 2011 at 3:20 PM

Hi Dale, it sounds like you can do a simple countif on the B column. I assume that anytime Tom’s name appears, it means he sold an item. As such, the following will return every instance where Tom sold something.

=COUNTIF(B1:B100,"Tom")

Be sure to modify the B1:B100 range to the appropriate range.

Reply

20 Angie June 29, 2011 at 3:11 PM

Dale, you’ve been wonderful in answering all of the above and I have the same problem with a little quirk. I want Cell S5 to turn red is Cell S4 = yes and S5 = no. How do I put that in conditional formatting?

Thanks so much in advance, you’re saving my job!

Reply

21 Scott Manning June 29, 2011 at 9:48 PM

Hi Angie, I am happy to save your job, but you have to stop calling me Dale. :)

You will need to utilize the AND function. This function allows you to analyze two separate statements. In this case, we are checking the values of S4 and S5.

The finall formula will look like the following:

=AND($S$4="yes",$S$5="no")

I hope that helps. Good luck.

Reply

22 Carol June 29, 2011 at 9:01 PM

Dale,

I would like to format a cell base on range of cells. I want it to check each cell against today. If date in the cell is less than today at least two times out of the full range of cells, I want to condditionally format the cell red

Reply

23 Scott Manning June 29, 2011 at 9:39 PM

Hi Carol, I can answer your question, but have to stop calling me Dale. :)

What you are describing is certainly possibly. The following formula assumes that your range of cells is E1:E8. It counts how many cells have a date less than TODAY. Then it determines if the total is greater than or equal to 2.

=IF((COUNTIF(E1:E8,"< "&TODAY()))>=2,"Greater than 2","Not greater than 2")

If you incorporate that into your conditional, use the rule type that says, “Use a formula to determine which cells to format.” Then input the following formula.

=(COUNTIF(E1:E8,"< "&TODAY()))>=2

I hope that helps.

Reply

24 Carol June 29, 2011 at 10:23 PM

Scott,

I had to modify the formula to be the following, because the color was not showing up.

=((COUNTIF(N6:T6,”=1)

This works great for less than today, but not equal to today.

Also, how would I do 10 days greater than today?

Also, is it possible to put 3 conditional rules. If those three are not true that it automatic does the 4 rule and formats the cell by the color identified. No condition?

One last question….I want to do one last condition that is greater than today but less than 10 days in the future.

All conditional formats. I sure you can tell that I am doing red, yellow, green, blue, and not N/A.

Thanks for your help.

Reply

25 Saurav Baidya July 12, 2011 at 12:45 AM

Dear Sir,

I had sent an email to you on 8th July as stated below, please give me a solution..
I have Microsoft Excel Starter 2010. I want to use some Redio Button for Selection of different Formula for different logic. I do not know how to make it in this new version. And I want to write some Macros in Microsoft Excel Starter 2010. Can you please help me how to do that. I really do not know how to do it. And I never worked with Macro. I need it very much. If you send some links that will be also too great for me!

Nice day to you!

With best regards,

Saurav

Reply

26 Scott Manning July 12, 2011 at 9:16 AM

Saurav, unfortunately, I do not have access to Microsoft Excel Starter 2010. It is a program that is only accessible on select new PCs. There is no way for me to download it and install it. I did a little digging and the program is very limited. From what I can tell, it has little to no macro capability.

Reply

27 Abhishek Sati July 13, 2011 at 1:39 PM

sir I want to compare a number in one column with its reference value in another column. i would like the color of the new value to change if there is change in the value from the reference value. How can I do it? Please help

Reply

28 Scott Manning July 17, 2011 at 1:54 PM

Hi Abhishek. What you described sounds straightforward. I will assume that you are comparing B1 to the value in A1.

=$B$1<>$A$1

The <> symbols means “not equal.” Use that as your code for the conditional formatting. If B1 does not contain the same value, then Excel will apply the conditional formatting you specify. I hope that helps.

Reply

29 Saurav Baidya July 14, 2011 at 12:41 PM

Dear Sir,

I am very happy to get kind reply! And thanks for spending time for my problem! It is very unfortunate that I cannot work with macro or the Excel Starter 2010 does not have any Macro capability. Do you have any other possibility or idea how I can implement in excel that user can select an option. Like calculating area of a circle or rectangle. If user choses Circle then it will calculate for circle, if for Rectangle then result will be for rectangle, where user can select only one option. Please give some idea or example. In Excel 97-2003 I had seen some of my colleague used some Radio Button or Check Box, I have no idea if it is possible to work with some graphic objects in Excel Starter 2010. I have only Excel Starter 2010 in my PC.

With best regards,
Saurav

Reply

30 Scott Manning July 17, 2011 at 1:56 PM

Saurav, unfortunately my lack of Excel Starter 2010 is really handicapping me to help you in this area. I was able to confirm that it cannot use macros, but I am not sure about data validation like you described. Sorry.

Reply

31 Saurav Baidya July 17, 2011 at 4:20 PM

Dear Sir,
Thanks for your reply! You are write Excel Starter 2010 is the basic, which does not have any macro programming facility. I have to by the full version. I am trying to find some different way.
With best regards,
Saurav

Reply

32 Beginner July 29, 2011 at 4:46 PM

I have a question regarding displaying cells used in a formula. Suppose A3=A2+A1. I know that double clicking on A3 will highlight A2 and A1. Is there any command also to do the same? I am NOT looking to display formula instead of actual values. I want to hightlight cells used in formulas. I appreciate your help.

Reply

33 Jesse August 5, 2011 at 3:27 PM

I am trying to put together a spreadsheet with Yes or No answers to a specific question. Is there a way to format all cells in column B to appear red if the corresponding cell in Column A (B4:A4 or B27:A27) has a “Y” in it? It appears that conditional formatting will only work with number values but it also makes sense that it should work for simple Y or N questions as well. Is there a way to go have a column or cell formatted by using non-numeric data in another column as the condition?

Reply

34 Saurav Baidya August 8, 2011 at 3:11 PM

Hi Jesse,

You can do this with conditional formatting in simple but tricky way as I also did in an application.
Depending on Y or N write to a cell 1 or 0 using IF conditional statement. Then you use this cell for your conditional formatting. Like in cell C1 use the formula
=IF(A1=”Y”,1,0) , where you write Y or N in cell A1.
Now use this C1 cell to format B1 for changing colour.

Please try and let me know.

Reply

35 Tricia August 17, 2011 at 11:41 AM

Scott,
I have made a simple conditional formatting to change colors depending on the text I have inputted into a certain column. We have a server at work and I placed that document on the server so my co-workers can use it. The color changes don’t happen when they are looking at it. But, if one of them enter data into spreadsheet, it does show up on my computer correctly. Is there a way for all of us to see the color additions?

Reply

36 Tricia August 20, 2011 at 6:55 AM

it was a glitch. It is working now.

Reply

37 Paul Robertson August 18, 2011 at 1:36 PM

Scott,

I’m trying to apply multiple conditions to our shift roster we have at work – currently I have 3 conditions which work fine if people are on leave/off/sick, but I’d like to expand this to cover all the various shifts etc we do – there are a good half dozen different shifts.

As an example, I’d like any cell on the worksheet that contains any one of “A/L”, “C/L”, “Off”, “TOIL” or “Sick” to turn red. Is there a function that could do this or am I hoping for too much?!

Thanks in advance!
Paul

Reply

38 Lisa August 25, 2011 at 2:44 PM

Scott,
I am working in Excel 2003 and want to have the following conditions:
First would be that if the value in column H is >=155 to have the cell turn red (I can do this).
Second would be that if the value in column H is between 130 and 154 to have the cell turn orange (I can do this).

When I put these two conditions in place my cells that contain text change to red, blank cells do stay “normal”. I tried to put a condition that said if the cell contained “1 HR” or “?” (the only other text in this column) to change the cell background to white. This however did not work. How do I stop these “text” cells from changing color?

Thank you for your help in advance.

Reply

39 CJCoon October 29, 2011 at 4:07 AM

Scott,

I just want to say thank you for taking the time out of your day to help those of us in need. Your direction on changing a cell fill based on the value of the cell was exactly what I needed.

Again….Thank you,

Chris

Reply

40 Tea February 23, 2012 at 12:49 PM

Aloha,

I need help. I’ve changed the text to change color when something is past due after today’s date; =NOW(). I am now trying to find out how to cancel that rule if I mark ‘X’ in another colume (I need the text to change back to it’s normal color).

I have been trying to figure this our in excel.

Please help!

Reply

41 Scott Manning February 23, 2012 at 1:02 PM

Hi Tea,

You will need to use Excel’s AND function. This function looks like this:
AND(logic1,logic2,...)

I am going to assume that the cell with the date is A1 and the corresponding canceling cell is B1.
=AND(A1>TODAY(),B1<>"X")

If the date in A1 is greater than today’s date AND there is not an X in B1, then the statement above is true.

I hope that helps. Good luck.

Reply

42 Tea February 23, 2012 at 1:42 PM

Aloha,
That didn’t quite work.
I have this rule for the G colume,
Cell Value< NOW()
Applies to, =$G:$G
Text color is yellow.

and also this rule for G colume,
Cell ValueTODAY(),I1″X”)
Applies to, =$G:$I
*for now text color is green but want it to be black when I get it to work.

This works for changing everything in colume G-I to green but it doesn’t change the yellow text and red text to green when I mark “X” in any colume of I.

I’m not sure if my explanation makes since to you but any HELP will do again.
I’d be happy to send you the document but I can’t attach it here. :/

Thanks so much.

Reply

43 Manojit Malla February 25, 2012 at 7:06 AM

Dear sir,

could you please suggest me to use conditional formatting condition formula writing for all bold or red coloured text to change to blue coloured text with bold format without using any number or value condition.

Thanks

Manojit Malla

Reply

44 Scott Manning February 25, 2012 at 7:12 AM

Hi Manojit, I am not sure I am following you. What are the conditions that should cause a cell to become red/bold or blue/bold?

Reply

45 Manojit Malla February 25, 2012 at 7:09 AM

Dear sir,

In my earlier mail, I forgot to state about excel version which are either Excel 2003 or 2007.

Thanks

Manojit Malla

Reply

46 Manojit Malla February 25, 2012 at 7:24 AM

Dear sir,

Actually, in my cost estimate spread sheet, I have marked few cells in one of the columns ( text & numbers) formatted with red colour and bold text. I wanted other adjacent column cells to have the same format by using conditional format condition. For instance, if that particular cell has red & Bold, i wanted same format in adjacent cell, if no formats used in the cells then the adjacent cells to remain unchanged.

Thanks

Manojit

Reply

47 Scott Manning February 25, 2012 at 7:38 AM

Manojit, unfortunately, I do not believe there is a way to conditional format in Excel based on formatting. Excel needs values for conditional formatting.

If you have some consistent, numeric-based criteria for coloring the first column red/bold, then you can use that same criteria to also color the adjacent cell blue/bold.

Reply

48 deb March 5, 2012 at 1:05 PM

still does not tell me how to change the problem if it says atleast….and not greater, less or equal…

Reply

49 djmoose May 24, 2012 at 9:21 PM

I am trying to do B2 is > then b1 answer is yes if not it says no, i am confused by the formula. I did Logical B2>B1,”yes”,”no” but when I go to do the highlight the yes only I am totally lost can you help?

Reply

50 Manojit Malla May 27, 2012 at 5:22 AM

For normal Condition, you have to use the formula =if (B2>B1,”Yes”,”No”) then only
if B2 is > it will show “Yes” else “No”.

Reply

51 godfrey June 20, 2012 at 12:45 AM

hi. i want to know how can i be able to creat the balnce of the material in the other spreadsheet to know the input and output balance. material to be in the store and the other to be used to the site

Reply

52 Marquint October 29, 2012 at 8:31 PM

Hi- I would like WHEN A DATE IS INPUTTED IN CELL b1 i want c1 TO CHANGE TO READ “CASE CLOSED”. How do I do that? I have a formula existing in c1 to count down the days a case has been open. but once someone closes it..it continues to count the days. I want it to just auto poulate”case closed” once rep places a dated this is the formula I have in c1 =DATEDIF(J3,L3,”d”) it’s not c1 though i just put that down to simplify…PLEASE HELP!!!
A1 B1 C1
DATE LETTER SENT iNVESTIGATION COMPLETED (DATE) days case has been open
12/5/2011 12/16/2011
12/12/2011 1/30/2012 49

Reply

53 melissa November 8, 2012 at 7:15 PM

how would i do a question like this with conditional formatting?

If the temperature goes up or down 5 degrees from the previous day, the cell background should turn red; otherwise, the cell background should be green

Reply

54 Sean June 3, 2013 at 2:57 AM

Hi Scott,
I have a question regarding Excel 2003, conditional statements.
Is it possible to have more than one result displayed if the condition is true?
For example, in one cell, say A5, I’ll select from a drop-down list “Fruit – Apple” or “Fruit – Pear”, etc…, or “Vegetable – Corn” or “Vegetable – Broccoli”, etc… or may choose to leave this cell blank all together
In another cell, say B5, I will have the store where it was bought, say “Store 1″, “Store 2″, etc…
If “Fruit – Pear” is selected, and “Store 1″ is also chosen, I want the information to be put together in another cell.
So, my formula would look similar to: IF(AND(A5″”,B5″”),A5……….,””)
This is where I would like it to display 2 results, A5 and B5, separated by a colon or space, so that it would display as “Fruit – Pear : Store 1″, otherwise it would leave the cell blank.
Is it possible to accomplish this?

Reply

55 Scott Manning June 3, 2013 at 6:41 AM

Sean, this is possible. However, this is not conditional formatting. Instead, it is a formula. Put the following formula in the cell where you want the results to display.

=IF(COUNTBLANK(A5:B5)>0,"",A5&" : "&B5)

This checks if one or more of the cells is blank. If not, then it displays the combined results of the cells.

Reply

56 Sean June 3, 2013 at 2:59 AM

In my last post, just now, it did not display the “” greater than or less than marks after A5 and B5 – not sure why

Reply

57 Dean November 11, 2013 at 9:54 PM

Hi Scott, Hope you can help,, I am trying to set up a worksheet for maintenance reasons, so when an item has been changed and a date inputted it will count down until its next due to be changed i.e. 6 monthly, 12 monthly so on, and to have it change colour from like (green = ok Amber = due Red = Overdue ) Is this possible

Thanks

Reply

58 Darby April 14, 2014 at 10:19 PM

Mr. Scott, I have the dreaded Excel Starter 2010…
Just interested in finding a way to count all the cells of a certain colour in this rather limited program! Obliged.

Reply

59 Teressa May 20, 2014 at 10:46 AM

I am trying to do conditional formatting when dates come past due to change color. I want it if the cell is blank or not past due to be white. If 10 days past the date in the cell to be one color and if date in cell reachs 30 or more days past due to be another color….. I can’t get this to work.

Reply

60 Natasha August 8, 2014 at 11:57 PM

Hi, I am looking to write a macro which searches through 4 columns that are named (Plant1, Plant2, Plant3, Plant4) and that are coloured (red, green, yellow or purple) and to return a number based on the combination. For example if Plant1 and red it will return 0, if Plant1 and green it will return 1, running from 0 to 16 for all the combinations. Thanks!

Reply

Leave a Comment

Previous post:

Next post: