How to change background color based on cell value in Excel?

When we deal with huge data in Excel, we may want to pick out some specific data and highlight them, such as change their background color. This article is talking about how to change the background color based on cell values in selections of Microsoft Excel quickly.

Change background color based on cell value with Conditional Formatting command

Change background color based on cell value with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Supposing you have a range of data, and now you need to color the values between 80 and 100, as the following screenshot shows, how could you change background color based on cell value?

doc-change-fill-color1


arrow blue right bubble Change background color based on cell value with Conditional Formatting command

Microsoft Excel's Conditional Formatting command can change background color for specific cells based on their values with following steps:

1. Select the range that you will work with;

2. Click the Home > Conditional Formatting > New Rule….

doc-change-fill-color2

3. In the New Formatting Rule dialog box, select and highlight the Format only cells that contains item in the Select a Rule Type: box.

4. In the Format Only Cells with section, specify the conditions that you need.

  • In the first drop down box, select the Cell Value;
  • In the second drop down box, select the between;
  • In the third and fourth box, enter the filter conditions, such as 80, 100.

doc-change-fill-color3

5. Click the Format button.

6. In the Format Cells dialog box, set the background color under Fill tab.

doc-change-fill-color4

7. Click OK button to save the settings. Then the specific cells with value between 80 and 100 are filled with the new background color in the selection. See screenshot:

doc-change-fill-color5


arrow blue right bubble Change background color based on cell value with Kutools for Excel

Using the Conditional formatting commands convers nearly 7 steps, and it may be hard to remember the specific settings during the operation. Therefore, we bring you another easy way: Kutools for Excel's Select Specific Cells tool, which can help you select the specific cells firstly, and fill them later.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

1. Select the range that you will work with;

2. Click the Kutools > Select Tools > Select Specific Cells….

doc-change-fill-color6

3. In the Select Specific Cells dialog box, check the Cell Option; specify the filter condition from Specific type, and then click OK button. See the following screenshot.

doc-change-fill-color7

4. Then cells containing values between 80 and 100 are selected in the selection. Go on to click the Fill button doc-change-fill-color9 under Home tab to change their background color. See screenshot:

doc-change-fill-color8

Click to know more about this Kutools for Excel's Select Specific Cells tool.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

-32#Umair Ahmed2014-02-01 15:51
Very Nice. please send me Ms Office tips and update on my e-mail address.
Reply | Reply with quote | Quote
+5#jas2014-02-11 06:55
good.............its working
Reply | Reply with quote | Quote
+4#Glenn2014-02-12 14:46
Worked well thanks.......................
Reply | Reply with quote | Quote
+2#Shekhar2014-02-14 11:24
Dear Madam,
My doubt is when I type a particular nuber in a cell, the same number of cells to be get coloured. For Example mentioned below if I typed 5 number in a cell. So that below cells upto 5 to be get cloured.

5

1
2
3
4
5
6
7
8
9

Can you please help me in this regard.


Thanks and regards,

K. Shekhar,
9666833301.
Reply | Reply with quote | Quote
+5#DSN2014-02-17 06:03
@Shekhar
Your question is not very clear. But in step 4, under "new formatting rule" --> "format only cells with : "cell value" "equal to" "5/number five"..
if you want is less than 5 then select "between 0(zero) and 5" or directly, "less than "5"..

Hope i've answered your quwstion.
Cheers :)
Reply | Reply with quote | Quote
0#BLK2322014-08-08 20:26
You are a gentleman and a scholar good sir. I just could not figure out how to do this. Thank you.
Reply | Reply with quote | Quote
0#tag2014-03-05 10:59
its great way to color the sheet but what i need is to color one column only , how can i do that ?
Reply | Reply with quote | Quote
-10#Feroz. Phanibandh2014-03-11 09:34
Hi Madam/Sir
Very Nice. please send me Ms Excel conditional formulas tips and update on my e-mail address
Thank's
Regards
Feroz Phanibandh
Reply | Reply with quote | Quote
-1#Andrew Hawkes2014-03-17 10:34
Try this and report back
Reply | Reply with quote | Quote
-1#Rasha2014-03-26 14:13
i need to highlight values regarding it +- sign in the correlation matrix. any suggestions?
Reply | Reply with quote | Quote
-1#Marie2014-03-31 08:29
Provide space and type + or - in the cell, format the cell as text.
Reply | Reply with quote | Quote
-2#Manikandan2014-04-01 10:32
Please send any document about macros like this.... Thanks in advance...:lol:
Reply | Reply with quote | Quote
-1#VINAY JASWAL2014-04-02 09:01
Sir, in my worksheet we have put a condition that IF (Cell Value1 > Cell Value 2, "Yes", "No"). Now I want to change the cell colour to Yellow for "Yes" and Red for "No". How should I do it? Please email me your reply.

Vinay
Reply | Reply with quote | Quote
+1#Jay Chivo - Admin2014-04-03 01:56
Quoting VINAY JASWAL:
Sir, in my worksheet we have put a condition that IF (Cell Value1 > Cell Value 2, "Yes", "No"). Now I want to change the cell colour to Yellow for "Yes" and Red for "No". How should I do it? Please email me your reply.
Vinay


Please go to use the Conditional Formatting >New rule... for your operations.

In the C1, please apply two conditional formulas as follows:





Please drag the C1 to extend the fill, and you will see the results:

Reply | Reply with quote | Quote
-1#John Schubert2014-04-14 18:17
The rule would not drag for me. I followed your example, except I did A1 B1, so it would highlight any that were not equal (my intent). However, dragging C1 down to C2 did not bring the rule with it. The cell's formula did however (var1 + var2) * var3
Reply | Reply with quote | Quote
-1#rj2014-04-03 12:36
sir a like to create a FORMULA THAT when the value is less than the designated cell it will turn red and if it is grearter than it will turn green i know how to that but only for one row now my problem is to make the work easier i want to copy the formula to other rows but it copy only the number not the formula
Reply | Reply with quote | Quote
0#TT2014-04-10 09:13
Hello,

I would like to create a formula that when the date in the cells of culumn 'Q' is older than today + 180 days the cell turns red. I have tried to combine different formulas but nothing seems to work so far. Anyone who can help?

thank a lot!
Reply | Reply with quote | Quote
0#Peter2014-04-11 15:33
Quoting VINAY JASWAL:
Sir, in my worksheet we have put a condition that IF (Cell Value1 > Cell Value 2, "Yes", "No"). Now I want to change the cell colour to Yellow for "Yes" and Red for "No". How should I do it? Please email me your reply.

Vinay

I would like the reply which you gave to the previous questioner as my problem is the same
Reply | Reply with quote | Quote
0#JACQUELINE M. SARGEN2014-04-17 17:55
Thanks for this quick tutorial. I was able to use it to track top scores fora fun event at work.
Reply | Reply with quote | Quote
0#Adil2014-04-30 04:34
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?
Reply | Reply with quote | Quote
0#Teo2014-05-05 09:28
Hi. I don't know if there is possible to do what i want, but here's what i'm trying to achieve:
Let's say i have a hundred entries with, let's say, 5 different values scattered around. I want excel to color each value with different colors. I was thinking on a formula/rule that would change the color based on the value change. It is possible?
Reply | Reply with quote | Quote
-1#Harrell Geron2014-05-15 01:51
how can a Excel function change color of the cell when the function compute a special value?
Reply | Reply with quote | Quote
+1#Damiano2014-05-16 10:32
Hi, I would like to ask you if it there is the chance to do the opposite of what you showed. I explain myself better: I have colums with differen values and they are colored red and green. Now I was trying to change those values in 0 and 1 according to the color of the cells. Then 0 for red cells and 1 for green. Is it this possible? Thanks
Reply | Reply with quote | Quote
-1#Ieva2014-06-09 12:08
Does anyone knows how to do so.
If the number is bigger than 48, change the color only of sat
Reply | Reply with quote | Quote
+1#lola20142014-07-15 23:21
Hello, I would like to know if any of you know how to make the percentage of color fill in the cell correspond to the value of the cell.... For example if my cell value is 50% only the 50% of the cell will fill with color, if i change the value of the cell to 100% the whole cell will fill with color.

Thankyou
Reply | Reply with quote | Quote
0#Vivek Khaire2014-08-06 05:02
It helped me in urgent meeting in my office. Thankyou very much for help.
Reply | Reply with quote | Quote
0#Shahid Kamal2014-08-11 04:44
Extremely Good.
it helped me in my work.
Reply | Reply with quote | Quote
0#August2014-08-14 02:47
Thanks to all It helped me very well
Reply | Reply with quote | Quote

Add comment


Security code
Refresh