Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

 

office tab pic

Layoff season is coming, still work slowly?
-- Office Tab boosts your pace, saves 50% work time!

  •  Amazing! The operation of multiple documents is even more relaxing and convenient than single document;
  •  Compared with other web browsers, the interface of Office Tab is more powerful and aesthetic;
  •  Reduce thousands of tedious mouse clicks, say goodbye to cervical spondylosis and mouse hand;
  •  Be chosen by 90,000 elites and 300+ well-known companies!
Full feature, Free Trial 30-day          Read More             Download Now!
 

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.


pic worker

Reduce work stress, enjoy a healthy and comfortable life,
-- Kutools for Excel helps you solve 80% problems, increase 80% productivity!

  •  Solve your problem targetedly, away from the huge amounts of useless information;
  •  Reduce thousands of keyboard and mouse clicks, relieve your tired eyes and free your keyboard hands;
  •  Deal with all complicated tasks in seconds, get success from the fierce competition, promotion and salary increase are not faraway!
Choice by 110,000 elites!         Full feature, Free Trial 30-day!         Read More             Download Now!
 

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.

Kutools for Excel

300 advanced tools,solve  1500 work scenarios

solve 80% Excel puzzles, increase 80% productivity

Help you Get Promotion,
Increase Salary!

Choice by 110,000 elites, 300+ well-known companies

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!
 
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jeer Wales · 4 months ago
    Can I give colour for a row of values with respect to previous value for a whole row.
    Eg

    23

    45

    32

    56

    can I give red for 23 and green for 45 and again red for 32 as it is less than the previous value and green for 56 as it is more than 32.
  • To post as a guest, your comment is unpublished.
    VANESSA · 10 months ago
    I do get to change a cell colour according to the text or letter in fill in the cell e.g.-H=YELLOW,E=GREEN
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, VANESSA,
      For solving your problem, please apply the Conditional Formatting feature, in the New Formatting Rule dialog box, choose the conditions as following screenshot shown:
  • To post as a guest, your comment is unpublished.
    Leon J. · 1 years ago
    how do i change the colour of cell depending on what is in that cell IE. Y=green N=red.

    thats how i need it if it has Y riten in the cell it turns green?
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, Leon,
      To highlight the cells based on the cell value, you should apply the Conditional Formatting feature, go to the New Formatting Rule dialog box, and then do as the following screenshot shown to highlight the cells which are Y located as green:

      Do with the same way to format the cells N as red color.

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Trying to get a cell to highlight if its above a certain number, the problem is that the cell includes the number and a date. Is there a way i can get it to just look at the number? The cell will include a number+date.....1400(7/2/2018). I need the formulate just to look at the 1400 and not the date.
    any ideas?
    thanks
    Z
  • To post as a guest, your comment is unpublished.
    dhakshanamoorthy · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,
      You can select the formula cells, and then apply the Conditional Formatting > Greater than (0)-format it green, and then Less Than (0)-format it red, see screenshot:

      Please try it, hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    If I have a date in a cell, how do I make it change to orange when it is within 30 days of coming due, yellow when it is within 15 days of coming due and red when it exceeds the date (past due)
  • To post as a guest, your comment is unpublished.
    Scott · 2 years ago
    I thought this color of cells by value was going to be a real pain. It could not have been explained more easily and clearly. Thanks. It took no less than 1-2 minutes to understand what to do.
  • To post as a guest, your comment is unpublished.
    govind · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Crystal · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Mahdi · 3 years ago
    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?
  • To post as a guest, your comment is unpublished.
    subramani · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    shepherd · 3 years ago
    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!!
  • To post as a guest, your comment is unpublished.
    AKSHAY · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    charlotte · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    seema · 3 years ago
    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?
  • To post as a guest, your comment is unpublished.
    thiru · 3 years ago
    hi ginge27

    select all cells & go to condittional formatting & new rules & select format only text contain & specific text,containing & give text name & select colour.
  • To post as a guest, your comment is unpublished.
    ginge27 · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    Maiqueashworth · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Neil · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    pravin · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Mick Dineen · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    jamesc · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Eswar · 4 years ago
    It is very much helpful..!!
  • To post as a guest, your comment is unpublished.
    Martha · 4 years ago
    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!!
  • To post as a guest, your comment is unpublished.
    Saiful Islam Jim · 4 years ago
    Hello sir, is it possible to make a cell (with date) colored automatically when only 2/3 days left to reach on that date?
  • To post as a guest, your comment is unpublished.
    Remco · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    SANTU · 4 years ago
    Thanks, it is working
  • To post as a guest, your comment is unpublished.
    Margaret Mc Grath · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Shane · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Kartik Podugu · 4 years ago
    Detailed explanation. Thanks. It worked.
  • To post as a guest, your comment is unpublished.
    Donald Pearson · 4 years ago
    So, I figured it out 5 minutes after I sent the original message. But thank you for the information posted above, it really helped!
  • To post as a guest, your comment is unpublished.
    Donald Pearson · 4 years ago
    Is there a way to do multiple formatting rules for one column or row?
  • To post as a guest, your comment is unpublished.
    abdul · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    imtiaz ahmad · 5 years ago
    :-| [url]ijaziffi786@hotmail.com[/url]
    thanks sir you are easy my
    work
  • To post as a guest, your comment is unpublished.
    dash · 5 years ago
    how to change everyday different color from row A1:A5
  • To post as a guest, your comment is unpublished.
    Jennifer · 5 years ago
    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?
    • To post as a guest, your comment is unpublished.
      admin_jay · 5 years ago
      [quote name="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?[/quote]
      Please send me a sample of your file at jaychivo#extendoffice.com. Please replace # with @.
  • To post as a guest, your comment is unpublished.
    Faeza · 5 years ago
    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
  • To post as a guest, your comment is unpublished.
    cp · 5 years ago
    Pls send me all conditional formulas..... with colour formulas.....

    Thanks
    Cp
  • To post as a guest, your comment is unpublished.
    Nic · 5 years ago
    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
    • To post as a guest, your comment is unpublished.
      admin_jay · 5 years ago
      [quote name="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[/quote]
      Hello, please try to create some sample data in a workbook, and send it to me at jaychivo#extendoffice.com. Please replace # with @.

      Please tell me what do you want to accomplish. :-)
  • To post as a guest, your comment is unpublished.
    E-square · 5 years ago
    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!
  • To post as a guest, your comment is unpublished.
    August · 5 years ago
    Thanks to all It helped me very well
  • To post as a guest, your comment is unpublished.
    Shahid Kamal · 5 years ago
    Extremely Good.
    it helped me in my work.
  • To post as a guest, your comment is unpublished.
    Vivek Khaire · 5 years ago
    It helped me in urgent meeting in my office. Thankyou very much for help.
  • To post as a guest, your comment is unpublished.
    lola2014 · 5 years ago
    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
    • To post as a guest, your comment is unpublished.
      Russ · 3 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Ieva · 5 years ago
    Does anyone knows how to do so.
    If the number is bigger than 48, change the color only of sat
  • To post as a guest, your comment is unpublished.
    Damiano · 5 years ago
    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
  • To post as a guest, your comment is unpublished.
    Harrell Geron · 5 years ago
    how can a Excel function change color of the cell when the function compute a special value?
  • To post as a guest, your comment is unpublished.
    Teo · 5 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Adil · 5 years ago
    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?
    • To post as a guest, your comment is unpublished.
      vasantha k · 2 years ago
      have you got any reply? if so pls forward to me @ vasanthcrv@gmail.com
  • To post as a guest, your comment is unpublished.
    JACQUELINE M. SARGEN · 5 years ago
    Thanks for this quick tutorial. I was able to use it to track top scores fora fun event at work.