Excel Conditional Formatting tutorial with examples

The tutorial explains all main features of Excel conditional formatting with examples. You will learn how to do conditional formatting in any version of Excel, efficiently use preset rules or create new ones, edit, copy and clear formatting.

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 spot variances of cell values with a quick glance.

Many users, especially beginners, find it intricate and obscure. If you feel intimidated and uncomfortable with this feature, 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 tutorial :)

What is conditional formatting in Excel?

Excel Conditional Formatting is used to apply certain formatting to data that meets one or more conditions. Just like usual cell formatting, it lets you highlight and differentiate your data in various ways by changing cells' fill color, font color, border styles, etc. The difference is that it is more flexible and dynamic - when the data changes, conditional formats get updated automatically to reflect the changes.

Conditional formatting can be applied to individual cells or entire rows based on the value of the formatted cell itself or another cell. To conditionally format your data, you can utilize preset rules such as Color Scales, Data Bars and Icon Sets or create custom rules where you define when and how the selected cells should be highlighted. Excel conditional formatting

Where is conditional formatting in Excel?

In all versions of Excel 2010 through Excel 365, conditional formatting resides in the same place: Home tab > Styles group > Conditional formatting. Conditional formatting on the Excel ribbon

Now that you know where to find conditional formatting in Excel, let's move on and see how you can leverage it in your daily work to make more sense of the project you are currently working on.

For our examples, we will use Excel 365, which seems to be the most popular version these days. However, the options are essentially the same in all Excels, so you won't have any problems with following no matter what version is installed on your computer.

How to use conditional formatting in Excel

To truly leverage the capabilities of conditional format, you need to learn how to utilize various rule types. The good news is that whatever rule you are going to apply, it defines the two key things:

  • What cells are covered by the rule.
  • What condition should be met.

So, here's how you use Excel conditional formatting:

  1. In your spreadsheet, select the cells you want to format.
  2. On the Home tab, in the Styles group, click Conditional Formatting.
  3. From a set of inbuilt rules, choose the one that suits your purpose.

    As an example, we are going to highlight values less than 0, so we click Highlight Cells Rules > Less Than… Using preset Highlight Cells Rules
  4. In the dialog window that appears, enter the value in the box on the left and choose the desired format from the drop-down list on the right (default is Light Red Fill with Dark Red Text).

    When done, Excel will show you a preview of formatted data. If you are happy with the preview, click OK. Preview of the conditionally formatted data

In a similar manner, you can use any other rule type that is more appropriate for your data, such as:

  • Greater than or equal to
  • Between two values
  • Text that contains specific words or characters
  • Date occurring in a certain range
  • Duplicate values
  • Top/bottom N numbers

How to use a preset rule with custom formatting

If none of the predefined formats suits you, you can choose any other colors for cells' background, font or borders. Here's how:

  1. In the preset rule dialog box, from the drop-down list on the right, pick Custom Format… Setting up custom formatting
  2. In the Format Cells dialog window, switch between the Font, Border and Fill tabs to choose the desired font style, border style and background color, respectively. As you do this, you will immediately see a preview of the selected format. When done, click OK. Choosing the fill color
  3. Click OK one more time to close the previous dialog window and apply the custom formatting of your choice. Custom format is applied to the selected cells.

Tips:

  • If you want more colors than the standard palette provides, click the More Colors… button on the Fill or Font tab.
  • If you wish to apply a gradient background color, click the Fill Effects button on the Fill tab and choose the desired options.

How to create a new conditional formatting rule

If none of the preset rules meets your needs, you can create a new one from scratch. To get it done, follow these steps:

  1. Select the cells to be formatted and click Conditional Formatting > New Rule. Create a new conditional formatting rule in Excel.
  2. In the New Formatting Rule dialog box that opens, select the rule type.

    For example, to format cells with percent change less than 5% in either direction, we choose Format only cells that contain, and then configure the rule like shown in the screenshot below: Choose the conditional formatting rule type.
  3. Click the Format… button, and then choose the Fill or/and Font color you want.
  4. Click OK twice to close both dialog windows and your conditional formatting is done! New conditional formatting rule is created and applied to the selected cells.

Excel conditional formatting based on another cell

In the previous examples, we highlighted cells based on "hardcoded" values. However, in some cases it makes more sense to base your condition on a value in another cell. The advantage of this approach is that irrespective of how the cell value changes in future, your formatting will adjust automatically to respond to the change.

As an example, let's highlight prices in column B that are greater than the threshold price in cell D2. To accomplish this, the steps are:

  1. Click Conditional formatting> Highlight Cells Rules > Greater Than…
  2. In the dialog box that pops up, place the cursor in the text box on the left (or click the Collapse Dialog icon), and select cell D2.
  3. When done, click OK.

As a result, all the prices higher than the value in D2 will get highlighted with the selected color: Create a conditional formatting rule based on another cell value.

That is the simplest case of conditional formatting based on another cell. 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 here:

Apply multiple conditional formatting rules to same cells

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

For example, you can create 3 rules to highlight prices higher than $105 in red, higher than $100 in orange, and higher than $99 in yellow. For the rules to work correctly, you need to arrange them in the right order. If the "greater than 99" rule is placed first, then only the yellow formatting will be applied because the other two rules won't have a chance to be triggered - obviously, any number that is higher than 100 or 105 is also higher than 99 :)

To re-arrange the rules, this is what you need to do:

  1. Select any cell in your dataset covered by the rules.
  2. Open the Rules Manager by clicking Conditional Formatting > Manage Rules…
  3. Click the rule that needs to be applied first, and then use the upward arrow to move it to top. Do the same for the second-in-priority rule.
  4. Select the Stop If True check box next to all but the last rule because you do not want the subsequent rules to be applied when the prior condition is met.
Arrange the conditional formatting rules in the right order

What is Stop if True in Excel conditional formatting?

The Stop If True option in conditional formatting prevents Excel from processing other rules when a condition in the current rule is met. In other words, if two or more rules are set for the same cell and Stop if True is enabled for the first rule, the subsequent rules are disregarded after the first rule is activated.

In the example above, we have already used this option to ignore subsequent rules when the first-in-priority rule applies. That usage is quite evident. And here are another couple of examples where the use of the Stop If True function is not so obvious but extremely helpful:

How to edit Excel conditional formatting rules

To make some changes to an existing rule, proceed in this way:

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

    That dialog window looks very similar to the New Formatting Rule dialog box used for creating a new rule, so you won't have any difficulties with it.

Tip. If you don't see the rule you want to edit, then select This Worksheet from the Show formatting rules for drop-down list at the top of the Rules Manager dialog box. This will display the list of all the rules in your worksheet.

How to copy Excel conditional formatting

To apply a conditional format you've created earlier to other data, you won't need to re-create a similar rule from scratch. Simply use Format Painter to copy the existing conditional formatting rule(s) to another data set. Here's how:

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

    Tip. To copy the formatting to multiple non-contiguous cells or ranges, double-click Format Painter.

  3. To paste the copied formatting, click on the first cell and drag the paintbrush down to the last cell in the range you want to format. Copy conditional formatting to another range of cells.
  4. When done, press Esc to stop using the paintbrush.
  5. Select any cell in your new dataset, open the Rules Manager and check the copied rule(s).

Note. If the copied conditional formatting uses a formula, you may need to adjust cell references in the formula after copying the rule.

How to delete conditional formatting rules

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

  • Open the Conditional Formatting Rules Manager, select the rule and click the Delete Rule button. Delete a conditional formatting rule.
  • Select the range of cells, click Conditional Formatting > Clear Rules and choose the option that fits your needs. Clear conditional formatting rules in Excel.

This is how you do conditional formatting in Excel. Hopefully, these very simple rules we created were helpful to get a grasp of the basics. Below, you can find a few more tutorials that can help you understand the inner mechanics and expand conditional formatting in your spreadsheets far beyond its traditional uses.

You may also be interested in

Table of contents

308 comments

  1. Hello,

    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!

    Rules:
    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. I need to highlight a cell that doesn’t contain 48

  3. Hi
    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?

      • Hi Alexander
        For example
        Column A will have a list of % ranges that managers can choice to apply a % increase within:
        1-2%
        2.25-2.3%
        2-3%
        And so on

        In Column B, I only want them to choice a % within that range. If they go over I’d like it to flag up/highlight

  4. 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,
    Le

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

    Example:
    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

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

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

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

  9. Difference between conditional formatting and regular formatting

  10. ***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.
    Thanks.

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

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

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

  14. 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?

  15. how to copy conditional format from one cell to rows, which drag formulas to rang of rows

  16. how to copy condintion format from one cell to rows

  17. 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?

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

  19. 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 ?
    Thanks

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

  21. i want select the one colour gradient bt only two colour is available how i can choos one colour or preset

  22. I want to color max value. but unable to color max value like that one (1). how to do it

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

  24. Hello,
    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:

    "Transaction
    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?

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

    • Dinesh:
      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.

  26. 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?

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

  28. 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?

    • Josh:
      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:
      =AND(H48>TODAY(),H48=(TODAY()+75))
      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.

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

  30. Hey,

    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

  31. Thank you very much for these tutorials, they are so useful!!!!

  32. How To Highlight Row If Cell Contains Text/Value/Blank In Excel

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

  34. Hello,

    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?

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

  36. Correction
    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 mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

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

  38. 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 support@ablebits.com. 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.

  39. Hi,

    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)

    • Thanks in advance :-)

  40. Hi,

    I am trying to highlight number of cell with the number i keyin, what is the formula for this situation?

    Thanks

  41. Hello,
    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.

    • Hello, Ian,

      Please try the following formulas:

      Rule1=AND(DATEDIF($A2,TODAY(),"d")>=1,DATEDIF($A2,TODAY(),"d")<=89)
      Rule2=AND(DATEDIF($A2,TODAY(),"d")>=90,DATEDIF($A2,TODAY(),"d")<=275)
      Rule3=DATEDIF($A2,TODAY(),"d")>=276

      Hope it will help you.

  42. Hi,
    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.

  43. 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!

  44. 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 :).

    Ben

    • 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 support@ablebits.com. Please include the link to this comment into the email.
      Our technical specialist will take a look at your task and try to help.

  45. Hi

    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

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

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

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

  47. Hi

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

    EG
    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

  48. Hello,
    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

    C=orange
    K=Blue
    B=Purple
    N=Red

    Thanks so much!

  49. Hello,
    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.

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

    • Hello,
      you can add IF function to your formula in P3:
      =IF(ISBLANK(O3),"",O3-TODAY())

      It will remain empty if there's nothing in O3, otherwise calculate the date.
      Please read this article to learn why and when IF can be used.
      Hope this is what you need.

      • Yes it's working, Thank you very much

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 :)