Note: The other languages of the website are Google-translated. Back to English
English English

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

When you deal with huge data in Excel, you may want to pick out some value and highlight them with specific background or font color. This article is talking about how to change the background or font color based on cell values in Excel quickly.


Method 1: Change background or font color based on cell value dynamically with Conditional Formatting

 

The Conditional Formatting feature can help you to highlight the values greater than x, less than y, or between x and y.

Supposing you have a range of data, and now you need to color the values between 80 and 100, please do with the following steps:

1. Select the range of cells that you want to highlight certain cells, and then click Home > Conditional Formatting > New Rule, see screenshot:

doc highlight by value 1

2. In the New Formatting Rule dialog box, select 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.

doc highlight by value 2

3. Then, click Format button, in the Format Cells dialog box, set the background or font color as this:

Change the background color by cell value: Change the font color by cell value
Click Fill tab, and then choose one background color you like Click Font tab, and select the font color you need.
doc highlight by value 3 doc highlight by value 4

4. After select the background or font color, click OK > OK to close the dialogs, and now, the specific cells with value between 80 and 100 are changed to the certain the background or font color in the selection. See screenshot:

Highlight specific cells with background color: Highlight specific cells with font color:
doc highlight by value 5 doc highlight by value 6

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


Method 2: Change background or font color based on cell value statically with Find function

 

Sometimes, you need to apply a specific fill or font color based on cell value and make the fill or font color not change when the cell value changes. In this case, you can use the Find function to find all the specific cell values and then change the background or font color to your need.

For example, you want to change the background or font color if the cell value contains “Excel” text, please do as this:

1. Select the data range that you want to use, and then click Home > Find & Select > Find, see screenshot:

doc highlight by value 7

2. In the Find and Replace dialog box, under the Find tab, enter the value that you want to find into the Find what text box, see screenshot:

doc highlight by value 8

Tips: If you need to find the values with case sensitive or match the entire cell content, please click the Options button to get the advanced search options, such as "Match Case" and "Match entire cell content" as you need.

3. And then, click Find All button, in the find result box, click any one item, and then press Ctrl + A to select all found items, see screenshot:

doc highlight by value 9

4. At last, click Close button to close this dialog. Now, you can fill a background or font color for these selected values, see screenshot:

Apply the background color for the selected cells: Apply the font color for the selected cells:
doc highlight by value 10 doc highlight by value 11

Method 3: Change background or font color based on cell value statically with Kutools for Excel

 

Kutools for Excel’s Super Find feature supports lots of conditions for finding values, text strings, dates, formulas, formatted cells and so on. After finding and selecting the matched cells, you can change the background or font color to your desired.

After installing Kutools for Excel, please do as this:

1. Select the data range that you want to find, and then click Kutools > Super Find, see screenshot:

doc highlight by value 12

2. In the Super Find pane, please do the following operations:

  • (1.) First, click the Values option icon;
  • (2.) Choose the find scope from the Within drop down, in this case, I will choose Selection;
  • (3.) From the Type drop down list, select the criteria that you want to use;
  • (4.) Then click Find button to list all corresponding results into the list box;
  • (5.) At last, click Select button to select the cells.

doc highlight by value 13

3. And then, all cells matching the criteria have been selected at once, see screenshot:

doc highlight by value 14

4. And now, you can change the background color or font color for the selected cells as you need.


Busy work on weekend, Use Kutools for Excel,
gives you a relaxing and joyful weekend!

On the weekend, the kids are clamoring to go out to play, but there is too much work surrounds you to have time accompany the family. The sun, the beach and the sea so far away? Kutools for Excel helps you to solve 80% Excel puzzles, save 80% work time.

pic beach
  •  Get a promotion and increase salary are not faraway;
  •  Contains 300 advanced features, solve 1500 application scenarios, some features even save 99% work time;
  •  Become an Excel expert in 3 minutes, and get recognition from your colleagues or friends;
  •  No longer need to search solutions from Google, say goodbye to painful formulas and VBA codes;
  •  All repeated operations can be completed with only several clicks, free your tired hands;
  •  Only $39 but worth than the other people's $4000 Excel tutorial;
  •  Be chosen by 110,000 elites and 300+ well-known companies;
  •  30-day free trial, and full money back within 60-day without any reason;
  •  Change the way you work, and then change your life style!
 
Comments (79)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Very Nice. please send me Ms Office tips and update on my e-mail address. umair_ahmed192@yahoo.com
This comment was minimized by the moderator on the site
good.............its working
This comment was minimized by the moderator on the site
Worked well thanks.......................
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
@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 :)
This comment was minimized by the moderator on the site
You are a gentleman and a scholar good sir. I just could not figure out how to do this. Thank you.
This comment was minimized by the moderator on the site
its great way to color the sheet but what i need is to color one column only , how can i do that ?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Try this and report back
This comment was minimized by the moderator on the site
i need to highlight values regarding it +- sign in the correlation matrix. any suggestions?
This comment was minimized by the moderator on the site
Provide space and type + or - in the cell, format the cell as text.
This comment was minimized by the moderator on the site
Please send any document about macros like this.... Thanks in advance... :lol:
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
[quote]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. VinayBy VINAY JASWAL[/quote] 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<br /> http://www.extendoffice.com/images/stories/doc-comments/2.png<br /> Please drag the C1 to extend the fill, and you will see the results: http://www.extendoffice.com/images/stories/doc-comments/3.png
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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).
This comment was minimized by the moderator on the site
i didn't get the result after completing the action of fill formatting option please give me correct suggestion. thanks, regards, sai.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
[quote]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. VinayBy VINAY JASWAL[/quote] I would like the reply which you gave to the previous questioner as my problem is the same
This comment was minimized by the moderator on the site
Thanks for this quick tutorial. I was able to use it to track top scores fora fun event at work.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
have you got any reply? if so pls forward to me @ vasanthcrv@gmail.com
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
how can a Excel function change color of the cell when the function compute a special value?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Does anyone knows how to do so. If the number is bigger than 48, change the color only of sat
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations