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

    I am looking for some pointers for the problem I have. There are three Columns which has values like

    Column_1 Column_2 Column_3 Result
    R239 G232 B110 RGB(239,232,110)
    G210 R134 B156 RGB(134,210,156)

    And the output should be what you see in Result column. Basically R, G and B values are scattered in the first three columns, but I need them in such a way that it looks like what is displayed in the Result column.

    I would really appreciate if someone can look into this. Thank you for looking into it.

    SK

  2. Hi Svetlana

    Hope you can help me...
    I'm using conditional formatting for two columns (ctr and ranking), but need an "if" statement to do the following...

    ctr => 20% + ranking =<3 = green
    ctr 3 = red

    ctr =>8% + ranking between 4-6 = green
    ctr 3% + ranking between 7-10 = green
    ctr <3% + ranking between 7-10 = red

    Can you please help??
    I am on skype if you can inbox me please

    Thanks
    Chantel

  3. the colour of the box which i edited should change the colour automatically to know me which i was edited is there any settings please help me
    TIA

    • Hi Bunny,

      Sorry, it is not possible to do this using conditional formatting. Most likely a special VBA macro is needed.

  4. In my work book i am using 3 columns, first column contains value and second column contains dates of completion fist process and third column contains the date of completion of second operation. how can i use formula to filter what is completed and balance to complete (i meant Backlog) in excel 2013.Please suggest a formula .

    thanks

  5. Hi Svetlana,

    How do I highlight a cell that has a variance of 20% or more when comparing to another cell

    Thanks

    • Hi Stuart,

      You can create a conditional formatting rule with the percentage change formula like this:

      =OR((B2-A2)/A2<-20%, (B2-A2)/A2>20%)

      Where column B contains new values and column A - old values.

  6. * Col-A = Col-B: Yes
    * Col-A Col-B: More
    I don't know why it did not post all 3 rules in here.

  7. (Question 1 above (#43) missing info)
    set 3 rules
    * Col-A Col-B: More
    Example for 2 rules: with "Yes" and "Less/More"
    =IF(A1=B1, "YES", "LESS/MORE")
    How can I break it to "Yes", "Less", "More"?

    Thanks,
    Son

  8. Hi Svetlana,
    I have 2 questions.
    1. I know how to use IF rule with 2 conditions for YES/NO but I don't know how to set a rule for 3 conditions.
    * Col-A Col-B: More
    Example: with "Yes" and "Less/More"
    =IF(A1=B1, "YES", "LESS/MORE")
    How can I break it to "Yes", "Less", "More"?

    2. Can I set multiple "Conditional Formatting" rule in one cell. Example,
    Col-A(number) Col-B(number) Col-C(Y/L/M)
    0 0 Y
    1 2 L
    4 3 M
    I can set Condition Formatting for Col-C with "Equal" to "Y" or "M" or "L" with different colors
    How can I set multiple Conditional Formatting rules for Cell in Col-A
    * A1 "Euqal to" B1: A1 = Green
    * A1 "Less than" B1: A1 = Yellow
    * A1 "Greater than" B1: A1 = Red

    Thank you very much for your help.

    Son

  9. Hi Svetlana,
    I have numbers in a row and i would like to have them summarized in a cell if something in the row has been deleted. For example:
    a1-20
    b1-50
    c1-30
    and i want if a1=0, b1=0, c1=50, then in d1 to have a1+b1 and c1=50
    Thank you,
    Zoya

  10. In a large spreadsheet I would like to apply conditional formatting to any cell that has an absolute value greater than or equal to 5,000,000. I'm having trouble developing that formula and would appreciate assistance if anyone is familiar with how to do this.

    Thank you - Tom

  11. Hello, I hope someone can help me with my problem. I have about 7 different sets of word banks. If a cell contains a word for lets sat word bank 1 I would like that sell to highlight yellow. If a cell contains a word for word bank 2 I would like that cell to highlight red, and so own. Is there a way to do with without making individual conditions for each word?
    Secondly, is it even possible to have a cell highlight any color if one column has three yellow highlighted cells in it? This is based off of my first problem.
    Thank you. I hope I was clear enough.

  12. I have three columns of data with 100 rows each and I want to check if the sum of the row in column A + the row in Column B = the value in the same row of Column C. If it doesn't then I want to RED highlight the row in Column C. For example: A(1)+B(1)?=C(1), A(2)+B(2)?=C(2), etc.
    Thanks,
    Dave

  13. Hi,Svetlana

    I HAVE 100 ROWS WITH ALPHANUMERIC.LIKE FOLLOWING TABLE
    11AS001
    12FB005
    12KA003
    12C40002
    12G3005
    12je001
    12bp005
    124j003
    129j0002
    122q005
    from these table i need to return a value based on the character.like that i have more than 10 values
    ex..
    if cell contains
    as------ A
    ka------B
    g3---C
    c4---D
    fb---E
    4j---F
    je---G
    bp---H
    9J---I
    2q---J

    I want to return a value like this
    Col A ColB
    11AS001--- A
    12FB005---- E
    ..
    Please help me.

  14. How can you edit multiple Conditional Format Rules at the same time?

    I have a row with 36 columns, each with a cell containing 7 conditional formatting rules linking the cell to conditions on cells in a corresponding row on another spread sheet.

    I need the next row to have the same conditional formatting, but referencing the cells in the next row on the other spread sheet. Is there some way to hi-light all conditional format rules on all 36 columns and do a search/replace on all of the formulas simultaneously, or is editing them one-by-one my only option?

  15. how to Automatically change the fill color in a row based on 3(m,n,o) columns current month

  16. Svetlana,

    I want cells in a column to become highlighted if the date listed in a cell is in the past. I tried:

    =$A2<TODAY()

    But this doesn't seem to be highlighting any cells with past dates. Any idea how I can set this up? If the first cell with a date is B4, should the formula go into B3?

    Thanks.

    • Hi Peter,

      You should modify the formula for your first cell with a date, like this:

      =$B4

  17. Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1 Available List:
    And Then you have Sheet 2, Client List:

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf

    As everything Match his requirement.

  18. Area tower Br# Siza Rent Sale Height Furnished View
    Marina Murjan 1+s 985 120,000 high yes marina
    marina mesk 3 2200 340,000 5000000 high no sea
    JLT Goldcrest Studio 650 65000 low no road
    green tanaro 2 1200 95000 2500000 mid yes Golf
    marina majar1 1 1000 105000 low yes marina

    And Then you have Sheet 2, Client List:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf
    michel 115000-125000 marina 1 1000 low yes marina
    joe 200000-300000 marina 3 2200 high no sea
    charlie 55000-60000 JLT studio 650 low no road
    mike 120000-150000 marina 1+s 985 high yes marina

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    greens tanaro 2 1200 95000 2500000 mid yes golf

    As everything Match his requirement.

  19. Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1, Available List:

    Area tower Br# Siza Rent Sale Height Furnished View
    Marina Murjan 1+s 985 120,000 high yes marina
    marina mesk 3 2200 340,000 5000000 high no sea
    JLT Goldcrest Studio 650 65000 low no road
    green tanaro 2 1200 95000 2500000 mid yes Golf
    marina majar1 1 1000 105000 low yes marina

    And Then you have Sheet 2, Client List:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf
    michel 115000-125000 marina 1 1000 low yes marina
    joe 200000-300000 marina 3 2200 high no sea
    charlie 55000-60000 JLT studio 650 low no road
    mike 120000-150000 marina 1+s 985 high yes marina

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    greens tanaro 2 1200 95000 2500000 mid yes golf

    As everything Match his requirement.

  20. hi please help me to create excel sheet that works like this.

    Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1, Available List:

    Area
    Tower
    Br #
    Size
    Rent
    Sale
    Height
    Furnished
    View
    Marina
    Murjan
    1+S
    985
    120,000

    High
    Yes
    Marina
    Marina
    Mesk
    3
    2200
    340,000
    5000000
    High
    No
    Sea
    JLT
    Goldcrest
    Studio
    650
    65,000

    Low
    No
    Road
    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf
    Marina
    Majar 1
    1
    1000
    105,000

    Low
    Yes
    Marina

    And Then you have Sheet 2, Client List:

    Name
    Budget
    Area
    Br #
    Size
    Height
    Furnished
    View
    John
    150000-165000
    Greens
    2
    1200
    Mid
    Yes
    Golf
    Micheal
    115000-125000
    Marina
    1
    1000
    Low
    Yes
    Marina
    Joe
    200000-300000
    Marina
    3
    2200
    High
    No
    Sea
    Charlie
    55000-60000
    JLT
    Studio
    650
    Low
    No
    Road
    Mike
    120000 - 150000

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name
    Budget
    Area
    Br #
    Size
    Height
    Furnished
    View
    John
    150000-165000
    Greens
    2
    1200
    Mid
    Yes
    Golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf

    As everything Match his requirement.

  21. Здравствуйте, Светлана,

    подскажите, пожалуйста, как сделать, чтоб ячейка выделялась красным цветом, если наступила определенная дата? (Excel интерфейс на английском)

    спасибо

    ---English translation---

    Hello Svetlana,

    Please show me how to highlight a cell in red if it contains a certain date? (Excel interface is English)

    thank you

  22. Hello,
    I have an excel sheet with with duplicated item# column and their quantities.
    how can i find duplicates and sum their vlues

    thanking you

  23. Hi..
    I am working on dropdown lists along with conditional formatting. I want the user not to be able to select anything from the next drop down unless the conditional formula is satisfied. Can you help me in this??

    Example:::
    Row 1 Column1 Column2
    Row 2 dropdown1 Condition 1
    Row 3 dropdown2 condition2

    My question is, If dropdown 1 is selected and condition 1 is satisfied, can access dropdown2, else no.

  24. hi my dear friends,
    how are you i need your help

    i want to apply conditional formating in my excel sheet exp:-

    in One Cell I put A ,i want to be in other cell get Apple automatically,how should i used formula & conditional formatting this ,
    can you suggest me please

  25. Hello,

    Is there a way to change the range in the following excel formula from 189 to 3500 in order to capture and sort all of my data throughout my worksheet? Here is how the formula is currently written =SUM(IF('F13 Data'!$BK$2:$BK$189="MATD", 1, 0))+SUM(IF('F13 Data'!$BO$2:$BO$189="MATD", 1, 0))+SUM(IF('F13 Data'!$BS$2:$BS$189="MATD", 1, 0))

  26. Hi

    Is there option to use conditional formatting based on the comment and the symbols(or symbols) used in the comment field on the excel cell?

    Tannk you

    Wojciech

  27. any one reply me ? how can i use the countif in conditional formating?

    Number
    1
    2
    3
    4
    5
    1
    5
    2
    3
    duplicate values will be highlighted. Even if i add new one ?

  28. Can anyone share a solution for this problem.

    Value A - 1000 (Sheet 1)
    Value B - 750 (Sheet 2)
    % From A to B - 75% (Sheet 3)

    The condition is highlight value more than 20%
    But the highlighted cell should be reflected on the Value A (Sheet 1) not on the Percentage Sheet (Sheet 3)

    I am using version 2013 Excel

    Thank you

  29. how to use the conditional formatting for Attendance.?

  30. I am using excel for making out a shift schedule for my company. How do I write a conditional formating formula that would highlight duplicated use of initials in a row then repeat, but independently, highlight of duplicate initials in the next row, the row after that, etc...

    Additionally, the formula would only look at the first 2 characters in a cell to check for duplication.

    Thanks.

    Example of data

    Site1 Site2 Site3 Site4 Site5
    1/1/2015 AA BB CC DD EE
    1/2/2015 BB CC BB AA EE
    1/3/2015 CC CC CC BB AA
    1/4/2015 BB CC BB-t AA EE

    First row has unique initials of AA to EE. There is no error with this row.
    Second row has duplicated BB in two columns that I want the formula to highlight.
    Third row has duplicated CC in three columns that I want the formula to highlight.
    Fourth row has duplicated BB in two columns when you look at the first two characters in the cell that I want the formula to highlight.

    Since this is a day by day basis, creating a conditional formating for each row individually would be unrealistic. The formula needs to look at every single row in the spreadsheet.

    Again, thank you for your help.

  31. Hi Svetlana,
    I am trying to apply conditional formatting for me excel sheet where I would like to highlight the whole row on single cell value for e.g. if $A5 = "Governance" then whole row should be highlighted. I have 250 rows and 10 rules to apply. And all of them are equal priority so not sure how can I use Stop if True rule. Couple of my experienced colleagues have told me that VBA can help you as there are more than 3 rules. What is your suggestion?

    Thanks in advance for your help.
    Sanjeev

    • Hi Sanjeev,

      As far as I know, in Excel 2007 and later you can create more than 3 rules without any problems.

      Stop if true is applied only to speed up the rules processing. If you describe your rules in more detail, we'll probably be able to help you with proper formulas.

  32. I receive an updated spreadsheet every week. The spreadsheet contains approximately 1,000 rows and 77 columns. I am looking for a formula that will tell me which cells have been changed since last week. This would have to include all cells. How can I compare the data from week to week and come up with the items that have been changed (additions to the list, removals from the list, and any changes).

    Thanks so much!

  33. Hi All
    Please help, I need to conditional format one spread sheet with three rules:

    1. All date dates after: 11/11/2014: Green (no problem with this one)
    2. All date dates a month before: 11/11/2014(ie. 10/10/2014): Yellow (problem with this one)
    3. All date dates before: 11/10/2014 (ie before: 10/10/2014): Red (problem with this one)

    Thanks very much for your help

  34. Hi,

    I have some formulated output's in a cell and want to highlight the partial values in output by changing its color.

    Formula used =ROUND(E2,0)&" ( "&ROUND(D2*100,0)&" %)"
    Outcome = 573 ( 57 %)

    desired Outcome is, (57 %) should be in red color.

    Thank in advance

    BR//

  35. I apologize, for some reason it is not displaying the formulas correctly upon hitting submit. Please disregard.

  36. I am unsure if this is the right area, but I need assistance with an issue. I have a column and the cells contain the following formula:

    =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),"1","")

    etc.

    While the formula works perfectly for its intended purpose, the column will not sum at the bottom.

    =SUM($P2:$P696) This always equals zero, even though the cells contain a numerical value. Am I doing something incorrectly?

    • I am unsure if this is the right area, but I need assistance with an issue. I have a column and the cells contain the following formula:

      =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),"1","")

      etc.

      While the formula works perfectly for its intended purpose, the column will not sum at the bottom.

      =SUM($P2:$P696) This always equals zero, even though the cells contain a numerical value. Am I doing something incorrectly?

      • Hello Darcy,

        The reason why the SUM function doesn't work is that your formula returns "1" as a text value. To make it a numeric value, please remove the quotation marks around it, i.e.:
        =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),1,"")

  37. I have 2 Columns of data. I want to 1st column will have a data where the 2nd column's conditional formatting will be based. Is it possible to have this type of formatting:
    if Col1>0, Col2 = YES in red
    if Col1=0, Col2 = NO in green
    if Col1<0, Col2 = NO in green
    Is this type of formatting possible.

    Thanks in advance and all the help.

    • Hello Roumel,

      Assuming Col 1 is column A and Col 2 is column B, create 2 rules with the following formulas for column B (do not include the column header in the rule):

      Red: =$A2>0
      Green: =$A2<=0

      Excel conditional formatting cannot put any text in the cell, but in addition to the above rules, you can enter the following simple formula in cell B2 and then copy it across the entire column B:
      =IF(A2>0, "YES", "NO")

  38. I have 2 rows of dates one is a forecast and another is an actual. I would like to highlight dates that are past due in the forecast column as long as a date in the actual column is blank.

    Forecast Actual
    11/1/2014
    9/5/2014 9/5/2014
    11/10/2014
    10/20/2014 10/20/2014
    10/4/2014
    11/1/2014

    thanks for any help you can provide

  39. Hello Michell,

    Sorry, but your task is not clear. If you could provide more factual data, i.e. what columns contain what values and what the desired result is, we will try to help.

  40. I am trying to come up with a spreadsheet that has the prices that we are paying for products on one line, and on the following lines, the pricing for our customers, which we have different groups for. My question is, if next month certain prices change for us and I adjust the prices we were paying, what formula would i use to make it so the customers prices are automatically adjusted for all the groups i list?

  41. Svetlana,

    Thank you for your recent help with conditional formatting. It was very useful.

    I would like for a cell to be highlighted if the date listed in the cell is prior to today. What formula would I use? Conditional formatting only allows a cell to remain highlighted for a month if you format by date.

    Thanks in advance.

      • will you please tell me that how can i pinup a hidden comment in excel's each cells, which highlight after enter a wrong entry. (which i customized)

  42. hi,
    i have 100 rows with a students marks. i want prepare separate list automatically based on their markslist those who had 0 marks.Please suggest me
    Example
    1.ramu 50
    2.raju 0
    3.suresh 10
    4.ramesh 0

    from the above table i need automatically those who having 0 marks like this
    raju 0
    ramesh 0

    • Hi Purushotham,

      You can apply Autofilter (DATA > Autofilter), then filter the Marks column showing only 0. Alternatively, you can use a VBA script.

  43. Hello Svetlana,
    I was calculating issue date:
    e.g A1 cell has a proposed date.
    C1 cell has a confirmed date. my query is, once the C1 cell is filled A1 will show "Over"/"NA" or vice-verse.

    Pls guide me.

    Thanks.
    Rakesh

  44. How do I set conditional formating on a shared spreadsheet I did not create? The owner of it no longer works with me.

  45. Hello Svetlana,

    I have an Excel sheet with two columns. Each row in each column lists either Y or N. I want to format the sheet so that if a cell in the first column lists Y, and the cell in the second column lists N, the cell in the second column will be formatted so that it changes color. I presume this will require a formula, but I do not know what that would be. Please let me know if it can be done.

    Thanks!

    • Hello PK,

      Select the entire second column and create a rule using this formula:
      =AND($B2="N",$A2="Y")

      Where B2 is the first cell in your second column, and A2 is the first cell in the first column.

  46. Helo all, can i get advice how to I can aplly contidional rule between cells in all rows, no just one, because if i aplly one cells is not work for others, how can i apply conditional rules for it.
    Many thanks

  47. Hello, I basically have 50 worksheets, each work sheet has 2 tables in it. An old one and a new one they all have the same titles etc, I need to compare the data. For instance A2-A43 is the old then A46-A87 is the new. Now what I want to do is if a change is present between the two of them highlight it red...

    Example

    A2 10
    A3 5
    A4 5

    A46 10
    A47 5
    A48 10

    How would I get it so that both A4 and A48 become highlighted, and also if I was to change the cell value to be the same would it then go away? As this is what I'm looking for.

    Regards , please reply today... I am struggling. Alex.

    • Hi Alex,

      Sorry for the delay, I was on vacation. If you are still looking for a solution, here you go:

      - Select your 1st table and create a rule with this formula: =$A2<>$A46
      - Select your 2nd table and create a rule with this one: =$A46<>$A2

      Where A2 and A46 are the first data cells of table 1 and table 2, respectively.

  48. Hello, I'm keeping a database, basically one file is old and one file is new. Is there away that I can get Excel to highlight information which is different on the new file to the old one? Everything has the same titles but a slight code has been changed so this may have an impact of the actual results.

    Alex

  49. Hi there,

    I'm trying to edit XML files onto Excel so that I can read changes into certain events, example... An employee working less hours than they should be. This then needs to show up in some way throughout the whole database. Is this possible? I'm an amateur on Excel, please help.

    Alex.

  50. Hi,

    I'm trying use a formula for working time calculation
    IN OUT TT status
    9:30 AM 6:00 PM 8:30
    9:30 AM 9:00 PM 11:30
    9:30 AM 12:00 AM 14:30
    9:30 AM 3:00 AM 17:30
    9:30 AM 5:00 AM 19:30

    in states Colum i want if
    9:30 AM 6:00 PM 1
    9:30 AM 9:00 PM 1.5
    9:30 AM 12:00 AM 2
    9:30 AM 3:00 AM 2.5
    9:30 AM 5:00 AM 3

    what kind of formula i should use for this

    • Suppose your in time is in a column and out time is in b column. And your data is in columns E F ang G ( G is that column where you have that 1 1.5 values updated) then paste below formula:

      =INDEX($G$1:$G$5,MATCH($A1&$B1,$E$1:$E$5&$F$1:$F$5,0),1)
      after pasting it press CTRL+SHIFT+Enter

    • Hello Zabiulla,

      You can use Anant's solution, or one of the following two options:
      1) Create a helper column and enter the following formula:
      =IF(B10<A10,(B10+1-A10)*24,(B10-A10)*24)

      It will let you get numeric values for the status. Then you can insert a simple IF formula with all combinations of the conditions and copy it down a new column:
      =IF(D10=8.5,1,IF(D10=11.5,1.5,IF(D10=14.5,2,IF(D10=17.5,2.5,IF(D10=19.5,3,"")))))

      2) If you want to get the result right in the status column, you can use the following formula:
      =IF(B10<A10,CHOOSE(INT((B10+1-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3),CHOOSE(INT((B10-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3))

      We hope this helps.

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