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.

Everyone knows that changing the background color of a single cell or a range of data in Excel is easy as clicking the Fill color Fill color icon button . But what if you want to change the background color of all cells with a certain value? Moreover, what if you want the background color to change automatically along with the cell value's changes? Further in this article you will find answers to these questions and learn a couple of useful tips that will help you choose the right method for each particular task.

How to change a cell's color based on value in Excel dynamically

The background color will change dependent on the cell's value.

Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.

Solution: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and Y.

Suppose you have a list of gasoline prices in different states and you want the prices greater than USD 3.7 to be of the color red and equal to or less than USD 3.45 to be of the color green. A table listing gasoline prices in different states

Note: The screenshots for this example were captured in Excel 2010, however the buttons, dialogs and settings are the same or nearly the same in Excel 2016 and Excel 2013.

Okay, here is what you do step-by-step:

  1. Select the table or range where you want to change the background color of cells. In this example, we've selected $B$2:$H$10 (the column names and the first column listing the state names are excluded from the selection).
  2. Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule…. On the Home tab, in the Styles group, click Conditional Formatting - New Rule…
  3. In the New Formatting Rule dialog box, select "Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
  4. In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as you can see in the screenshot below. Select 'Format only cells that contain' and set the rule conditions.

    Then click the Format… button to choose what background color to apply when the above condition is met.

  5. In the Format Cells dialog box, switch to the Fill tab and select the color of your choice, the reddish color in our case, and click OK. In the Format Cells dialog box, on the Fill tab, select the background color of your choice.
  6. Now you are back to the New Formatting Rule window and the preview of your format changes is displayed in the Preview box. If everything is Okay, click the OK button. The preview of format changes is displayed in the Preview box.

    The result of your formatting will look similar to this: The background color of selected cells is changed based on cell values.

    Since we need to apply one more condition, i.e. change the background of cells with values equal to or less than 3.45 to the green color, click the New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule: A rule to change the background of cells with values equal to or less than 3.45 to the green color

    When you are done, click the OK button. What you have now is a nicely formatted table that lets you see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :) The background color is changed based on 2 conditional formatting rules.

    Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.

    The font color is changed based on 2 conditional formatting rules.

How to permanently change a cell's color based on its current value

Once set, the background color will not change no matter how the cell's contents might change in the future.

Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value's changes.

Solution: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

This is one of those rare tasks that are not covered in Excel help files, forums and blogs and for which there is no straightforward solution. And this is understandable, because this task is not typical. And still, if you need to change the background color of cells statically i.e. once and forever unless you change it manually again, proceed with the following steps.

Find and select all cells that meet a certain condition

There may be several possible scenarios depending on what kind of values you are looking for.

If you need to color cells with a particular value, e.g. 50, 100 or 3.4, go to the Home tab, Editing group, and click Find Select > Find…. Go to the Home tab, Editing group, and click Find Select > Find…

Enter the needed values and click the Find All button. Enter the needed values and click the Find All button.

Tip: Click the Options button in the right-hand part of the Find and Replace dialog to get a number of advanced search options, such as "Match Case" and "Match entire cell content". You can use wildcard characters, such as an asterisk (*) to find any string of characters or a question mark (?) to find any single character.

In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria: Use the wildcard character to find all gas prices between 3.7 and 3.799

Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl + A to select all found entries. After that click the Close button. Select all found items and click the Close button.

This is how you select all cells with a certain value(s) using the Find All function in Excel.

However, what we actually need is to find all gas prices higher than 3.7 and regrettably Excel's Find and Replace dialog does not allow for such things.

Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g. between -1 and 45, get the maximum / minimum value in a column, row or range, find cells by font color, fill color and much more. Select Special Cells add-in for Excel

You click the Select by Value button on the ribbon and then specify your search criteria on the add-in's pane, in our example we are looking for values greater than 3.7. Click the Select button and in a second you will have a result like this: Select all values within a particular range using Select Special Cells add-in.

If you are interested to try the Select Special Cells add-in, you can download an evaluation version here.

Change the background color of selected cells using "Format Cells" dialog

Now that all cells with a specified value or values are selected (either by using Excel's Find and Replace or Select Special Cells add-in) what is left for you to do is force the background color of selected cells to change when a value changes.

Open the Format Cells dialog by pressing Ctrl + 1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :) Change the background color of selected cells using the Format Cells dialog.

If you want to alter the background color only without any other format changes, then you can simply click the Fill color button and choose the color to your liking. Change the background color of selected cells by clicking the Fill color button.

Here is the result of our format changes in Excel: The backgrounds color of selected cells is changed permanently, regardless of the cell value's changes.

Unlike the previous technique with conditional formatting, the background color set in this way will never change again without your notice, no matter how the values change.

Change background color for special cells (blanks, with formula errors)

Like in the previous example, you can change the background color of special cells in two ways, dynamically and statically.

Use Excel formula to change background color of special cells

A cell's color will change automatically based on the cell's value.

This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.

We are going to use the gas prices table again as an example, but this time a couple of more states are included and some cells are empty. See how you can detect those blank cells and change their background color.

  1. On the Home tab, in the Styles group, click Conditional Formatting > New Rule… (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance).
  2. In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field:
    • =IsBlank()- to change the background color of blank cells.
    • =IsError() - to change the background color of cells with formulas that return errors.

    Since we are interested in changing the color of empty cells, enter the formula =IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button Collapse Dialog icon in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12). Enter the formula and select a range of cells.

  3. Click the Format… button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.

    The preview of your conditional formatting rule will look similar to this: A rule to change the background color of blank cells using a formula

  4. If you are happy with the color, click the OK button and you'll see the changes immediately applied to your table. Click the OK button and you'll see the changes immediately applied to your table.

Change the background color of special cells statically

Once changed, the background color will remain the same, regardless of the cell values' changes.

If you want to change the color of blank cells or cells with formula errors permanently, follow this way.

  1. Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button. Open the
  2. In the "Go to Special" dialog box, check the Blanks radio button to select all empty cells. Check the Blanks radio button to select all empty cells.

    If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.

  3. And finally, change the background of selected cells, or make any other format customizations using the "Format Cells" dialog as described in Changing the background of selected cells.

Just remember that formatting changes made in this way will persist even if your blank cells get filled with data or formula errors are corrected. Of course, it's hard to imagine off the top of the head why someone may want to have it this way, may be just for historical purposes :)

How to get most of Excel and make challenging tasks easy

As an active user of Microsoft Excel, you know that it has plenty of features. Some of them we know and love, others are a complete mystery for an average user and various blogs, including this one, are trying to shed at least some light on them. But! There are a few very common tasks that all of us have to perform daily and Excel simply does not provide any features or tools to automate them or make an inch easier.

For example, if you need to check 2 worksheets for duplicates or merge rows from single or different spreadsheets, it would take a bunch of arcane formulas or macros and still there is no guarantee you would get the accurate results.

That was the reason why a team of our best Excel developers designed and created 70+ add-ins that we call the Ultimate Suite for Excel. These smart tools handle the most grueling, painstaking and error-prone tasks in Excel and ensure quickly, neatly and flawless results. Below is a short list of just some of the tasks the add-ins can help you with:

Just try these add-ins and you will see that your Excel productivity will increase up to 50%, at the very least!

That's all for now. In my next article we will continue to explore this topic further and you will see how you can quickly change the background color of a row based on a cell value. Hope to see you on our blog next week!

Table of contents

426 comments

  1. 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

  2. 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?

  3. 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.

    • 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

      • That works, Thank You :)

  4. 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

    • 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.

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

  6. 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?

    • 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.

  7. 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

    • 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.

  8. 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?

    • 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)

  9. 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?

    • 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.

  10. 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

    • 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).

      • 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.

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

  12. my problem out side of your presentation

  13. 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!

    • 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 : )

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

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

  14. Hi Svetlana,

    Thank you for the post. I have a follow-up question. I have two columns and I want to compare the two values for row 1 in each column for dozens of rows. I want column A's cell to change color if it's value is less than the corresponding cell in Column B. For example if A1=4 and B1=5, then I want A1 to turn red and I want to do this for each cell in A1:A50 compared to each corresponding cell in B1:B50. Is there a way I can do this all at once instead of one cell at a time?

    Thanks,

    Matt

    • Hi Matt,

      Of course, you can do this at once using the conditional formatting. Select cells A1:A50 and create a new rule (Conditional formatting > New rule > Use a formula to determine which cells to format). Enter this formula =$A1<$B1 and choose the red fill color. Done!

      If you want to do the same in a table that has headers (data starts in row 2), then simply change A1 to A2 and B1 to B2 in the formula.

      • Hi Svetlana,

        Thank you, but that only worked for the first cell (assuming I didn't do something wrong). How do I get it to compare A2 to B2 and C2 to C3, etc.? I'm used to using formulas where I could create the formula and then just copy the formula to all of the cells. Is there a similar way to do that with this conditional formatting?

        Thanks again,

        Matt

        • Hi Matt,
          Yes, it works in a similar way with conditional formatting. Instead of copying the formula to other cells, you select the entire range that you want your formula to apply to, and use a mixed cell reference, i.e. an absolute column and relative row (e.g. $A1 and $B1).

        • I figured it out. Thanks for your help.

  15. how do you do the above using drop-down menus

    • Hi Eric,
      Sorry, I do not exactly understand your question. I do not know any way to change the background color in Excel other than by using conditional formatting.

    • it is shorting ....

  16. hi , actually i have 100 cells out of which 10 contains value as 0,
    and others are any number ...i have used some formulas , as a result of which 0 value has been entered in the cells,, i want to change the colour of cell containing
    0 when its value changes to some new value other then 0, at the same time i do not want to change cell colour of my existing cells containing values other then 0..
    pls help

    • Hi Sameer,

      Select your 100 cells, press Ctrl+F, enter 0 into the "Find what" field and click the "Find all" button. Then switch to the list of found values and press Ctrl+A to select them all. After that create a conditional formatting rule by choosing "Format only cells that contain" (the rule will apply to the highlighted cells only). Under "Format only cells with" choose "Cell value", "not equal to", 0. Press the Format button to choose the fill color of your liking and you are done.

      • great helpfull

  17. i require a range of cells to turn color when the value of a non adjacent cell reaches a specific value, example;
    cells b2,b3,b4,b5,b6 to turn red when cell f12 is greater than 20

    • Hi Gln,

      Just select the needed cells (b2:b6) and create a new rule with this formula =$F$12>20.

  18. Hi Dinesh and Hafiz,

    Thank you for your comments! It's really nice to know that my article was helpful.

    • thanks a lot

  19. thanks i found my problem solution.

  20. Its very usefull for me

    • No i Can't understand

    • Very easy and Usefull, $nx...

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 :)