How to use conditional formatting in Excel

The tutorial explains the basics of Excel conditional formatting feature. You will learn how to create different formatting rules, how to do conditional formatting based on another cell, how to edit and copy your formatting rules in any version of Excel 2019, 2016, 2013, 2010 and earlier.

Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and identify variances of cells' values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners. If you feel intimidated by this feature too, please don't! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this short tutorial.

The basics of Excel conditional formatting

The same as usual cell formats, you use conditional formatting in Excel to format your data in different ways by changing cells' fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain criteria, or conditions.

You can apply conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell's value. You do this by creating rules (conditions) where you define when and how the selected cells should be formatted.

Where is conditional formatting in Excel?

For a start, let's see where you can find the conditional formatting feature in different Excel versions. And the good news is that in all modern versions of Excel, conditional formatting resides in the same location, on the Home tab > Styles group.

Conditional formatting in Excel 2007

Conditional formatting in Excel 2007

Conditional formatting in Excel 2010

Conditional formatting in Excel 2010

Conditional formatting in Excel 2013 - 2019

Conditional formatting in Excel  2013 - 2019

Now that you know where the conditional formatting feature is located in Excel, let's move on and see what format options you have and how you can create your own rules.

How to create Excel conditional formatting rules

To truly leverage the capabilities of conditional format in Excel, you have to learn how to create various rule types. This will help you make sense of whatever project you are currently working on.

Conditional formatting rules in Excel define 2 key things:

  • What cells the conditional formatting should be applied to, and
  • Which conditions should be met.

I will show you how to apply conditional formatting in Excel 2010 because this seems to be the most popular version these days. However, the options are essentially the same in all Excel versions, so you won't have any problems with following no matter which version is installed on your computer.

  1. In your Excel spreadsheet, select the cells you want to format.

    For this example, I've created a small table listing the monthly crude oil prices. What we want is to highlight every drop in price, i.e. all cells with negative numbers in the Change column, so we select the cells C2:C9.
    Select the cells you want to format.

  2. Go to the Home tab > Styles group and click Conditional Formatting. You will see a number of different formatting rules, including data bars, color scales and icon sets.
  3. Since we need to apply conditional formatting only to the numbers less than 0, we choose Highlight Cells Rules > Less Than...
    Choose Highlight Cells Rules > Less Than...

    Of course, you can go ahead with any other rule type that is more appropriate for your data, such as:

    • Format values greater than, less than or equal to
    • Highlight text containing specified words or characters
    • Highlight duplicates
    • Format specific dates
  4. Enter the value in box in the right-hand part of the window under "Format cells that are LESS THAN", in our case we type 0. As soon as you have entered the value, Microsoft Excel will highlight the cells in the selected range that meet your condition.
  5. Select the format you want from the drop-down list. You can choose one of the pre-defined formats or click Custom Format... to set up your own formatting.
    Select the format you want to apply.
  6. In the Format Cells window, switch between the Font, Border and Fill tabs to choose the font style, border style and background color, respectively. On the Font and Fill tabs, you will immediately see a preview of your custom format.
  7. When done, click the OK button at the bottom of the window.
    Choose the background color.

  • If you want more background or font colors than the standard palette provides, click the More Colors... button on the Fill or Font tab.
  • If you want to apply a gradient background color, click the Fill Effects button on the Fill tab and choose the desired options.
  • Click OK to close the "Less Than" window and check whether the conditional formatting is correctly applied to your data.

As you can see in the screenshot below, our newly created conditional formatting rule works right - it shades all the cells with a negative price change.
The newly created conditional formatting rule is applied.

Creating an Excel conditional formatting rule from scratch

If none of the ready-to-use formatting rules meets your needs, you can create a new one from scratch.

  1. Select the cells to which you want to apply the conditional format and click Conditional Formatting > New Rule.
    Creating a new conditional formatting rule in Excel
  2. The New Formatting Rule dialog opens and you select the needed rule type. For example, let's choose "Format only cells that contain" and opt to format the cell values between 60 and 70.
    Select the conditional formatting rule type and specify the values.
  3. Click the Format... button and set up your formatting exactly as we did in the previous example.
  4. Click OK twice to close the open windows and your conditional formatting is done!
    The values are conditionally formatted.

Excel conditional formatting based on cell's value

In both of the previous examples, we created the formatting rules by entering the numbers. However, in some cases it makes more sense to base your condition format on a certain cell's value. The advantage if this approach is that no matter how that cell's value changes in the future, your conditional formatting will adjust automatically and reflect the data change.

For instance, let's use the "Oil price" example again, but this time highlight all the prices in column B that are greater than February's price.

You create the rule in a similar fashion by selecting Conditional formatting > Highlight Cells Rules > Greater Than... But instead of typing a number in step 4, you select cell B6 by clicking the range selection icon as you usually do in Excel. As the result, the prices get formatted as you see in the screenshot below.

Create an Excel conditional formatting rule based on a cell's value.

This is the simplest example of Excel conditional formatting based on another cell. Other, more complex scenarios, may require the use of formulas. And you can find several examples of such formulas along with the step-by-step instructions in this article: How to change a cell color based another cell's value.

This is how you do conditional formatting in Excel. Hopefully, these very simple rules we have just created was helpful to understand the general approach.

Apply several conditional formatting rules to one cell / table

When using conditional formatting in Excel, you are not limited to only one rule per cell. You can apply as many rules as your project's logic requires.

For example, let's create 3 rules for the weather table that will shade temperatures higher than 60 °F in yellow, higher than 70 °F in orange, and higher than 80 °F in red.

You already know how to create Excel conditional format rules of this kind - by clicking Conditional Formatting > Highlight Cells rules > Greater than. However, for the rules to work correctly, you also need to set their priority in this way:

  1. Click Conditional Formatting > Manage Rules... to bring up the Rules Manager.
  2. Click the rule that needs to be applied first to select it, and move it to the top using the upward arrow. Do the same for the second-in-priority rule.
  3. Select the Stop If True check box next to the first 2 rules because you do not want the other rules to be applied when the first condition is met.
    Setting the rules' priority

Using "Stop If True" in conditional formatting rules

We have already used the Stop If True option in the example above to stop processing other rules when the first condition is met. That usage is very obvious and straightforward. Now let's consider two more examples where the use of the Stop If True function is not so obvious but also very helpful.

Example 1. Show only some items of the icon set

Suppose, you have added the following icon set to your sales report.
A sales report with icon sets

It looks nice, but a bit inundated with graphics. So, our goal is to keep only the red down arrows to draw attention to the products performing below the average and get rid of all other icons. Let's see how you can do this:

  1. Create a new conditional formatting rule by clicking Conditional formatting > New Rule > Format only Cells that contain.
  2. Now you need to configure the rule in such a way that it gets applied only to the values greater than the average. You do this by using the =AVERAGE() formula, as shown in the screenshot below.
    Tip. You can always select a range of cells in Excel using the standard range selection icon Range selection icon or type the range inside the brackets manually. If you opt for the latter, remember to use absolute cell references with the $ sign.

    Configure the rule using the AVERAGE function.

  3. Click OK without setting any format.
  4. Click Conditional Formatting > Manage Rules... and put a tick into the Stop if True check box next to the rule you have just created. And... see the result in the screenshot below : )No formatting is applied to the cell values greater than the average.

Example 2. Remove conditional formatting from empty cells

Suppose you created the "Between" rule to highlight cells' values between $0 and $1000, as you can see in the screenshot below. But the problem is that empty cells are also highlighted.
Empty cells are also highlighted with conditional formatting.

To fix this, you create one more rule of the "Format only cells that contain" type. In the New Formatting rule dialog, select Blanks from the drop-down list.
Create a conditional formatting rule for blank cells.

And again, you simply click OK without setting any format.

Finally, open the Conditional Formatting Rule Manager and select the Stop if true check box next to the "Blanks" rule.

The result is exactly as you would expect : )
No formatting is applied to blank cells.

How to edit conditional formatting rules in Excel

If you've had a close look at the screenshot above, you probably noticed the Edit Rule... button there. So, if you want to change an existing formatting rule, proceed in this way:

  1. Select any cell to which the rule applies and click Conditional Formatting > Manage Rules...
  2. In the Conditional Manager Rules Manager dialog, click the rule you want to edit, and then click the Edit Rule... button.
    Editing conditional formatting rules in Excel
  3. Make the required changes in the Edit Formatting Rule window and click OK to save the edits.

    The Edit Formatting Rule window looks very similar to the New Formatting Rule dialog you used when creating the rule, so you won't have any difficulties with it.

    Tip. If you don't see the rule you want to edit, select This Worksheet from the "Show formatting rules for" drop-down list to display the list of all rules in your worksheet.
    Display the list of all conditional formatting rules in the worksheet.

How to copy conditional formatting in Excel

If you want to apply the conditional format you have created earlier to other data on your worksheet, you won't need to create the rule from scratch. Simply use Format Painter to copy the existing conditional formatting to the new data set.

  1. Click any cell with the conditional formatting you want to copy.
  2. Click Home > Format Painter. This will change the mouse pointer to a paintbrush.
    Copy conditional formatting in Excel.

    Tip. You can double-click Format Painter if you want to paste the conditional formatting in several different ranges of cells.
  3. To paste the conditional formatting, click on the first cell and drag the paintbrush down to the last cell in the range you want to format.
    Paste the conditional formatting to another range of cells.
  4. When done, press Esc to stop using the paintbrush.
    Note. If you've created the conditional formatting rule using a formula, you may need to adjust cell references in the formula after copying the conditional format.

How to delete conditional formatting rules

I've saved the easiest part for last : ) To delete a rule, you can either:

  • Open the Conditional Manager Rules Manager (as you remember, you open it via Conditional Formatting > Manage Rules...), select the rule and click the Delete Rule button.
  • Select the range of cells, click Conditional Formatting > Clear Rules and choose one of the available options.

Delete conditional formatting rules in Excel.

Now you have basic knowledge of Excel conditional format. In the next article, we will focus on more advanced features that will help you push conditional formatting in your spreadsheets far beyond its traditional uses.

And in the meantime, you may want to check out a few more examples of Excel conditional formatting:

275 comments to "How to use conditional formatting in Excel"

  1. Jackie says:


    I am trying to use conditional Formatting > Format only cells that contain>cell value between (then I have the values copies from a seperate sheet. I have created 4 different rules with 4 different values. The first two rules work, but the 3rd, and 4th do not. The weird thing is if I move up my third rule to the second position it will work, but it is still only the first two rules that will work. Please help!

    Cell Value Between ='Employee List'!$B$2 and ='Employee List'!$B$16
    Cell Value Between ='Employee List'!$B$20 and ='Employee List'!$B$30
    Cell Value Between ='Employee List'!$B$34 and ='Employee List'!$B$39
    Cell Value Between ='Employee List'!$B$43 and ='Employee List'!$B$70

    • Hi,
      The information you provided is not enough to understand your case and give you any advice, sorry. You haven't written what formulas you are using.
      Pay attention to the following paragraph of the article above: "Apply several conditional formatting rules to one cell — Using "Stop If True" in conditional formatting rules"
      If you describe the problem in more detail, I will try to help you.

  2. Jas says:

    I have a cell with a % range in it ie 1-2% and in the next cell I want the LMs to suggest a % increase within the range. However if they go over that range ie 2.5% I want that cell to flag red, is this possible?

  3. Le says:

    Hello- I need your help with conditional format formula. I want it to highlight cell A1 if cell A1 is 50% less than B1. Also if I have a lot of cells with 0 i want to make sure there is no color.
    I after that I can create same formula different percentages.
    thank you,

  4. Renier Boshoff says:

    Good day.
    I have a spread sheet of 300+ line. I want to add a formula what hides values what is the same amount, but opposite (positive and negetive).
    A1 -35,00
    A2 -34,80
    A3 -33,26
    A4 -15,00
    A5 32,12
    A6 33,26
    A7 34,80

    A1 - Stay
    (A2,A7) Hide Same number, but opposite value(positive and negative)
    (A3,A6) Hide Same number, but opposite value(positive and negative)
    A4 - Stay
    A5 - Stay

    Thank you

  5. MICHAEL says:

    I want to copy from one sheet to another.
    If the total of the cell is > 499.00
    I want to copy the entire row into another sheet in the same workbook

  6. wahid says:

    Dear Concern,
    I am very novish in using Excel, I am a business man, for my own business I have to make my Inventory Management System.From viewing Youtube I have almost built my system but in some case I am stuck. In my "Stock Out" section when stock out command quantity is more than (Opening qty+Stock In qty) there I want "SHORT" word should appear. But this thing I cannot do. Can you Please help me in this issue,how can i solve it.
    Thanks with regards

  7. Aash says:

    Hello i want a doubt to be cleared!
    so when i am applying conditional formatting for a cell which is which four different conditions
    1, i need red colour when i use the formula to determine the cells to format For eg there is another cell, when the condition is met then i need it red.
    2, when i am using format only cells that contains - in that i have given blanks, and no format
    3, I need yellow when i am using the format only cell that contain - in that i given cell value - and select equal and i select a cell to which it should be equal.
    4, the last one is i am using format only top and bottom ranked values - in that i give bottom - and i select 1 and i give a colour to that.

    the problem is it is not displaying as the way i need there is some mismatch happening.
    Could you please help with this. Thank you in advance.

  8. NINA says:

    ***Select the range E2:E30 and create a new conditional formatting rule that uses a formula to apply yellow fill and bold font to values that sold for less than or equal to 70% of the sticker price.****

    The only thing i'm stuck at is the formula. can any one help me figure it out.

  9. Seth says:

    Is there a way to create conditional formatting in a line graph? I have created a monte carlo simulation and I have only graphed the first 50 series on the line graph. I would like to make the lines that are above $1 green and the lines below $1 red.

  10. hakim says:

    I want to make a two different colour in the same column if the number is double mins two times then it will be yellow and the other hand number will be more then two times then it will be red

  11. stuti mohanty. says:

    in versions of 2010, 2013& 2016 and 2017, 2018, 2019 in all ms word, ms excels& ms power point all Groups, all Tool Bars names And DETAILS send me in and all shortcuts KEYS sent me
    in now all in now
    for me my G- mail ID

  12. Dan Morris says:

    In Excel new formatting, use a formula to determine which cells to format, im trying to create a formula to change the font color based on a cell value of B10 (+/-0.007) IE Anything beetween 9.9993 and 10.0007 should be "True" is that possible?

  13. stuart says:

    Hi experts, can you please tell me how to move the date on a plot(x axis)back to the bottom once I have changed to y axis to a logarithmic scale?

  14. Anne says:

    I'm trying to format whole rows based on the data in column AB, using formula =$AB10>0 but it doesn't highlight anything. What am I doing wrong?
    The data in column AB is currency, and I need the rows highlighted if the value in AB is greater than $0.00.

  15. RG says:

    Hello, I am tring to color code a Rank of numbers in a single column. This column contains positive, zero, and negative numbers. I have 3 conditional rules, for 1,2, & 3 places
    1st Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,1)
    2nd Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,2)
    3rd Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,3)
    My test numbers are: -4, -2, 4, 0, 2
    First place is 4, correct. Second Place is 2, correct, Third place is -2, wrong. It should be zero.
    Can you help ?

  16. sanskriti joshi says:

    Show the names of employees in blue text color who have salary greater than or equal to 3000 and show the names in green text color for those earning less than 3000.

  17. Sharif says:

    hi does anybody know how to make yes have a background value of 100%?

    So I am able link it to a dependent and can average everything out.

  18. joy says:

    I have a total of 50 employees. On a separate sheet, I have the list of employees who filed for leaves on a specific day however I'd like to highlight only the first 10% of 50 (which is 5) since the allowed number of leaves per day is 5 only. The basis is the Transaction ID:

    ID" Employee # Name Date and Time Rqstd
    1 F025630 John Smith 4:30:03 PM
    2 F025631 John Doe 7:32:03 PM
    3 F025632 Jane Doe 12:30:03 AM
    4 F025633 Jane Smith 1:30:03 AM
    5 F025634 Tony Stark 1:35:03 AM
    6 F025635 Steve Rogers 1:36:03 AM
    7 F025636 Clint Barton 2:30:03 AM
    8 F025637 Natasha Romanoff3:30:03 AM
    9 F025638 Bruce Banner 6:30:03 AM
    10 F025639 Thor Odinson 9:30:03 AM

    How do I do that?

  19. dinesh says:

    please help me ,
    if i am type 20-2 in excle 2013 then excle take 43151 value , not take correct value so how to resolve this

    • Doug says:

      How is the cell you're entering the 20-2 formatted?
      Try formatting it in another way.
      Right click select Format Cells and then choose another option from that list. Maybe text.

  20. Aj says:

    Please help.

    So I’m not sure what to use or what to do. So I’m trying to make an inventory sheet, so right now b9,c9, is 10 where e9,f9 is 10 and h9,i9 is 10. So when i9 goes to “0” I want h9 to Recognise and talk to f9 and say hey I’m giving my value of 10 to i9 so you will need to talk to e9 and Your Your 10 from him. Do I make sense?

  21. Maria haider says:

    What are the different number formats?How will you change the format of the number?write the steps

  22. Josh says:

    I want to set the conditions of a cell to:
    today's date - 180 days turn red
    today's date - 135 days turn orange
    today's date - 90 days turn yellow
    today's date - 75 days turns green

    Can anyone help?

    • Doug says:

      You've asked to apply four different conditional formats based on four different conditions, so you'll need four rules all applied to the same range of cells.
      Select the range of cells to which you want to apply the rules.
      Select Conditional Formatting, select Highlight Cell Rules, then More Rules, then select Use Formula to determine which cells to format.
      Where the range is H48:H51, in the field that is displayed enter this formula:
      Notice that the formula references the top left cell in the range H48:H51. Your range and top left cell is probably different, but this formula must reference the top left cell in the range.
      Notice that this formula will apply the formatting to the date that is 75 days from today.
      The H48>TODAY() ensures the date in H48 is not in the past.
      You asked for TODAY()+ 180, etc. so the formula includes H48=(TODAY()+75), etc. So it is only formatting the cells that are equal to 180 days (or whatever number of days)from today. Not greater than the number of days from today. This allows you to put different formatting to each rule and specific future date.
      Select format, then choose the fill color then OK and then apply and OK your way back to the dialogue window that displays the rules.
      In that window you'll want to click Add New Rule so that you can enter a new rule which will have the same formula except that you'll change the "+" number to the different number of days and different formatting for each different rule.
      Follow this same procedure until you have all four rules and their respective formatting the way you want them.

  23. nevermind says:

    how can i add 2 cell the second cell will petch or getch the data of the first cell and after adding it the 1 cell will reset to zero and the second cell will stay its value
    for example
    1+0=1 then after the sum it will be like this
    0+1=1 then i will input new no. to cell 1
    2+1=3 the the the second cell will show the total and the 1cell will be reset to 0

  24. Abhinay Singh says:


    Can someone help me with the below one ?

    A reports to B and B reports to C and c reports to D and so on hierarchy..

    Now there are certain expectations wherein A reports directly to D .. in case I want to pull n represent this as per hierarchy how should I do it ?

    Endly, let’s assume 5000 employees hierarchy is X and 3000 hierarchy is Y how should we add them up together

  25. Smith says:

    how do you compare figures that are in the same raw. e. g show how the deposits made perform against each other.

  26. Mary says:


    I am trying to highlight cells in column C based on the second digit in a 10-digit code - I want each cell in that column highlighted one color if that digit is N, another color if that digit is not an N. I assume I will need two rules, and am trying to use the formulas =mid(c2,2,1)="N" and =mid(c2,2,1)"N". When I tested the formulas in a blank column the "true" and "false" come up correct, but when I put them into a Conditional Formatting rule, they are not highlighting the correct cells. Any suggestions?

  27. Dan says:

    Hello i have a work list sheet with multiple items on then i have a date type in two different columns i have a 90 day wjndow from the date to complete the work by the date i was trying to figure out how to have the celss turn red after the 90 is past

  28. Hussein says:

    Kindly anybody has Idea, I have one text cell in Excel, hwo to divide that text into two cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do, Thanks in adv. and happy new year

    • Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on or

      Sorry I can't assist you better.

  29. Hussein says:

    Kindly anybody has Idea, I have one text cell in Excel, who to divide that text into tow cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do

  30. Amy says:

    Hi -
    I want to trigger a cell to change color depending on the % in another cell. I have been able to create rules for 0% in T8 to trigger U8 Grey, 80% T8 to trigger U8 orange, but when I add 100% T8 to trigger U8 Green it doesn't work and still triggers orange. Can you let me know what I am doing wrong?
    Thanks for any and all your help!!!

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  31. Carine says:


    I am not sure if it has been mention above but I am stuck with a formula.

    I want the following: If $X2 is not blank / empty and $W2 has a negative value (loss) then the entire row goes red. If $W2 has a positive value (revenue) then the entire row goes blue.

    I have tried few things but I don't know. I have tried this for example =IF($W2<0,ISBLANK=$X2=FALSE)

  32. Ian says:

    I am trying to create a training document. The document will contain several employees, each having a date they were certified, and then a date that they will be due to re-certify. I am trying to figure out the formulas that I would need to input in order to change the row either red=overdue, yellow=due within 90 days, and green=91days>. The formula that seems to be closest to what I need is Rule1=datedif($A2,Today(),"d")>=1:89,Rule2=datedif($A2,today(),"d")>=90:275, and Rule3=datedif($A2,today(),"d")>=276 but for some reason the rules are keeping all three colors from showing. could you please point me in the right direction and thank you for your time.

  33. Mohsin says:

    I want to keep conditional formatting in dates for vehicles record. when i enter the current it should check last 6 month records. if it's there will change automatically.

  34. Sophie says:

    Hi all,

    My problem is probably very basic in comparison with all of yours but i am not an excel bizz and I couldn't find the resolution in the above comments or tutorial...
    At work i have created a spreadsheet for all our new clients and all of the checks and ID they have top provide etc. before we can start working for them. When all of the checks have been passed and they have supplied sufficient information about themselves for our records the date of completion is put in the end column. How can I make this cell with the date in turn red after a year of the date? As this is when we have to request new up-to-date information from them... Any help would be greatly appreciated asap!

  35. Ben says:

    Privet vsem!

    Sorry for disturbing you, but I am a newbie to Excel fomatting and need some help. I have an excel sheet where I keep the profiles of people(name, last name, performance, ....). I do have multiple copies of the same person(let's call him Andrey Goncharov) on my sheet as I am copying it from another source (every week). So last week I marked Andrey's "Performance" column as a "Top Performer". When I copy (from a different source) again this week, if there is Andrey Goncharov, I want Excel to auto-fill the "Performance" column of a new copy as a "Top Performer", because I already have done before for that person. I hope I could explain my issue.

    Thanks a lot for your help and support, you are a wonderful team :).


    • Hello, Ben,

      I'm sorry, it's difficult to come up with any suggestion, since we don't know how your data is stored and there are too many peculiarities that can affect the way your task should be solved.
      If you still require our assistance, feel free to send us your example workbook with the source information and the result you want to get to Please include the link to this comment into the email.
      Our technical specialist will take a look at your task and try to help.

  36. swaroop says:


    I have table shown below
    no low high b/s present
    01 105 125 b 105
    02 252 183 s 183
    03 453 522 s 522

    In need a formula for present column in such that if b/s column contain "b" then present column value should be low column otherwise if b/s column contain "s" then the value of present cell should be high . could you please give me the formula for the above case

  37. Dragon says:

    I cant seem to work this out, I know its simple but my brain is fried.

    I need to use conditional formatting to say Target met if there has been an increase of 5% in 2015 from 2014 and improvement needed if there has not been a 5% increase. Its the % that are confusing me. Thanks

    Strongly Agree
    A2 0.93
    A3 0.97
    A4 0.92
    A5 0.93
    A6 0.95
    A7 0.89
    A8 0.82

    Strongly Agree
    A13 0.91
    A14 0.91
    A15 0.9
    A16 0.89
    A17 0.88
    A18 0.8
    A19 0.75

  38. Rasheed Ibrahim says:


    Im trying to add a conditions into a excel sheet to highlight the cell in chosen colour.

    if figure is cell A is equal to or more than 25% of the figure in Cell B, then colour cell A Red, otherwise colour cell A Green.

    are you able to help me with a simple formular. I am using excel 2016

    thank you

  39. Emily says:

    Perhaps someone might be able to help me. I'm an intermediate excel user but haven't extended much past simple formulas.

    I am working on a project with a column dedicated to using 3-4 single letters to represent different words. Is there a way to conditionally format so a blank column would automatically change the font color given the letter imputed into the cell?
    i.e. C=Card, B=Basket, K=Keep Brunch,F=Flowers, N=Nothing


    Thanks so much!

  40. Brad Carlisle says:

    I'm trying to conditionally format cells ONLY if the cell does not contain a formula and the cells is >0. I have a data set that pulls data from different places and I want to be able to easily tell when something is hard coded as long as it's greater than zero.

  41. Tarique Khan says:

    Hello Dear Experts..

    I have a table in which I already inserted formula & conditional formatting to get the result.

    Example:- In column O3 I have mentioned Expiry Dates & in column P3 I have mentioned =O3-TODAY() & inserted conditional formatting to know remaining days to get expired with Less than value using colour format. (Like, colour to be change as yellow if days remain less than 60 & Red if days remain less than 30) it is working.

    But What I want is if the expiry date has not been mention in column O3 then Column P3 should be left blank without any text.

    Awaiting for your response

  42. Karam says:

    How can we add value of two cell but show in one cell
    For exmpl
    A1 cell value is 10
    B1 cell value is 20 total 30(a1+b1)
    But these values calculated in b1 cell not another cell. Plz reply

  43. Ashok Sahu says:

    I have 2 question.

    1:- When I will enter date in shhet2 A2 column then the out put will show in sheet2 B2 column. My B2 column formula is =If(isblank(A2),"Please enter Date",if(isnumber(A2),vlookup(A2,Sheet1!A:C,2,0),"Invalid Date")).
    So I want, when will show "PLEASE ENTER DATE" in B2 then the font colour will be GREEN. When "INVALID DATE " will show then the font colour will be RED.And the result of vlookup font colour will show YELLOW.

    2:- The second question is- I put the formula in B6 =if(A6="Sun","Ashok,Deepak",if(A6="Mon","Rajesh,Ramesh",if(A6="Tue","Prakash,Dillip",""))).
    So I want when the name will show then the font colour will be blue.

    so please tell me how to solve it by conditional formating.

  44. Tarique Khan says:

    Hello Experts..

    I have a table in which I already inserted formula & conditional formatting to get the result.

    Example:- In column O3 I have mentioned Expiry Dates & in column P3 I have mentioned =O3-TODAY() & inserted conditional formatting to know remaining days to get expired with Less than value using colour format. (Like, colour to be change as yellow if days remain less than 60 & Red if days remain less than 30) it is working.

    But What I want is if the expiry date has not mention in column O3 then Column P3 should be blank without anything.

  45. Munir says:

    Hello Friends!

    I writing the figure 624 in a cell G6 and in cell H6 I writing =G6*1% in this case I got the correct answer, but I intend to get formula for multi figures in h6 like this =g6*1% with the new answer *10% and further with new answer *12% in just a single cell as H6. what I can do for the same. Guide me someone please.

    M. Munir

  46. MONIK says:




    A1 = < A1-(A1*0.1)




  47. D Hilton says:

    I have a training spreadsheet with expiry dates. I want to use traffic lights to turn the cells red, amber or green as they approach the date of expiry compared to todays date, in months. eg due in 12 months or more green, due in 6 months or more amber and less than 6 months red. Is there an easy way to do this in Excel 2010 please. I have formatted the date as eg Jan 2017, Mar 2019 to make it easier. Thanks

  48. Carlos Currea says:

    I've been trying to make this work. I want that every time I have a negative number in column L, the whole row where the number is change the font to red. I can make it work for just one row but when I try to implemented in the worksheet is when I got stuck. In the conditional formatting option I select the "Use a formula..." option. I type =$L:$L<0, But it doesn't work. when I just select a specific cell (i.e. =$L$145), it works but that's not what I am looking for. On the other part I just type the rows I want to be affected by this (=$A:$R,$T:$AD).
    Any help regarding this matter, I really appreciate it.
    Thank you!

  49. Kieran Yeow says:

    Hi Guys, Can someone help me please.
    i need help with an example below.

    i want in column x. 13/06/05 , 14/08/16, 25/07/17
    in column y i want it to turn red if nothing is filled in a week after the dates in column x.

    Can someone please help

  50. Anthony says:

    im building 5 town houses, i owe the bank 2.5 million
    when i have sell for 5 townhouses lets say 600- 700 thousand each
    in excel i got
    2.5m in cell B3
    every time i sell a house i enter the price and it deducts from B3
    lets say i have 300 thousand left to pay off showing in B3
    i enter 400 thousand i want the B3 to be zero and what is leftover goes to a different cell
    hope this makes sense

  51. Ray says:

    i have names lised in a column 1-15.
    What i am trying to do is is, that if one of those same names appears in another cell in the same column, that the original name in the column 1-15 disappears and turns a different colour?
    Is this possible through conditionl formatting?

  52. Nitai says:

    I need help.
    I want to see only one cell in which conditional formatting applied on the basis of value entered in the cell.That means if i enter 10% then 10% of the cell will coloured, if i enter 50% then 50% will be colored,if i will enter 100% then full cell will be coloured.(This is for only one cell).

    Thanks in advance.

  53. Justin says:

    I'm trying to make a timecard that calculates both regular and overtime hours. I want to make it where when I reach a total of 40 hours in regular time it stops entering data in the cells in the regular time column and any time over 40 hours will then start showing up in the cells in the overtime column.

    I apologies if this doesn't make sense

  54. Melisa Gualdrón-López says:


    I am designing a hitmap using conditional formatting and I would like not to show the value in the cell. I have tried modifing the format of the cell by writting the comand ;;;; in the costum blog of the cell format... but does not work... any idea on how to "Hide" the cell value?


  55. Nabeel says:

    How to Get? - If amongst 4 cells if I put 1 in any of the cells, rest of the three cell should be showing 0. How to do that?
    Which formula to use?

  56. nirpinder says:


    i have series of rows employee wise , with character p entered for present on applicable dates in a month , i want to highlight the column where p consecutively appears in a row 10th time

  57. Edilberto Riel says:

    i want to learn how to make alert(thru highlight) when the stocks was below safe level. i was able to find out how for 1 row but my problem was how to do it if i'm monitoring 1 thousand items wherein i don't have to do it 1 by 1. 2nd, is it possible to highlight the entire row?

    item on hand safety stock
    a1 10 15
    a2 11 14
    a1000 12 10

  58. Raluca says:


    Can someone please help me. I need to do conditional formatting on values that are on different worksheets.Cell O5 in worksheet A if it's found in Cell I in worksheet B to be highlighted as whatever colour.

    Many thanks

  59. David Keen says:

    I need to be able to register in two cells the highest and lowest negative and positive results from a changing portfolio total in order not to have to manually monitor and record these myself. I am a complete novice when it comes to formatting cells so a simple abc approach would be very much appreciated.

    Many thanks

    • Hi, David,

      let's say that your totals are in the 7th row (A7:G7). And you have two other cells where you want to see the highest and lowest results. So, for the lowest one you put the next formula in the cell:
      (where A7:G7 is your range of the results)
      For the highest one, enter another formula in another cell:
      Every time the results change, these formulas will adjust the highest and the lowest numbers.

      If you need to find the lowest AND the highest for negative results, the lowest AND highest for positive ones, let us know what Excel version you're currently using.

      • David Keen says:


        Many thanks for your advice, much appreciated.

        I may not have explained the problem correctly. I have one cell with an ever-changing total, both post and neg, which I record in two other cells, one recording the highest positive and the other the highest negative. I would like to be able to record these automatically so need the formula for each cell to do that.

        I am using Excell for Mac 2011 version 14.7.3

        Apologies for any misunderstanding and thanks once again

  60. vikram says:

    Hi team
    i have a doubt regarding the average of values, for suppose if we have two trials TS1 and TS2, in which i have got TS1 is 0.08% and TS2 is 0.03%, the average we required is from the values which are >0.05% it means the final value from above is 0.08%. for this i have kept a command that =IF(AVERAGE(D14,F14)<0.05,"<0.05",AVERAGE(D14,F14)). for suppose the values for TS1 and TS2 both are <0.05% it means if TS1 is 0.03 and TS2 is 0.04 then the Average value to be displayed is <0.05. but iam not able to get it, plz anyone can help me out.....

  61. M zaid says:

    Hy Svetlana
    Today I Read Your excel formula where i am very pleasant because you solve many people problem i need some help
    i create timing report
    ( Like )
    Real In Time: Employ In Time (Remarks)
    9:00 9:25 late
    9:00 9:28 late
    9:00 9:18 OK
    9:00 9:22 late
    i want formula which show automatically status if employ 20 mint late come

  62. Salma says:

    Good day. Please assist me as i have to insert an IF formula for an evaluation tool. I have an overall average calculated for the tool however i require an IF formula that states if a certain criteria = 0 then the overall evaluation average should =0.

  63. LizT says:

    Hi Svetlana
    I used your instructions for conditional formatting, which worked wonderfully, thank you! However, I'm now stumped as to how to copy these colours over, I will try to explain:
    Used the formatting to fill colour in cells of varying "greater than" amounts relating to an average value which is based on a figure entered each week. So the names of people are down column A and each column after has their weekly score. Column AA has the average, which is coloured accordingly. I'd like to have the cell containing the name of each person to automatically fill with the same colour as the one containing their average score.
    I am a complete novice at this, hope you can help :)

  64. JerryW says:

    I have a column with data in it with 3 rules set up. The rules are if the value is less than 80, highlight the cell in red, if the value is between 80 and 89, highlight in yellow and if the value is between 90 and 100, highlight the cell in green. Everything works as it should. I would like to know if it is possible to create another conditional format rule for all cells to the right of my column with values in it that will highlight the cells for each line of data to change color based on the data column?

    I only want to input values in the one column all the way to the left. The rest of the columns to the right only change color based on that column of cells.

  65. Navneet says:

    please send your mail id. i want to send a spread sheet to you. That's my worksheet for deviation statement for govt sector. i need your hepl to fixing the formula in this worksheet. thanking

  66. JEFF says:

    I want to implement alternating rows conditional formatting only if a field in Column A (A5:A500). Please help how I can achieve that? The formatting that I want to include is adding a border and filling the cell with a colour. Cheers.

  67. Tk says:


    I have a column that I want to apply data bar conditional formatting to, however when I apply the formatting it doesn't appear. The data is calculated from other cells and contains formulas. Kindly assist


  68. Steve McCluskey says:


    I am working on a spreadsheet which is to show delays in projects. I am using conditional formatting formulas to change the colour of the cell directly beneath the planned week number.

    Is there a way to copy this format so that the reference cells are automatically updated as they would be if a copying a normal formula?


  69. Jeff says:

    Hello, I have a large spreadsheet with columns of data (example, K 39,041.00, L 34,584,.25, M 26, N 39,470.04)and would like to highlight cells in both column K & N if column N is greater than column K. I would like to do the same thing (different color) if column L is equal to column K. Can I accomplish this through Conditional Formatting?

    Thanks in advance,

  70. Sandeep says:

    Hi there,

    I have list of codes in tab 1 (approx 100). I want to format the colum A in tab 2 that if I type the code other than mentioned in tab 1 than it should highlight.

    Will conditional format will work on this occasion?

  71. Afaq says:


    I need to give me a reminder or change color of the vehicle ID colomn as soon as it reaches the oil change mileage. I have to develop a worksheet for oil change of fleet of vehicles. The oil change is based on current mileage to next mileage which is (Current Mileage + 7000). Would you help me out with this one.

  72. Ray says:

    I have a worksheet of inventory items. The first column is the stock number the second is the name of the items. The third one is how many we have received. The other columns are items that went to a project, we have approximately 13 projects and a formula for the last two columns which have what has been issued and what is available. The problem arises when my boss wants to lock the third column C which has the number of items purchased that has been sent to us. I have tried using the page protection function, but this locks the whole page not just the items in column C. I need the rest of the page to function while locking a single column. Is that possible and if so how do I do it?

  73. Bhagirath says:

    Hello experts!
    I want a conditional formatting in following context:
    1. I have 6 columns and more than 30 rows.
    2. Column A contains dates in English & column B contains dates in local language.
    3. Column C contains days as "Sun", "Mon"......

    Here, I need a conditional formatting of cells from Column A through Column F if column C contains "Sat". This condition should apply even in blank cells where I need "cell fill color".

    Problem: I can fill color in the cell containing "Sat" but I need the same in entire row from column A up to F.

    Can somebody help me out?

    Thank you

  74. Riyas says:

    I have some numbers,with seperate cells. if i select one digit, automatically
    that shows how many same digits in that page. with Bold & Colored. ?


  75. Sualihat says:

    I have created a sales sheet with description,price,quantity,and total with 5 different product that I sell. Now I want to create another, where when I sell any item the system will automatically calculate the total of so I can save. Thus POS but I will not print a receipt

  76. Lillian Mein says:

    I want a cell to turn red base on a response in a combo box. The formatting works when it is in relation to any other cell or even a drop down list, but it seems as if it is not recognizing the combo box.

  77. surat khan says:

    hi above value which is i want to sum but the value occurs always zero,please get me solution

    • Nitai says:

      Put these values column A
      use below formula and drag down

      then Use Sum function

  78. Nadiger says:

    I have at the top today date

    I have around 30 rows where i have typed different dates. I want to highlight if the row date is less than today date with red font. Kindly help me how to do this.

    Guddappa nadiger

  79. RANJITHKUMAR says:



  80. naveen says:

    how to running value control via condition formatting a1-10then b1 100,c1 200 but my total is 300 i want a1 greater then b1 then highlight b1

  81. rajat mahajan says:

    Hi I excel sheet if there are 10 employees I'd in column and in other excel sheet i want the employee I'd twice. Is there any formula for same

  82. Lauren says:

    How to I highlight a column cell if the minute value is within 15 minutes of another column cell?

    C4 is 9/30/16 12:11
    F4 is 9/30/16 12:23

    C4 is only 11 minutes before F4 and needs to be highlighted.


  83. Chuck says:

    Could you tell me how to use conditional formatting to turn one cell yellow 5 minutes before to five minutes after the time "now()" listed in second or different cell?

  84. Steven Smith says:

    How can I get the total in two cell's to reduce when I add an amount in another cell?

    For example cell H7 is at 100 and cells M7 and N7 are at 20. When I make cell H7 105 how can I automatically get cells M7 and N7 to reduce to 15? The cells are in hours and minutes.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)