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.
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! |
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?
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….
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.
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:
Note: The Conditional Formatting is a dynamic feature, the cell color will be changed as the data changes.
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….
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:
3. Then cells containing values between 80 and 100 are selected in the selection. Go on to click the Fill button 
under Home tab to change their background color. See screenshot:
Download and free trial Kutools for Excel Now!
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:
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:
Click to know more about this Kutools for Excel's Select Specific Cells tool.
Download and free trial Kutools for Excel Now!
Demo: 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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Kutools for Excel
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 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...
Comments
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.
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 :)
Very Nice. please send me Ms Excel conditional formulas tips and update on my e-mail address
Thank's
Regards
Feroz Phanibandh
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
thanks,
regards,
sai.
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!
I would like the reply which you gave to the previous questioner as my problem is the same
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?
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?
If the number is bigger than 48, change the color only of sat
Thankyou
it helped me in my work.
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!
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.
Thanks
Cp
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
Please send me a sample of your file at jaychivo#extend office.com. Please replace # with @.
thanks sir you are easy my
work
For example, depending on the value entered into B2, can I formate G5 to change color?
Thank you!!
can you give me a solution?
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
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?
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.
I hope someone can help
select all cells & go to condittional formatting & new rules & select format only text contain & specific text,containing & give text name & select colour.
many thanks.
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
how can i do this?
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.
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