Comments on: Two ways to change background color in Excel based on cell value

In this article, you will find two quick ways to change the background color of cells based on value in Excel 2016, 2013, and 2010. Also, you will learn how to use Excel formulas to change the color of blank cells or cells with formula errors. Continue reading →

Comments page 13. Total comments: 426

  1. Thanks a lot
    it was so helpful for me

  2. Can you use this to change a cells color based on a word in the cell instead of a value?

  3. Hi,
    Is there a way to select a random cell in excel and have all the other cell with the same value highlight? So, once I select another cell, the originla highlight is lost and all the cells with values same as the new selected cell gets highlighted.
    I need this for work to make my presentation look spot on and set me apart from the rest. If you could reply back, I would very much appreciate it.
    Thank you!
    Shweta

  4. Svetlana,
    Thank you for your post. It is very useful. You seem to be an expert in excel color and I wonder if I can ask you a question about Excel color. I inherited an excel file with a pale yellow/sand color. When I check the color index, it shows -4142. When I change the color of the cell, it shows the normal color. When I set as no fill, I get this color (-4142). When I print the sheet, the color displays similarly as a no filled cell. I wonder if you know how this -4142 "color" gets set in the first place?
    Thank you in advance.

    1. Hello Tina,

      I am hesitant to give any advice without seeing the formatting. If you can send us your workbook at support@ablebits.com, we will hopefully be able to help.

  5. Thank you very much

  6. Hi Svetlana,

    I am very impressed with the creative solutions I have seen on this page.
    I am hoping you can help me with a problem I have been struggling with for months.

    I would like to copy the contents and format of a column of cells and paste them in another column using a formula.
    All columns are formatted as type = General
    Column A is a list of random IP addresses that are color formatted as green or red cells. Just say every other IP address is green and the others red.
    Column C is also a list of IP addresses (a master list) with no color.

    In column B, I want to take all the IP addresses in column A and line them up on the same row as the IP address in column C (and keep the color formatting from column A).
    In column B to get the IP address from column A to line up with the IP addresses in column C, I used the formula:
    =IF(ISNA(MATCH(C1,A:A,0)),"",INDEX(A:A,MATCH(C1,A:A,0)))
    This works but column B does not have the color formatting as the cells in column A
    Is there any way to keep that formatting when using a formula?

    Here is the data (remove column B if you want to check the formual):
    192.168.36.21 192.168.36.21 192.168.36.21
    192.168.32.31 192.168.33.174
    192.168.35.0 192.168.32.31 192.168.32.31
    192.168.37.56 192.168.40.163
    192.168.45.10 192.168.42.82
    192.168.43.221 192.168.35.0 192.168.35.0
    192.168.45.108 192.168.37.56 192.168.37.56
    192.168.46.208 192.168.38.105
    192.168.32.226 192.168.38.214
    192.168.39.208 192.168.43.224
    192.168.32.246 192.168.45.10 192.168.45.10
    192.168.35.109 192.168.43.221 192.168.43.221
    192.168.34.99 192.168.46.215
    192.168.32.55 192.168.45.108 192.168.45.108
    192.168.46.208 192.168.46.208
    192.168.32.226 192.168.32.226
    192.168.43.42
    192.168.39.208 192.168.39.208
    192.168.32.246 192.168.32.246
    192.168.34.23
    192.168.36.68
    192.168.35.109 192.168.35.109
    192.168.34.99 192.168.34.99
    192.168.32.55 192.168.32.55

    Maybe this is not the right approach and using some other way like Offset would be the way to go. Any input is welcome

    Thank You for your valuable time,
    Sven

    1. Thank you, Thomas! Much appreciated : )

  8. Hello,
    I'm trying to set up a sheet where if I enter something in A (will be a number) then B,C,D,E turns red until something is entered into each one.
    Once a value, or word is entered in each of B:E they turn back to white.
    Is this possible? It seems like trying to connect 2 formulas may be contradicting

  9. I am partially red/green colour blind and rely on the labels that appear when I use Fill on the Home ribbon (pause over a colour and the label appears). But those labels do not appear when I use Fill from the conditional formatting feature. How do I get them to appear when using conditional formatting? Also how do I get labels to appear in 'more colours'?

    1. Hello Kevin,

      I am very sorry, I don't know a way to make the labels to appear for conditional formatting colors.

  10. Hi Svetlana

    I have a range of values in Row1 - say A1 to J1
    I have another range of values in row2 - say A1 to J2.
    I am comparing A1 and A2; then B1 with B2....... then J1 to J2.
    Whenever value in row1 < row 2, the row2 cell should be changed to red color.
    This is needed to be done across the entire row 2.
    Kindly let me know the solution.

    1. Thank you Svetlana!!

    2. Hello Sridhar,

      If you have to highlight each cell in row 2 that has a value greater than the value in row 1 in the same column, then select row 2 and create a rule with this formula:
      =A2>A1

      If you have to highlight the entire row 2 if at least 1 cell in row 1 has the value less than in row 2, you need a helper cell, e.g. A5, with the following array formula (remember to press CTRL+Shift+Enter instead of Enter to complete it):
      =SUM(--(A1:J1<A2:J2))

      After that, select row 2 and create a rule with this formula:
      =$A$5>1

  11. Hi,

    I have applied conditional formatting to change the color of few cells to red in a row based on a value. After applying conditional formatting,if i copy and paste some data to these cells, the background color changes to white. Can you please help?

    1. Hello Satish,

      When you use the standard Paste command, the current rules are replaced with the ones applied to inserted cells.

      You need to use the Paste Special option instead: right-click on the destination cells, select Paste Special from the context menu and choose the Values or Formulas radio button under Paste.

  12. Thank you so much, it is very useful!
    I also want to create legend after doing these way, how to create leagend?
    Thank you indvance!

  13. I am tracking training of people on my project. All of the training they take has to be retaken annually.
    Based on the completion date in a cell I want to turn the cell GREEN if the number of days fall within 1 to 334 days from the completion date. I want to turn the cell YELLOW If the number of days fall within 335 to 364 days from the completion date. I want to turn the cell RED if the number of days is 365 or more from the completion date.

  14. In col. B2- 100. In col.A3-101,A4-102,A5-103. In col.C3-99,C4-98,C5-97.
    when col.B2 there are 101 i want green color in A3.
    when col.B2 there are 102 i want green color in A4.
    when col.B2 there are 103 i want green color in A5
    & Viseversa
    when col.B2 there are 99 i want red color in C3
    when col.B2 there are 98 i want red color in C4
    when col.B2 there are 97 i want red color in C5

  15. i want to color some selected cells based on cell color
    please let me how to do it

    Thanks

  16. i want to color selected cell based on cell color

  17. hi the post is very helpful to me bcoz I have learnt a new thing but I got one question how do you do if you want to color the cell after entering the value maybe less than 3 to color yellow and greater than 3 or empty cell to remain with default color

  18. Lets say I have a formula in a cell from another page in the workbook. The cell in the other work book is a drop down. Is there any way when that drop down is used/filled to make the cell on the second page change color to show its populated?

    1. Hello Steph,

      It looks like you need a special macro to fulfill this task. I am really sorry we can't help you with this.

  19. Thank you, this topic is helpful

  20. Is there a way to format a column of numbers to highlight only when another column has an "X" in that row?

    1. Hi Patricia,

      Of course, there is a way. Simply select the column you want to highlight, not including the header and create a rule for it with this formula:
      =A2="X"

      Where A2 is the first cell with data in the other column, again not including the header row.

  21. You're the best! Thank you doll

    1. Dear Svetlana Cheusheva,
      Can we color a cell based on result. In detail, I am using a formula (=min(various nos of cell in a particular row) to find the lowest figure , Like ( =MIN(W7,S7,O7,K7,like so on))and for example result is W7 cell and I want to automatically color the W7 cell. By conditional formatting it can be done by selecting entire cell range, but problem is I have to more than 40-50 row and I have to repeat the same rule for each row. Is there any help for me. Pls guide.

      1. Hello Rameswar,

        You can select all the rows and create the conditional formatting rule with the formula =A7=MIN($W7, $S7, $O7....)
        Where A7 is the first (top-left) cell of your range.

        But I'd recommend creating a helper column (hidden) and copy the following formula there: =MIN($W7, $S7, $O7....) And in your conditional formatting rule, use the formula =A7=$AB7, where A7 is the top-left column and AB is the helper column.

        If you have a large data set, this rule will work much faster because Excel won't have to calculate =MIN($W7, $S7, $O7....) for each cell where the rule is applied.

  22. Thank you for your response, but i might not have explained it well enough.
    I have 3 cells that need to change rules according to what i have selected from a drop down list. So, if i have "refrigerator" selected from the list, the 3 cells need to have one rule for that selection, and if i select "freezer" the same 3 cells need to have another set of rules. Not sure how to do it.

    Thanks for you help though! :)

    1. Hi Ron,

      Regrettably, Excel conditional formatting rules cannot be changed depending on the value selected in the drop down list (this can be done using VBA only).

      As an alternative, you can create several rules for the same range of cells, one per each value in the drop-down list, that will check the contents of your drop box and color the cells you want depending on the drop-down list's value and your additional conditions.

  23. Hi,

    Can I have a complete row highlighted in any color based on if there is any change (addition or deletion) of data from the exiting value?

  24. very nice , thank you.

  25. Please Please Help!!!
    My cell C1 has to say "yes" if A1>100 and B1100 and B1>70.
    please help how to make this formula ??? My head is broken by now :(

    1. Hi Sergey,

      Here you go :)

      =IF(AND(A1>100, B1100>70, B1>70),"yes","")

  26. I am creating a task list and I would like to change the fill color of the row depending on to whom the task is assigned. I have created the drop-down list and used conditional formatting to assign colors to the individuals names. Now when I select a name from the drop-down list the fill color changes for that cell. How can I get the corresponding cells in the row to change to the same color?

      1. Thank you for taking the time to assist me. It is much appreciated.

  27. i want to color some cells based on different cell's condition. I've done it but the problem is when i close the document after saving and open again then i see that the condition has been erased. but why??

    1. Hi Tanjin,

      This is absolutely abnormal! Please make sure you save the workbook after creating the rule.

  28. Sorry, i meant color coded values

    1. Hi Ron,

      You can select a group of sells you want to color (e.g. D10:D12) and create 2 rules with different fill colors based on these formulas:
      =$D10="Refrigerator" and =$D10="Freezer".

  29. How can you make a group of cells change their rules based on what you select from a drop down list??

    I have a drop down list consisting of "Refrigerator" and "Freezer". I have coded values in "D10-D12" that need to change based on whether i have "freezer" or "refrigerator" selected.

    Thanks!

  30. Hai,

    I have a trend monitoring datasheet . Each kind of trend refers to 1 - 100 numbers. i need to highlight the trend if it occurs more than 2 times.
    Eg. Trend No.1 - BY Road, 2 -By shop, 3 -By School ... up to 100 numbers. If trend No.1 occurs two times, it should highlight. Can you please help me.

    1. Hi Anil,
      To be able to suggest a proper formula, I need to know more details. Is each trend a column or a row? Do you want to highlight certain cells or entire rows? Please send me a workbook with your sample data at support@ablebits.com and I will try to help.

  31. Hi,

    I want to change the cell colors in col A based on the values in another cells say Col C. Here, i have conditions that if the values in Col C are in the range <% then green, if + % then yellow otherwise Red..how to get it done!!

    1. Hi!

      You can select all cells with data in column A, not including the column header, and create 3 conditional formatting rules based on the formulas like these (assuming that row 2 is your first row with data).
      Green: =$C2<50%
      Yellow: =$C2>60%
      Red: =AND($C2>=50%,$C2<=60%)

      For more details, please check out this tutorial: How to change a cell’s color based on another cell.

  32. I am working with recertification dates of about 100 people. I have columns with recertification dates with an adjacent column that allows me to see if personnel are current or overdue. These cells change color based on that value. Their are several columns of data between the name of the indivual and their status. I would like to highlight names of individuals who are overdue. Is there a way to reference another cell so that when they go overdue it highlights their name and when they return current the highlighting goes away?

    1. Hello Matt,

      You can do this by creating a conditional formatting rule based on a formula. Select the column you want to highlight a create a rule using a formula similar to this: =$C2="Overdue" (where C is your status column and 2 is your 1st row with data). You can find more information about conditional formatting based on another cell here: Change a cell’s color based on another cell value.

  33. Hi Svetlana,

    I am struggling to figure out how to set a value range in my spreadsheet to correlate with specific colors when a value is input in the cells. For instance, I want the cell color to be blue when the value input into the cells is less or equal to 2.0, and if the value is 3.0 - 4.0 the cell will turn green, and if the value input into the cell is greater than or equal to 4.1 it will turn pink. Can you please help?

    Thank you in advance for any assistance you can provide.

    Julie

    1. Hi Julie,

      You can easily do this by creating 3 separate rules in this way:
      - Select all the cells you want to color;
      - Click Conditional Formatting > New Rule > Format only cells that contain;
      - Select the needed option from the drop-down list (2nd box from the left) for each rule - "less or equal to", "between", and "greater than or equal to";
      - Click the Format button and choose the format you want for each rule.

      You can find the detailed guidance with screenshots here - Creating an Excel conditional formatting rule. Hopefully you will find the info helpful.

  34. This has been so very helpful. Thank you for sharing your wealth of knowledge. I would love to know how to conditionally format a cell colour based on whether a different cell is NOT blank. I have perfected the blank value, how do I do the opposite?
    I am also curious if it is possible to use the icon sets to show based on the value (or the blank versus containing data) of other cells. An example, a check list of items that spans a row, a check mark at the front of the row to show all columns have been filled in (contain data, any data) or an exclamation in the first cell to show that there are some cells that are blank, or an x if all cells in that row are blank. Is that even possible???
    Thank you for your time

    1. Hi Michelle,

      You can create a rule with the following formula: =$B2="" It will color cells in the selected column(s) if a corresponding cell in Column B is not blank. Naturally, you will need to replace B with the column you need, and "2" with the row number where your data starts.

      As for the icon sets, I described a solution exactly for this task in my latest article - How to use conditional formatting in Excel. You can go directly to the section How to apply an icon set based on other cells' values. Hopefully, this is what you are looking for.

      1. Oh my gosh that icon set creativity is brilliant!!! Thanks Svetlana!!

  35. thanks for supporting

  36. How to change colour of entire column ( if > green &if < red)comparison to other column ?

    1. Hello Rasendra,

      This is a bit unusual to change the color of the entire column, most other users asked how to change the color of the entire row. Anyway, if this is your task, you can change the color of the whole column in this way:

      - Select the entire column, say column B.
      - Create 2 rules with these formulas: =$B$2<$A$2 and =$B$2>$A$2 (we are comparing values in row 2).

      Make sure both formulas are applied to the entire column, say =$B:$B.

  37. Hi, never mind previous request, I found the answer in another of your articles. Also very good, by the way.

    1. Thank you for your feedback Justine! I am really glad to know you found the articles helpful.

  38. Hi Svetlana, great, easy to understand instruction. I hope I haven't missed this, but I can't see how to color an entire row based on the value in one cell for a whole page of rows. For example - value in A2 is A so row is red, value B makes the row blue, etc. It will take a new rule for each value/color combo? Thanks, Justine

  39. how to fill color by getting two values from two cells respectively.
    For ex..
    1 2 3 4 5 6 7 8 9
    2 6 then from 2nd to 6th column have to fill color.
    1 5 then from 1st to 5th column have to fill color.
    can u pls help me?

    1. Hi Charu,

      I am sorry I do not exactly understand the task. Can you please send us your source data and the desired result at alexander.frolov@ablebits.com? We will try to figure this out.

  40. Hi Svetlana,
    Amazingly good post.
    I've one question as well.
    A3 X
    A4 X
    A5 Y
    A6 Z
    A7 Z
    A7 X

    I'm trying to compare two cells for entire (Current and Preceding) and if they are not equal then fill with some color for the cell which differs from its previous one. I'm using Conditional formatting using a formula - =A2A3

    Result is it fills color for A4 instead of A5 and A7 Instead of A8.

    1. Hi Santosh,

      Thank you! You simply need to use the cells' addresses in your formula in the reverse order. So, select the needed range, say A3:A8, and create a rule with this formula:
      =A3<>A2

      1. That works, Thank You :)

  41. Hi Svetlana,

    It's really helpfull article. I appriciate the way you described about the features of Excel.I am working on Excel services using O-data and I am stucked in one point.
    Like in given example there are total 10 rows and you did formatting for that.
    I want to add 4-5 extra rows in that same sheet.So is there any way that the cells having values greater than 3.7(given in your example) should come in reddish color and cells having values less than or equals tp 3.45(given in your example) should come in green color for extra rows automaticaly without again selecting the entire table.

    Hope you can help figure this thing out!

    Thanks

    1. Hi Vishesh,

      Usually Excel is smart enough to apply the existing conditional formatting rules to the newly added rows. If this does not happen, then you can either:
      - When creating a rule, select some more empty rows below your data, say 100 - 200 rows, or
      - Convert the range to table (Insert tab > Table). In this case, the existing rules will definitely be applied to all new rows you add to the table.

  42. thanks so much for this useful and well exmapled article, saved me hours on what i was about to do. you're awesome!

  43. HI Svetlana,

    Useful post and perfect answers :)
    1 Query - i want to format cells in a column with colurs based on the vloopkup value from other sheet.
    How can this be acheived?

    1. Hi Adil,

      Thank you for your kind words : )

      You can use the Vlookup function in the formula, the conditional formatting rules support it. Here is an example:
      =VLOOKUP($A2,Lookup!$A$2:$C$100,3)>20

      Just keep in mind that Vlookup is a resource-consuming function and it may slow down your Excel if you have large tables.

  44. Dear SC,

    Thanks for your article know i know how can i use the conditional formatting for filling the color in an specific column, But could you please tell me how i can fill a color in a specific column based on the value of an another column
    With your quoted example how you can color the name of states A1,A2,A3 based on the value of mentioned in the column B1,C1,D1

    1. Hello Radhakrishnan,

      You can color the names of states in column A based on values in columns B, C and D in this way:
      - Select the cells you want to color (A2:A12 in our example)
      - Create a conditional formatting rule based on formula, e.g. =AND($B2<3.5,$C2<3.5,$D2<3.5) It will color the states with gas prices less than 3.5 in columns B - D.

      For more information, please check out my other article: How to change a cell's color based on a value of another cell. Hope this helps.

  45. Hi,
    For example A1 & B1 both cells values should be same. If it's not true, then C1 cell background need to be change! can you help me?

    1. Hi Saran,

      Just select all the cells in column C that you want to change the background color and then create a conditional formatting rule with this formula:
      =($A1<>$B1)

  46. I need some help here. I am trying to change the font of Cell A2 red based on B2 being blank. How can I work that out?

    1. Hi,

      Select the cells you want to highlight in Column A and create the conditional formatting rule based on this formula: =B2=""
      And then choose the needed font color on the Font tab of the Format Cells dialog.

  47. Hi,

    I would just like to fill a cell with a color based on a %. Say fill a cell 50% with a color as a graphic representation of the amount. Is that possible in excel?

    Thanks

    Vinny

    1. Hi Vinny,

      Of course, you can do this using the conditional formatting. The easiest way is to select your table or a range of cells, click the Conditional formatting button > Highlight Cells rules > Equal to..., specify the percent you want to color, e.g. 50%, and choose a fill color from the drop down list (click Custom Format... if you want more colors).

      1. Can this be done with a gradient as well? For example setting RED for 0% and GREEN for 100% so it does the colours by itself according to the result of the formula.

        I'm using this and I would like the result of this cell to change its colour.

        =COUNTIF(E4:E37, "SI")/34

        Thank you.

  48. Thanks I was able to find my answer / straight to the point

  49. my problem out side of your presentation

  50. Hello Svetlana,

    I'm having a trouble with conditional formatting, I've been looking for solutions several days but still can't figure out.

    I use "between" rule to highlight cells. But is there any way that I can highlight cells with 0 and ignore blank cells? Because when I choose highlight between "0" to "46" (for example), blank cells are also highlighted.

    I try to use 3-scale color, it can ignore blank cells but unfortunately my data will be much better if I use "between" rule to highlight.

    Hope you can help figure this thing out! Thank you very much!!
    Best regards!

    1. Hi Thinh,

      You can do this by creating a second rule of the "Format only cells that contain" type. In the 'New Formatting rule' dialog window, click the little arrow next to "Cell value" box and choose Blanks from the drop-down list. Then simply click OK without setting any format. Finally, click Conditional Formatting > Manage rule on the ribbon and select the option "Stop if true" next to the Blanks rule. Also, make sure it is first in the list, if not, move it to the top as explained here . Hope this helps : )

      1. Thank you very very much!!! You are such a genius! I've been searching for days with no result! Your solution works exactly as I hope it will!

        So much thank you for your help! You've just saved my thesis :)

        Best regards!!

        1. I'm glad to know it worked for you. Good luck with your thesis! : )

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)