by 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 
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.
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:
- 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).
- Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule….
- 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.
- 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.
Then click the Format… button to choose what background color to apply when the above condition is met.
- 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.
- 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 result of your formatting will look similar to this:
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:
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 :)
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.
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….
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:
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.
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.
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:
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 :)
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.
Here is the result of our format changes in Excel:
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.
- 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).
- 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
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).
- 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:
- If you are happy with the color, 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.
- Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button.
- In the "Go to Special" dialog box, 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.
- 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:
- Remove duplicates and find unique values
- Merge tables and combine data from different sources
- Combine duplicate rows into one
- Merge cells, rows and columns
- Find and replacing in all data, in all workbooks
- Generate random numbers, passwords and custom lists
- And much, much more.
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!
422 comments
Sir, i have a one excel file that has lot of datas in it. my question is when you are entering in the cell it's turned to yellow.....only entering time that's all after that it was same like other ordinary file. but i strongly believe that it is not a conditional formatting. and i have checked cell style also i am not able to get it.
Please help me to find this.....without knowing this i cannot able to sleep.
Thanks in advance.
Regards,
Nirmal
hello,
can anyone help me please? I'll be quick and brief.
I need that cells B2 to B35 get colored in yellow with red text automatically, as soon as cell B1 reaches value 30.
If value in cell B1 is less than 30, nothing must happen. Only once it reaches 30 the cell B2 to B35 need to become yellow with red text.
How can I do that?
Thanks in advance!
Hi! All the necessary information is in the article above. Try this formula:
=$B$1>30
When
Value of column A Less then Value of column B then column B red
How to create this Formul in excel
Hi! Please read the above article carefully. Also, this article will be helpful: Excel conditional formatting formulas based on another cell.
DEAR SIR,
If I change the color of a1 then the color of b1 also change same a1, and all other cell like a1 and b1.
thank a lot for your help
Hello,
Please I need some help and hope to get it from here;
I need conditional formatting such that;
1. if I enter "SUNDAY" in cell A2, the cells A2:E2 should be colored yellow
2. if I enter a "text" on E3, a serial number in the manner "yy-mm-dd/hh.mm.ss" should be auto generated in cell B3 based on the date in A3 and the time (00:00:00 in real time) the data "text" is being entered.
3. if cells A2:A5 has the same dates, and I enter a new date in cell A6, cells A6:E6 should be colored blue
Whenever I used to fill color in a cell it changes automatically.
How do I set the conditional formatting for a drop-down list in order to change background colour whenever I chose a cell value within a selceted range. e.g. I have a list of names from one category (all employees of company A) and another list of names (all employees of comany B). All those names appear in the drop down list but I want to assign green color for all employees from company A and red color for all employees from company B.
Appreciate you help!
Unfortunately, conditional formatting for the drop-down list in Excel is not possible.
How do you apply a =cell<cell logic across a large data set?
Example: I want to conditional format each cell to compare to the same cell in another set of data on the same sheet. The format cell should be red.
E3 needs to compare to U3
E4 needs to compare to U4
F3 needs to compare to V3
F4 needs to compare to V4
G3 needs to compare to X3
G4 needs to compare to X4
Hi!
Select the range of cells you want to highlight with color and create a conditional formatting rule as described in these instructions: Conditional formatting formulas to compare values (numbers and text).
I set a conditional format to reflect a Yellow fill. When I do a Format Cells, to check the color of this cell, I select Fill and More Colors. BLACK appears as the Current value. Why isn't Yellow appearing? How can I test that the background color is Yellow so I can have the user review the field(s) with a Yellow background.
Hi,
Is it possible to get filled cell color by HEX code?
Suppose I have a HEX code like #F0F8FF in written form in an excel cell. I want to convert or filled cells by their value.
Hello
I have some sort of a Gantt chart which fills automatically the calendar cells with a color base on conditional formatting. The formula that I am using is
=AND(NOT(ISBLANK($E8)),$E8=M$6)
Would like to add an additional criteria to change the color of the filling base don another cell value ie R for red, B for blue, etc.
How could I add that ?
Regards
Just wanna say Thank you. I am using Excel 2021. To others, just read the above and view the images provided by the author and everything will work perfectly. The Condition Formatting work great for my Realtime Bitcoin and Crypto Excel Sheet. I just condition format 2 columns only under the title Beer or Vodka. It either reflected green or red in Realtime. If green, I am happy and drink beer. If red, I drown myself with Vodka and I go to sleep. Anyway, thank you very much.
I have prepared a sticker format in excel and returned value using lookup function to fill the sticker details. In one of the cell the value returned shows the category (Color) of the sticker (eg: Purple) , I wanted to change the whole sticker (meaning, the complete sticker range) color according to the color of the value returned (here, Purple). is this possible?
Hi!
You can change the color of a cell by the name of the color that is written in another cell using a VBA macro.
Also, you can use conditional formatting by formula
A1="Purple"
Based on this condition, create a conditional formatting rule with the desired color. For each of the possible colors, create a separate formatting rule.
This did not work at all. I don't know if its for an older version? But when I format a new rule, I see a new rule, but it doesn't actually do anything. So if I set the rule that anything higher than 175 should be green. It does not change the color. I would like to change the actual color of the cell so when you are looking at it, it looks different than the other cells. I hope that makes sense. Like if I have value of 125 I would like it to be red, but then if the formula changes and the equation is now a value of 175 I would like it to be green. Is this not possible anymore?
Hi!
I can't guess what didn't work for you, what formula you used, and what data you have. The advice in this article works great. If there is a problem, describe it in detail.
Using the find box in excel, once I type in the data and press find. Once found then turn the cell red. All data in cells is different
Hi!
If this is a problem, please describe it in detail. Maybe this article will be helpful: Using Excel Find and Replace in the most efficient way.
Thank you
thanks for the important article.
Thank you so much! We used to hard code similar features, this is much more robust and convenient. I can't thank you enough through words. =)
thanks alot
Hi, Thanks for your guidelines, very helpful.
I have changed colours of cells as I wanted to. However do you know how I can then make the values disappear and only have the colours come up on cells?
Hi!
Your question is not entirely clear, please specify.
Hi Lucky, Chose same color for Fill & Font in your conditional formatting settings. That will help you showing only colors based on value, no text.
Hi,
If I have a number in collum a, let's says 100, and whatever the number is in collumn b id like to change the colour of based on the following:
If 30 % or less than collumn a number turn it red
If 30 to 60 percent of column a turn another colour aka orange
If 60 or more percent turn another colour aka green
So if colum b value is
20 = red
50 = orange
605= green
Many thanks!
Hello!
If I understand your task correctly, the following tutorial should help: Excel conditional formatting Color Scales.
Hi, how can i change the fill color by using formula for the below
IF my Cell no F16 is X i want to fill entire row 16 as one specific color
can any one help
Hi!
Please have a look at this article — How to change the row color based on a cell's value.
I hope it’ll be helpful.
Hi, thank you for the instruction.
My problem is, can it change color if it less/more than the month before.
for example,
if C2 is higher than B2 then cell C2 is red, if lower then C2 is green
if D2 is higher than C2 then D2 is red and so on.
Hi!
Please re-check the article above since it covers your task.
Follow the instructions in the first paragraph of this manual. Specify Cell Value - greater than - B2. Or use the formula =C2>B2
How is it possible i complete the entry in a cell and this row automatically colored
Hi, I did conditional format for the cell and i could find changes in my system.
When it opened in some other system, these changes are not refelecting even though formula available for the said cell,
Hi,
Can we highlight a cell based on cursor position without using vba, because i need it on spreadsheet.
(if I move or select a particular cell, that cell will get changed to color or highlighted). Can anyone help me for this issue,
Thank you,
Hi!
Conditional formatting works with formulas. But there is no formula that determines the position of the cursor.
I have set the rule to change the color of the cell. It's working fine. However, by the same rule, is it possible for me to change the font color as well?
Hello!
You can do this when creating or editing a conditional formatting rule. In the Format Cells dialog box, switch to the Font tab and select the color of your choice.
Hi there,
my problem is a bit different. I want to highlight updated cells in a pre filled data. E.g. I already have a set of data, but due to some reason any one or few cells with existing data may get modified (by someone else in my team), I want to apply conditional formatting to my sheet so that those updated cells get highlighted by its own. So that, next time when I look into my data, it will show me the changes.
Hello!
The Excel formula cannot determine the date the cell was modified. Therefore, conditional formatting using a formula is not possible. Perhaps this article will be useful to you - How to track changes in Excel.
I want to turn a cell into Green when I type 1 and red when I type 0. Also, I want the entered number to disappear once the cell turns Green/Red
Hello!
To hide the value in a cell, you can use the conditional formatting formula
=NOT(ISBLANK(E1))
and set the color to white for the text in the cell if the condition is true.
I hope it’ll be helpful.
This did not help me. I'll be very glad if someone understands what I'm trying and comes up the solution to this.
I tried following your instructions for changing cell colour depending upon its value (in my case it is text), but it does not work??
Did you find a solution to this? I'm wanting to populate cells with text and change the colour.
Hi!
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.
How i can using condition formating if we need notif colour red if im done working 6 day. Every working 6 day automatically red colour
Hello!
Use the NETWORKDAYS function to count the number of business days with slow dates. Read more in this article.
Thank you!!!!!!!!!!!!!!!!!!!!
Simple and practical with excellent explanation
On an existing spreadsheet of dollar amounts, I want to use conditional formatting to show the cell's value is greater than the value of the cell above it by setting it to light green. I followed your example and it worked fine for that cell. But how do a do a mass change of all the cells in that column (except the topmost cell, of course)? Same question: how do I make that change for two dozen columns? Basically I want: "Set this cell' background to light green if its value is greater than the value in the cell just above it." - i.e., same column, but row minus 1.
Thanks,
thank you for this useful information
Hi,
What if you wanted the colour to change where the condition is compared to another cell? I want a cell to turn red when the value is less than the other cell, or green if the value is higher.
Hello!
You can learn more about coditional formatting in Excel in this article on our blog — How to use conditional formatting in Excel
It contains answers to your question.
I have the standard excel, I want, if I can any field in that standard excel there should be color change, I am not able to find it, pl sugest.
My question is how do I highlight groups of rows with different colors based on the duplicate value.
For example: all the rows with 2004 in column G would be any color, then all the rows with 2005 in column G be any other different color. There are years from 1991 to 2020 in 500 row table.
thank you so much bhai
This is Resolved. Thank you
Sorry, Making it a little bit complicate, if there is anything other than "Pending" or "Complete" in Column A[n] then the corresponding column in B[n] should be Yellow.
Please suggest? Many thanks.
Its done. Thank you.
What if the conditional formatting needs to be based on text and not numbers.
Example: Turn column B1 Red, if the Value Column A1 entered as “pending”. Turn column B2 Green, if the Value Column A2 contain the is entered as “complete”.
Appreciate your help and thanks in advance
Hello!
Use two conditional formatting formulas:
=A1="pending"
=A2="complete"
Create a conditional format for each color with these formulas.
I hope my advice will help you solve your task.
Thanks for the immediate response. That's really great.
I got it done individually. But I got multiple values in column A. A1 might be having "Pending" A2 might be having "Complete" similarly till A2000. How to apply this to the whole column. Based on the value in Column A[n] the colour coding should be applied in Column B[n]. Whenever there is a change in Column A from "Pending" to "Complete" or vice-versa, that should change the color in Column B[n] accordingly .
Please suggest?
Hello!
Please reread the article above, it covers your case completely. Select the table or range where you want to change the background color of cells. After that, create a conditional formatting rule. Thank you.
Thank you for this example. I was able to complete the task that I wanted!
Thanks for the help...It's like a magic trick. I learned something new and useful today.
I use conditional cell coloring a lot, especially in large spreadsheets. Please tell me if it is possible to make a cell blink, or change intermittently between two colors, so as to emphasize that this cell requires some data input?
Hello Tim!
Unfortunately, standard cell formats in Excel do not provide such effects. This can be done using the VBA macro.
thx so much
Very helpful post.Thanks