How to use conditional formatting in Excel 2010, 2013 and 2016

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 Excel 2007, 2010 2013, and 2016.

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 and Excel 2016

Conditional formatting in Excel 2013 and 2016

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 Excel 2007, 2013 and 2016, 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.
Tips:

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

239 Responses to "How to use conditional formatting in Excel 2010, 2013 and 2016"

  1. Zabiulla says:

    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

    • Anant says:

      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.

  2. Alex says:

    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.

  3. Alex says:

    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

  4. Alex says:

    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.

  5. Lubo says:

    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

  6. PK says:

    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!

  7. Tris says:

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

  8. Rakesh Pal says:

    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

  9. Purushotham says:

    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

  10. PK says:

    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.

  11. Michell Villavicencio says:

    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?

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

  13. Scott says:

    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

  14. Roumel says:

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

  15. Darcy says:

    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?

    • Darcy says:

      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?

  16. Darcy says:

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

  17. Laxmikant says:

    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//

  18. Chimy says:

    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

  19. Lainie says:

    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!

  20. Sanjeev says:

    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.

  21. John says:

    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.

  22. Ram says:

    how to use the conditional formatting for Attendance.?

  23. Blackzorde says:

    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

  24. Ram says:

    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 ?

  25. Wojciech says:

    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

  26. Eddie says:

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

  27. Mohammed says:

    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

  28. Chaitra says:

    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.

  29. ghaleb says:

    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

  30. KML says:

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

    подскажите, пожалуйста, как сделать, чтоб ячейка выделялась красным цветом, если наступила определенная дата? (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

  31. Ahtasham says:

    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.

  32. Ahtasham says:

    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.

  33. Ahtasham says:

    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.

  34. Ahtasham says:

    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.

  35. PK says:

    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.

  36. ujjal saha says:

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

  37. SJ says:

    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?

  38. Purushotham says:

    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.

  39. David says:

    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

  40. tintin says:

    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.

  41. Tom says:

    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

  42. Zoya says:

    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

  43. Son says:

    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

  44. Son says:

    (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

  45. Son says:

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

  46. Stuart says:

    Hi Svetlana,

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

    Thanks

  47. stb says:

    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

  48. bunny says:

    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

  49. chantel says:

    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

  50. sk says:

    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

  51. Purushotham says:

    hi svetlana

    i have 100 rows with different values,some of values are highlighted with conditional formating, i need to show highlighted values only, unhighlighted values are to be in hiding.

  52. Purushotham says:

    Hi svetlana

    i have 5 columns with 5 different subjects.I need to show the student who has below 15 marks in all subjects.
    example
    St name col1 col2 col3 col4 col5
    xxxx 10 20 16 9 18
    yyyy 18 22 17 19 10
    zzzz 20 21 12 19 19
    AAAA 25 21 18 19 19
    BBBB 26 25 19 23 20

    i need to display like this (those who having below 15)
    St name col1 col2 col3 col4 col5
    xxxx 10 20 16 9 18
    yyyy 18 22 17 19 10
    zzzz 20 21 12 19 19

    help me....
    thanks in advance.

  53. Dave says:

    Thanks for all your insight, very helpful. I have a simple budget spreadsheet and have set up a conditional formatting rule that works well. What happens is column B is my target monthly budget. Columns C through M are what I have spent each month. When I go over the number in column B the cell highlights.

    Rule Format Applies to
    Cell Value > $B$2 AaBbCc =$C$2:$M$2

    Works great, what is bugging me is I can’t figure out how to copy and paste to the other rows and change the rule for the “cell value”. It keeps $B$2 but does change the “applies to” cells. I know I could have done each row separately in the time I have spent on this but like I said it is bugging me. Thanks in advance ;}

  54. Jane says:

    I have a spreadsheet several hundred lines of products with the prices from 5 vendors in column . I want to auto-highlight the column that contains the lowest vendor price for each product (that is not zero). How could I get this to work with conditional formatting?

  55. Stuart says:

    Hi Svetlana,
    How do I get a sum for shaded cells of varying colors (and just a shaded cell total not the actual value in it)

    Thanks.

  56. Amy says:

    Its worth a shot... but I am trying to run an audit on a a spread sheet, and am using the Conditional Formatting. I have been trying to high light cells in a Column that is used for Hours submitted on a time sheet. Our company policy is to round to the nearest 15 Minute increment. So I would like to highlight any cell that is not rounded to the nearest 15 minute increment. I read several places how excel divides an hour up in percentages, but cannot find a formula to highlight the field without Rounding it. I am new to the formulas so I need assistance, or, even if you can point me in the right direction...?

    • Eric says:

      Hey Amy,

      Assuming the hours submitted are in decimal form (e.g. 6 or 8.0 or 7.75) you can use the following to accomplish this for a single cell (then use the process outlined in the main article to copy the rule to other cells):
      1. Click the "HOME" tab on the menu bar
      2. Click "Conditional Formatting"
      3. Click "Add Rule..."
      4. Click "Use a formula to determine which cells to format"
      5. In the formula text box paste the following formula and replace "##" with the cell you want to apply the rule to
      =NOT(MOD(##,0.25)=0)
      6. Click "Format" and select a fill color (I picked yellow)
      7. Click "OK" for the format and then again for the rule
      8. Done!

      Now for any cell that the rule is applied to, if the hours recorded aren't rounded (e.g. 8.2) the cell will be highlighted!

      • Eric says:

        I should amend part my last sentence to say "if the hours aren't rounded to the nearest 15 minute increment"

  57. George says:

    Ms. Cheusheva,

    Thank you for an informative explanation on conditional formatting. I do have a question though with my Excel 2007. I have notice that visually if I use the conditional formatting to change the colour of the text in the cell to a colour like red, I can see it in the spreadsheet, but I can’t run any macros or functions against it (like countif = red)? After some checking to see where the problem is, I notice that the text in the cell that even though visually to the eye it is red it has the default code of black when I check the font format? Is this a bug or something that I am missing?

    Cheers,
    George

  58. George says:

    Sorry Ms. Cheusheva,

    I just came across your comment to Stuart above. I will check out that link to see what I can do.

    Cheers,
    George

  59. Alex says:

    Hello,

    Need some help with a formating rule. I have several times in one colum example:
    4:17:49pm
    2:05:03pm
    11:15:04am
    9:00:49am

    And I want to do a color rule to highlited red if the time difference is more that 2 hours apart. How do I do this? Someone please help me out.

    Thank you a bunches
    Alex

  60. Larry says:

    I have three rows of numbers with the highest number highlighted in each row. Can i highlight the lowest number of only the numbers that are already highlighted?

  61. Tina says:

    Hello, I am trying to do conditional formatting on a finacial scorecard. I need to do Red, Yellow Green based on the following criteria.

    Column B is my target revenue - $30,000 - Every column after that is for a week of revenue and I need to color them based on the following guidelines.

    Within 5% - Green
    Within 5-10% - Yellow
    More then 10% - Red

    So if Column D's revenue is $45,000, I need to color it Red. But if column E's revenue is $30,500 it should be green.

    I have tried everything and I am sure I am making a silly mistake. Can you please help?

    Thanks!

    • Eric says:

      Hey Tina,

      I don't know if you're still having this issue, but this may be helpful to other users if you've already figured it out!

      Assuming the following:
      - The first target revenue is in B1 (if not, simply adjust the formulas used below)
      - The first week's revenue is in C1 (same note as above)

      You can do the following to accomplish your goal:
      (With C1 selected)
      We need to make three rules, one for each case. All of the following rules will be added using the following process:
      1. Click the "HOME" tab on the menu bar
      2. Click "Conditional Formatting"
      3. Click "New Rule..."
      4. Click "Use a formula to determine which cells to format"

      ### For red: ###
      Assure that C1 is selected!
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =OR(C1$B1*0.1)
      3. Click “Format…” and set the fill color to red
      4. Personally I set the font color to white to make it more readable, but this isn’t necessary
      5. Click “OK”, you’re done!
      Now when the revenue in C1 is outside a 10% margin of the target revenue, the cell will be filled red! (Note that blank cells will also be filled red, this is covered in the section “Catching blank cells”)

      ### For yellow (very similar to process for red): ###
      Assure that C1 is selected!
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =AND(C1>=$B1-$B1*0.1, C1=($B1-$B1*0.05),C1<=$B1*1.05)
      3. Click “Format…” and set the fill color to green
      4. Click “OK”, you’re done!
      Now when the revenue in C1 is within a 5% margin of the target revenue, the cell will be filled green (be sure to check out the “IMPORTANT” section to make sure this works)!

      ### Catching blank cells: ###
      Assure that C1 is selected!
      You can skip the next section if you want blank cells to be filled red (or don’t mind either way)
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =ISBLANK(C1)
      3. Click “OK”, you’re done!
      Now blank cells will simply be ignored!

      ### IMPORTANT!! ###
      To assure that the rules act as intended please do the following:
      1. Click “Conditional Formatting”
      2. Click “Manage Rules…”
      3. Make sure that the rules are in the following order (distinguishable by the fill color):
      Blank
      Green
      Yellow
      Red
      NOTE: Rules can be moved up or down using the arrow buttons to the right of "Delete Rule"
      4. Make sure that for each rule the “Stop If True” box is checked (far right column)
      This assures that only one rule will be applied to any given cell

      ### Finishing up: ###
      Now that we have all the rules made for C1, we want to be able to apply the same rules to other cells!
      The process for copying conditional formatting is included in the main article, but the process is summarized below:
      1. Select C1
      2. Click “Formula Painter” under the “HOME” tab
      3. Drag from C1 to the end of the range of cells you wish to copy the formatting to (e.g. from C1 to L1 or from C1 to C10 or from C1 to L10)

      That’s it! Please let me know if you (or anyone else reading this) experience any problems!

  62. Kimoy says:

    Hi can you help me with this problem?
    I want to separate the amount in an excel 2007
    For example:

    A1 = 12

    I want the answer would be
    A2 = 5 (Constant/or Keeps the value Lesser or equal to 5 but not negative)
    A3 = the excess amount from the value of A1

  63. Jurgen says:

    Hi, I am trying to create a conditional format the when Cell B2 = or contains a certain word then a particular number goes into cell C2, D2 or E2 depending on what Cell B2 contains.
    ie if B2 = cat then C2 = 1, if B2 = dog then C2 = 1 and D2 = 1 etc

    Thanks in advance

    • Hi Jurgen,

      With conditional formatting you can only "format" cells, i.e. change their background or font color, add icon sets or color scales, etc.

      To put a certain value in a cell depending on another cell's value, you need an IF formula. For example:

      Formula for C2: =IF(OR(B2="cat", B2="dog"), 1, "")

      Formula for D2: =IF(B2="dog", 1, "")

  64. yosef halevy says:

    good day
    excel 2010,
    conditional formatting rules manager
    new rule
    format only cells that contains
    format only cells with
    cells value
    equal to:4 and 5 and 10 and 17 and 35 etc..in the same line
    (not equal to:4
    then
    equal to 5
    equal to 10 in different lines
    all those numbers in one line)
    then format
    fill
    and so on
    is it possible in excel 2010 or 2013.
    thanks
    Halevy yosef

  65. SURAJ KUMAR says:

    I WANT TO KNOW THAT HOW CAN I GET THE HIGHLIGHTED CELL FOR TOP 5 PERSON'S NAME ON THE BASE OF ORDER... EX:-
    CUSTOMER NAME ORDER QUANTITY
    JOHN LARSON 120
    FRANCIS MONTGOMERY 126
    JENNA SAIZ 67
    KELLY PICKETT 98
    MARIA HERNANDEZ 111
    HUGO LAWS 210
    RICHARD BAKER 106
    JOEL CANO 130
    STEVE TAYLER 36
    PLZ.. GIVE A SOLUTION FOR THIS IN A EASILY UNDERSTANDABLE WAY.. THANX IN ADVANCE...

  66. Shannon Murphy says:

    Is there any way to use conditional formatting to show cells that have been edited? I cannot use Track Changes because I do not want to convert my table to range.
    If not conditional formatting, is there any other way to do this?

    • Hello Shannon,

      Conditional formatting can't do this, you need a VBA macro. If you don't need to keep the changes after you close the workbook, you can use the code below:
      - Open your Excel file and press Alt+F11 on your keyboard;
      - Select the necessary sheet in the list and paste this code:
      Private Sub Worksheet_Change(ByVal rnUpdated As Range)
      rnUpdated.Interior.Color = CLng("&H00FF00")
      End Sub

  67. Mzamo says:

    Hi

    How do i copy the colour in a formatted cell to a name cell?
    Thanks

  68. yusuf mariam says:

    Hi
    I have two documents in word containing employee's names but one document starts people's names like Yusuf Mariam and the other Mariam Yusuf so it's confusing me wheather it's one person's name or two people with different names.Please help me with the format i can use to identify them in excel 2013.

  69. Dani says:

    Great Svetlana Cheusheva,

    Hope you will be fine.

    Today I did my best to solve one of my problems regarding conditional formatting and at last when I read your guidelines and instructions step by step. So these procedures were really easy and were found satisfactory and would like to say special thanks & regards to your efforts and technical support to the needy individuals who are facing problems in MS excel.
    Once again thanks
    Regards,

    Cadet Dani

  70. Aaron says:

    Need to lock conditional formatting but still allow values to be pasted into those formatted cells from other sources.

    Problem: I have set up a spreadsheet that highlights cells that need to be populated or left blank. Certain cells that need to be populated are highlighted based on a particular part type (which is chosen from a dropdown menu for the cells located in column AF). Since we are looking over data that can be up to 2000 thousand rows and covering columns A-CL it would take hours to retype all the information to ensure the right columns are populated based on that part type. It is easier to just paste the values in from outside sources. However, it would make it easier to audit the data if the highlighted cells remained highlighted after those values are pasted in.

    Question: Is there a way to lock the formatting while at the same time allowing those conditionally formatted cells (that are highlighted) to have values pasted as an overlay from other sources without deleting the format?

    Thanks,

  71. Md Rais Ahmad says:

    hi anybody can provide me excel data for practise.

  72. Lizl Koekemoer says:

    Hi, is it possible to create a rule to give a value to a work.
    For example: I have a checklist and from a drop down list I can select a number of words, Conformance, Non-conformance and Partial non-conformance, now I would like to automatically have a value assigned to each of these in another column.
    i.e. Conformance = 3; Partial non-conformance = 2 and non-conformance = 0.
    How would I do this.

  73. Lisa says:

    Hi,

    WHAT FORMULA TO USE TO FILL UP THE CELL,THANKS

    SHIPREFT COUNTRY SHIP MODE
    JTWA555666 ? ?
    JSYS2555333 ? ?
    JBKT555999 ? ?

    Ship COUNTRY SHIP MODE
    JSYS Australia SEA
    JTWA TAIWAN AIR
    JBKT THAILAND TRUCK

  74. Navaneeth says:

    Hello,

    Whether can we autocapture the formatted text such as (B,U,L) and replace with some html tags.

    Can i know how to write it.

    Regards,
    Navaneeth

  75. Shiv says:

    I need help in inserting specific comment based on the value of certain cell. E.g.: If the value of cell A1 is greater than equal to 3 then I would like to generate a comment in B1 stating 'reporting not required', so as A1=4, comment in B1 should read as 'reporting required' and so on for different values in A1. Is this possible in Excel? Appreciate your guidance.

  76. Karthik says:

    Hi,

    I have two rows, the first one with various targets/thresholds and the second onwards with achieved values. While I apply the conditional formatting it gets applied to the entire table. Since the targets are different for each row, I could not do it by just formatting the first row and pasting the format for the rest. There should be a work around which I'm not aware. Please help.

  77. JJ says:

    I want to compare one column A of Catalog Numbers containing lot numbers in column B to one column C of Catalog Numbers containing lot numbers in column D and populate the items that match in column E as 0 and the items that don't match in column E as-1 and highlighted in red,

    • Hello,

      If we understand your task correctly and you are trying to compare the lot numbers in column D to lot numbers in column B, then enter the following formula into E1:
      =IF($D1=$B1,0,-1)

      Copy it down the entire column to get the results for all rows.

      Then create a conditional formatting rule with the formula below to highlight those cells in column E that contain "-1":
      =$E1=-1

  78. Karen says:

    First- I want to thank you for all the great tips. I am creating a workbook that has 6 tabs (goals) - each tab containing the goals for a particular metric for a particular region. Each tab is identical in set up but has different values for the goals. I also have 6 tabs with each regions report card. On those tabs i enter the actual result for a metric and use conditional formatting to look at the value to the goals tab for that particular region. If i create one report card i want to be able to copy it 5 times. My problem is the conditional fomatting will still look at the conditional formatting for the first one i created and look at the 1st goal tab. I want to be able to copy the original worksheet but want a quick way to change the worksheet reference in all conditional formatting formulas for a particular worksheet- can this be done?

  79. BIOLA says:

    hello, pls am trying to use a colour code for excel i want to use a data in one column to change the row colour based on the input on any other column like i have a 7 in column b and want it to change to a colour red when i input D in any other column

    • Hello,

      Though you can't create a rule for all columns, you can try listing certain columns that the rule will check. E.g. select column A and create a rule with the following formula:
      =OR($B1="D",$C1="D",$D1="D",$E1="D",$F1="D",$G1="D")
      If any of the listed cells contain "D", it will highlight the cell in column A.

  80. Rajendra says:

    Dear Developer, Often I use conditional formatting at my Excel spreadsheets, however the condition (color) used to explain the reason of a cell or a range, can differ based on the designer and the user of the product/workbook, in this case I have to first train the user, that why I had put that condition to get particular color, Here it is difficult for one to use the spreadsheet without explanation, is there way; where I can put my text Explaining the reasoning?
    Or suggest to add a text field at conditional module to add user thought.so that user can read the reasoning why this condition has been inserted/used

    E.g. We all Universally know that RED means Danger, Yellow means be alert or, Green means OK, but it may differ based on the user prospective, if he does not know what the RED stand for, or Green is OK or v/versa

    In my case color Blue can stand for RED

    Please let me know

    Happy New Year 2016

  81. Waleed says:

    Hi Svetlana, Irina

    I am trying to use conditional formatting of cells using the rule definition as : =($CM:$CM="Red"), but couldn't able to achieve it MS Excel 2010, but it works smoothly in Kingsoft Spreadsheets. Any solution for this ?

    Thanks in advance to you 'Super Girls'.

    • Hi Waleed,

      In Excel conditional formatting rules, cell references are relative to the top-left most cell in the applied range. That is why, instead of specifying the range in the formula, you should supply the top-left cell only. In your case, select the entire column CM, and create a rule with the formula =$CM1="Red"

      Excel will apply the rule to the whole column and adjust the row reference like it does when you copy a formula down a column.

      For more information about cell references, please see Relative and absolute cell references in Excel conditional formatting rules.

  82. HASHID HUSSAIN says:

    we have give formula in column M that if column L in -figure than column M should show Amount Due, Same way if Column N have a received date than column M should change as Received

  83. Matthew Sayle says:

    Hi guys,

    I'm making a spreadsheet for a syndicate I will be doing soon and need help.

    This is the first table of the page

    Name 1 | 1 2 3 4 5 6
    Name 2 | 12 11 10 9 8 7

    This is the second table on the page

    Draw 1 | 1 54 12 6 5 7

    Ok, so on the second table what I want to happen is that when the numbers are drawn I need excel to automatically fill the number in on table one so I know that they've been drawn.

    Please can someone help, I would be truly grateful.

  84. Travis says:

    Privet,

    I have a Column of cells with the following Conditional Formatting:
    =ADDRESS(ROW(),COLUMN())=CELL("address")

    Followed by the simple VBA code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub

    This allows me to hyperlink from one sheet to another and when landing on the cell the hyperlink is attached to it highlights that particular cell on that sheet. Now i was wondering if there is a way that i can link that highlighted cell to other cells so those highlight as well. So when the linked cell highlights the other ones id like to highlight with it, do as well.

    Spaciba

  85. Ian says:

    Hi Svetlana,

    I am having trouble with this.

    I have a spreadsheet that I would like the row to turn green if I put a tick in the tick box I have added to each row.

    Can this be done using conditional formatting or will I need to look at this a different way.

    Thank you for your help.

    Ian

  86. purushotham says:

    Hi svetlana,

    I need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 80-89 grade is A

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4

  87. purushotham says:

    Hi svetlana,

    I need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 60-69 grade is C

    like dat i need grade values

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4
    please help me
    Thanks

  88. purushotham says:

    Hi Irina Pozniakova

    need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 60-69 grade is C

    like dat i need grade values

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4
    please help me
    Thanks

  89. Dawood says:

    its was good . thanks

  90. Naveen says:

    How do i apply this formula in conditional formatting to highlight cells SUMIFS(L:L,J:J,">="&Q7,J:J,"<="&$R7,G:G,$S7,H:H,$T7,$L:$L,$T$1,$L:$L,$T$2)

  91. Ann says:

    There a list of 500 names
    Eg.
    Raju
    Ramesh
    Xyz
    X
    Z

    The name Raju is replaced by the name Ramesh in the whole sheet
    How do we rectify it ?
    In excel

  92. Raj says:

    hi want conditional formating in coloume B < coloum a
    10 9
    20 10
    30 25
    40 18
    50 27
    60 50
    70 55
    80 40
    90 30
    100 150

  93. Vaibhav says:

    Hi people,

    Please help me with this calculation.
    I have 3 range of targets
    A - if the product sold is from 0 to 49 then the amount to be calculated per product is $40
    B - If the product sold is between 50 to 99 then the amount to be calculated per product is $60
    c- If the Product sold is more than 100 then the amount per product will $100

    Please let me know do i do it on excel where i have the column A1 with number of products and B1 with amount earned in total

    Thank You !!!

  94. Stuart says:

    Hi Svetlana,

    How do you add a drop down table to a cell

    Thanks.

  95. nick says:

    I am new to this so pls bear with me.

    I am creating a budget tool - the user will check one of 3 non-numeric options). that option will correspond to a specific budget line item on another sheet. For example my 3 items along with their corresponding values are:

    Fast - $100
    Slow - $50
    Standing - $25

    So I'm looking for a code that will allow me input the correct budget item once the user has checked the appropriate box. I've been looking for help on this and keep coming up with solutions that are simply numeric.

    any help would be appreciated

  96. Kamran Rauf says:

    Please help, i want to put formula background of a cell, like if i enter the digit 10 then it should be automatically multiply with 200 and should come 2000.

  97. Kamran Rauf says:

    Please help, i want to put formula background of a cell, like if i enter the digit 10 then it should be automatically multiply with 200 and answer should come 2000.

  98. Annie says:

    Hi
    Is there a way to apply conditional format within a pivot table but not to values, but to regular columns, and have it stick upon refresh? I've searched and tried everything I can think of to no avail.
    Thanks!

  99. Ishitapathak says:

    how can highlight small a in whole sheet not capital A

  100. Mike says:

    How can I apply a conditional format (the same one) to the same column (in this case column S) across multiple worksheets in the same workbook? I am trying to highlight all values over 1.0 found in column S across 25 different tabs of the same workbook. Thank you.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard