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

Change background color based on cell value with Conditional Formatting command

Change background color based on cell value with Kutools for Excel

Change background color based on specific value or text with Kutools for Excel


Select and change background color based on cell value:

Kutools for Excel’s Select Specific Cells feature can help you find and select the specific cells, and then you can do some formatting for the selected cells you need.

doc replace greater than 8

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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


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

doc-change-fill-color1-1

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, then click the Home > Conditional Formatting > New Rule….

doc-change-fill-color2-2

doc-change-fill-color3-3

2. In the New Formatting Rule dialog box, select and highlight the Format only cells that contain item in the Select a Rule Type: box, and 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 criteria:between;
  • In the third and fourth box, enter the filter conditions, such as 80, 100.

3. Click the Format button, in the Format Cells dialog box, set the background color under Fill tab.

doc-change-fill-color4-4

4. Click OK > OK to close the dialogs. 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-5

Note: The Conditional Formatting is a dynamic feature, the cell color will be changed as the data changes.


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

Except the Conditional Formatting, here, 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 : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days

After Installing Kutools for Excel, please do as follows:

1. Select the range that you will work with, and click the Kutools > Select > Select Specific Cells….

doc-change-fill-color6-6

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

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

 Download and free trial Kutools for Excel Now!


arrow blue right bubble Change background color based on specific value or text with Kutools for Excel

This Select Specific Cells utility also can help you to select and color the specific text or value as you need. Please do as follows:

1. Select the data range that you want to color the specific cells.

2. Then apply this Select Specific Cells feature, in the popped out dialog box, click Cell under the Selection type, and choose Equals from the Specific type drop down list, then enter the specific text or value that you want to select. See screenshot:

doc-change-fill-color9-9

3. Then click Ok button, and the specific values in the selection have been selected, and then specify a color under the Fill Color button to shade the cells you need, see screenshot:

doc-change-fill-color10-10

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

Download and free trial Kutools for Excel Now!


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

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink -79 Umair Ahmed
Very Nice. please send me Ms Office tips and update on my e-mail address.
2014-02-01 15:51 Reply Reply with quote Quote
Permalink +6 jas
good........... ..its working
2014-02-11 06:55 Reply Reply with quote Quote
Permalink +11 Glenn
Worked well thanks......... ..............
2014-02-12 14:46 Reply Reply with quote Quote
Permalink -1 Shekhar
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.
2014-02-14 11:24 Reply Reply with quote Quote
Permalink +8 DSN
@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 :)
2014-02-17 06:03 Reply Reply with quote Quote
Permalink 0 BLK232
You are a gentleman and a scholar good sir. I just could not figure out how to do this. Thank you.
2014-08-08 20:26 Reply Reply with quote Quote
Permalink -1 tag
its great way to color the sheet but what i need is to color one column only , how can i do that ?
2014-03-05 10:59 Reply Reply with quote Quote
Permalink 0 yo
go to "manage rules" and to the right of the coloring rule you want for the column, there should be a bar with a box on the right. click that and select your column. Then click apply and okay.
2014-10-31 19:39 Reply Reply with quote Quote
Permalink -30 Feroz. Phanibandh
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
2014-03-11 09:34 Reply Reply with quote Quote
Permalink -1 Andrew Hawkes
Try this and report back
2014-03-17 10:34 Reply Reply with quote Quote
Permalink -1 Rasha
i need to highlight values regarding it +- sign in the correlation matrix. any suggestions?
2014-03-26 14:13 Reply Reply with quote Quote
Permalink 0 Marie
Provide space and type + or - in the cell, format the cell as text.
2014-03-31 08:29 Reply Reply with quote Quote
Permalink -10 Manikandan
Please send any document about macros like this.... Thanks in advance... :lol:
2014-04-01 10:32 Reply Reply with quote Quote
Permalink 0 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
2014-04-02 09:01 Reply Reply with quote Quote
Permalink +2 Jay Chivo - Admin
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:

http://www.extendoffice.com/images/stories/doc-comments/1.png

http://www.extendoffice.com/images/stories/doc-comments/2.png

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

http://www.extendoffice.com/images/stories/doc-comments/3.png
2014-04-03 01:56 Reply Reply with quote Quote
Permalink -2 John Schubert
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
2014-04-14 18:17 Reply Reply with quote Quote
Permalink 0 Aaron Gibson
You need to select the 'Fill Formatting Only' option from the menu (click on the small icon after you drag to the bottom of your data).
2014-10-01 12:26 Reply Reply with quote Quote
Permalink 0 SAI
i didn't get the result after completing the action of fill formatting option please give me correct suggestion.
thanks,
regards,
sai.
2015-08-13 07:40 Reply Reply with quote Quote
Permalink -1 rj
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
2014-04-03 12:36 Reply Reply with quote Quote
Permalink +2 TT
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!
2014-04-10 09:13 Reply Reply with quote Quote
Permalink 0 Peter
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
2014-04-11 15:33 Reply Reply with quote Quote
Permalink +2 JACQUELINE M. SARGEN
Thanks for this quick tutorial. I was able to use it to track top scores fora fun event at work.
2014-04-17 17:55 Reply Reply with quote Quote
Permalink 0 Adil
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?
2014-04-30 04:34 Reply Reply with quote Quote
Permalink 0 Teo
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?
2014-05-05 09:28 Reply Reply with quote Quote
Permalink -1 Harrell Geron
how can a Excel function change color of the cell when the function compute a special value?
2014-05-15 01:51 Reply Reply with quote Quote
Permalink 0 Damiano
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
2014-05-16 10:32 Reply Reply with quote Quote
Permalink -1 Ieva
Does anyone knows how to do so.
If the number is bigger than 48, change the color only of sat
2014-06-09 12:08 Reply Reply with quote Quote
Permalink 0 lola2014
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
2014-07-15 23:21 Reply Reply with quote Quote
Permalink 0 Russ
Don't know how to do that, but you could approximate it by choosing different shades of a color. For example, use the most saturated blue for 100% and white for 0%, but for 40-60% select a blue that is halfway in between. Fill in the other percentages with corresponding shades.
2016-03-08 14:06 Reply Reply with quote Quote
Permalink +1 Vivek Khaire
It helped me in urgent meeting in my office. Thankyou very much for help.
2014-08-06 05:02 Reply Reply with quote Quote
Permalink +1 Shahid Kamal
Extremely Good.
it helped me in my work.
2014-08-11 04:44 Reply Reply with quote Quote
Permalink 0 August
Thanks to all It helped me very well
2014-08-14 02:47 Reply Reply with quote Quote
Permalink -4 E-square
what if I input a Alphabetical Value:
for Example if I input Received the cell will turn into Red
and when I put Not Yet Received the cell will turn into Blue.
how about that?
I badly need an Answer. Plss!
2014-09-09 07:23 Reply Reply with quote Quote
Permalink 0 Nic
Please help

I have a spreadsheet measuring when a date is coming up for renewal - however it must be based on the date completed. i.e. if completed 1/1/12 it would come up for renewal 1/1/14 and so it should be highlighted in red after that date physically occurs.

I have set the formula on one cell i.e. C5+730
2014-09-26 01:38 Reply Reply with quote Quote
Permalink 0 admin_jay
Quoting Nic:
Please help

I have a spreadsheet measuring when a date is coming up for renewal - however it must be based on the date completed. i.e. if completed 1/1/12 it would come up for renewal 1/1/14 and so it should be highlighted in red after that date physically occurs.

I have set the formula on one cell i.e. C5+730

Hello, please try to create some sample data in a workbook, and send it to me at jaychivo#extend office.com. Please replace # with @.

Please tell me what do you want to accomplish. :-)
2014-09-28 02:03 Reply Reply with quote Quote
Permalink 0 cp
Pls send me all conditional formulas..... with colour formulas.....

Thanks
Cp
2014-10-12 23:32 Reply Reply with quote Quote
Permalink 0 Faeza
hi Sir,

I want to make a cell color at B1 & B3 using the formula condition IF b2>4 and if only b1 on top 3. Could you please advice me on this matter.
cell a cell b
1 7.16 1
2 7.16 1

3 4.03 1
4 1.00 7
2014-10-17 03:27 Reply Reply with quote Quote
Permalink 0 Jennifer
I have a spreadsheet that students scan an ID card into, then macro formatting to make it time/date stamp. However, I can't distinguish between when they sign in and when they sign out. This is what I want to do: Student scans card when they come in. When they leave, they scan card and either text or the box color changes to distinguish between and an in scan or an out scan. Students do nothing but scan card into a cell...do not want them to have to locate their name or choose in or out. In other words, they scan in and out but they have no idea there is a change in the color. Does that make sense and is there a way to do it?
2014-10-23 13:34 Reply Reply with quote Quote
Permalink 0 admin_jay
Quoting Jennifer:
I have a spreadsheet that students scan an ID card into, then macro formatting to make it time/date stamp. However, I can't distinguish between when they sign in and when they sign out. This is what I want to do: Student scans card when they come in. When they leave, they scan card and either text or the box color changes to distinguish between and an in scan or an out scan. Students do nothing but scan card into a cell...do not want them to have to locate their name or choose in or out. In other words, they scan in and out but they have no idea there is a change in the color. Does that make sense and is there a way to do it?

Please send me a sample of your file at jaychivo#extend office.com. Please replace # with @.
2014-10-24 02:03 Reply Reply with quote Quote
Permalink 0 dash
how to change everyday different color from row A1:A5
2014-11-03 18:56 Reply Reply with quote Quote
Permalink 0 abdul
I have a worksheet with column A..reviewed by, B date and C is for follow up. Under follow up, we put YES or NO. How can i make column C colour coded for NO as Blue and YES as yellow?
2014-12-03 15:14 Reply Reply with quote Quote
Permalink 0 Donald Pearson
Is there a way to do multiple formatting rules for one column or row?
2014-12-04 16:41 Reply Reply with quote Quote
Permalink 0 Donald Pearson
So, I figured it out 5 minutes after I sent the original message. But thank you for the information posted above, it really helped!
2014-12-04 16:50 Reply Reply with quote Quote
Permalink 0 Kartik Podugu
Detailed explanation. Thanks. It worked.
2015-03-17 11:03 Reply Reply with quote Quote
Permalink 0 Shane
I'd like to hightlight a cell if it begins with 215 or 218 or 223 or 227. Not sure of the command to use. Thanks!
2015-05-18 17:50 Reply Reply with quote Quote
Permalink 0 Margaret Mc Grath
Doing work rota I need e.g. yellow cell to equal 4 (hours), green cell to equal 6 and red cell to equal 8 so when I add up 2 yellow cells plus 1 green cells plus 1 red cell I would get the answer 22 (4 x 2 + 6 + 8) I would appreciate if you could help me. Roster is done out in color not numbers.
2015-05-22 13:32 Reply Reply with quote Quote
Permalink -1 SANTU
Thanks, it is working
2015-06-06 05:00 Reply Reply with quote Quote
Permalink -1 Remco
Very nice, but I need to go one step futher; I need cells that contain even nulbers to change to yellow, and odd number change to a blue background. How do I do that?
2015-06-26 10:38 Reply Reply with quote Quote
Permalink -1 Saiful Islam Jim
Hello sir, is it possible to make a cell (with date) colored automatically when only 2/3 days left to reach on that date?
2015-08-03 04:57 Reply Reply with quote Quote
Permalink -1 Martha
Is it possible to format other cells so that their color changes depending upon the contents of another cell?

For example, depending on the value entered into B2, can I formate G5 to change color?

Thank you!!
2015-08-07 15:08 Reply Reply with quote Quote
Permalink -1 Eswar
It is very much helpful..!!
2015-08-18 00:19 Reply Reply with quote Quote
Permalink 0 jamesc
I have a sheet that calculates a value and displays it. I want the background to change based on that value. ie if the calculated value is 3 turn yellow or if 50 turn green. My range is from 1-100 and I am breaking it into 3 steps, red yellow green. thanks/jmc
2015-08-19 17:33 Reply Reply with quote Quote
Permalink +1 Mick Dineen
I want to format a column of cells based on completion status, so I will have complete, in-progress and open. I want to format so I have a colour for each status - green (open), yellow (in-progress) and grey (complete). I have tried to conditional format in excel with no luck.

can you give me a solution?
2015-09-10 15:35 Reply Reply with quote Quote
Permalink 0 pravin
Product price priceid
mobile 2000 101
asset 100 102

if i have to color priceid red where price is less than 1000

what we will do ?
need help
2015-10-30 06:08 Reply Reply with quote Quote
Permalink 0 Neil
In column A I have a list of product names and in column B I have a formula that grades the product performance in the marketplace.

I figured out how to use the conditional formatting to change the performance metric cells based on the color gradient using the percent value. What I can't figure out how to do is change the background of the cell that contains the product name to be the same color as the conditional formatted cell.

Can you help?
2015-11-03 03:44 Reply Reply with quote Quote
Permalink 0 Maiqueashworth
Hi
In an earlier post:
# Jay Chivo - Admin 2014-04-03 01:56
following a question from VINAY JASWAL, you explain very nicely how to do a conditional cell fill if a value is greater or less than a numerical value. Can this be done for a string value? i.e. Can a C be greater than a D? I am looking to choose cell colour depending on whether the grade is greater than, equal to or less than a certain value. Thanks in anticipation.
2015-11-07 02:47 Reply Reply with quote Quote
Permalink 0 ginge27
I'm trying to change the colour of a cell depending on the text next to it.... I'm struggling....
I hope someone can help
2015-12-01 16:53 Reply Reply with quote Quote
Permalink 0 thiru
hi ginge27

select all cells & go to condittional formatting & new rules & select format only text contain & specific text,containing & give text name & select colour.
2015-12-17 09:38 Reply Reply with quote Quote
Permalink 0 seema
if the value of cell C1 is less than the value of cell B1 then the color of the cell D1 must become red. need formula?
2015-12-23 10:14 Reply Reply with quote Quote
Permalink 0 charlotte
Hello, i would like to change the colour of a number of cells in a row if column P contains any text, however the colour it changes to depends on the text in column D would you please be able to help
many thanks.
2016-03-17 16:05 Reply Reply with quote Quote
Permalink 0 AKSHAY
Sir, I have value in cell A10, in A12. Now I entered a simple formula in A15 subtracting these (A10-A12). I want background color of result cell (A15) in green if A10 is large (means result in positive number) and background color in red if A12 is large ( result in negative number). Please provide solution.
2016-03-24 19:07 Reply Reply with quote Quote
Permalink 0 shepherd
l have created a soccer excel sheet and what l am trying to do is that l want to highlight cells based on thier values that " 1-2 " must highlight red for home team and " 0-0 " must highlight yellow for draw and 2-1 green for win home team please note that the value "1-2" is in one cell and all the results will be in one column say D4:D32 , CAN SOMEONE HELP!!
2016-04-21 18:54 Reply Reply with quote Quote
Permalink 0 subramani
Dear Sir,

i want to Differentiate the color while entering the time , for example if i typed 9:00 a.m. that cell will show white color (but enter the time 9:01 the color must vary) and enter the time 6:00 p.m. that cell will shows white color (but enter the time 6:01 the color must vary).

Please help me.

thank you
2016-08-12 12:51 Reply Reply with quote Quote
Permalink 0 Mahdi
Hi..i wanna set a rule for some cells containing Date values(such as 07-02-2016)in order to change backgrounds for example 10 days before that Date arrives or in the day that Date arrives.
how can i do this?
2016-08-16 14:23 Reply Reply with quote Quote
Permalink 0 Crystal
So I am creating a excel spreadsheet for fire department reports. What I am looking for is it to calculate the days a report is overdue, unless it has been completed:

Column A Column B Column C Column D Column E Column F
Incident # Incident Date Todays Date Completed Date Days Overdue Todays Date

I want column B subtracting Column C to calculate in column E unless Column D is complete. I hope this makes sense. I want to use the conditional formatting to show me in colors how long a report is overdue (with the 3 color scale). I have the formula for the number of days it is overdue to have a correct value and color scale, but I want it to remain green when the report has been completed.
2016-08-23 15:35 Reply Reply with quote Quote
Permalink 0 govind
HI,

I want if i change any value or number in any cell then that cell text color should get change for e.g. if number in any cell is 100 and I have to change it as 98 then that cell text 98 should change into red color. Is it possible in Excel?? Do clarify please
2016-11-23 06:44 Reply Reply with quote Quote

Add comment


Security code
Refresh