May
29

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:

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

  1. Steven Smith says:

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

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

  2. prashant johari says:

    which formula we used in condition formation to coloring the amount is greater than 30 and less than 61

  3. Chuck says:

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

  4. Lauren says:

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

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

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

    Thanks

  5. Teng says:

    Hi
    why my file cannot save the custom conditional formatting?

  6. rajat mahajan says:

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

  7. naveen says:

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

  8. RANJITHKUMAR says:

    IN EXCEL ONE COLUMN MATERIAL SEND DATE,NEAREST COLUMN DATE TODAY ,NEXT COLUMN COUNT DATE FROM 1 TO 2 COLUMN,NEXT COLUMN DAYS>180 MEANS INDICATES RED
    DAYS<180 MEANS INDICATES GREEN

    NEXT COLUMN ACTUAL DATE OF RECEIVING,NEXT COLUMN STATUS OPEN/CLOSED(IF MANUAL ENTER ACTUAL DATE OF RECEIVING MEANS)THAT AUTOMATICALLY COMES CLOSE THEN DATE FORMULA WILL NOT UPDATE FOR THAT PARICULAR ROW

  9. Nadiger says:

    I have at the top today date

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

    Regards,
    Guddappa nadiger

  10. surat khan says:

    884,00
    125,00
    4,15
    4,15
    1,00
    9,46
    1,56
    1,13
    25,00
    3,75
    1,00
    1,00
    1,13
    5,00
    6,88
    2,36
    1,06
    2,55
    2,48
    6,16
    7,50
    1,88
    102,50
    168,75
    0,37
    0,11
    0,92
    2,31
    7,50
    0,46
    1,13
    3,75
    6,50
    3,40
    1,31
    26,25
    1,31
    1,75
    25,00
    3,90
    hi above value which is i want to sum but the value occurs always zero,please get me solution

    • Nitai says:

      Put these values column A
      use below formula and drag down

      =VALUE(SUBSTITUTE(A1,",",""))
      then Use Sum function

  11. Lillian Mein says:

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

  12. Sualihat says:

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

  13. Riyas says:

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

    please

  14. Bhagirath says:

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

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

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

    Can somebody help me out?

    Thank you

  15. Ray says:

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

  16. Afaq says:

    Hi,

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

  17. Sandeep says:

    Hi there,

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

    Will conditional format will work on this occasion?
    Regards,,

  18. Jeff says:

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

    Thanks in advance,
    Jeff

  19. Steve McCluskey says:

    Hi,

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

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

    Thanks
    Steve

  20. Tk says:

    Hi

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

    Regards

  21. JEFF says:

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

  22. Ray says:

    Thank you Irina.

  23. Amit says:

    I want use to Conditional formatting

    If A Column is "Sunday",

    Then B Column Fill Red color. Is this possible

  24. VHaig says:

    Is it possible to get copies of the workbooks you use in your conditional formatting articles so I can practice?

  25. Navneet says:

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

  26. JerryW says:

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

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

  27. LizT says:

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

  28. Salma says:

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

  29. M zaid says:

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

  30. vikram says:

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

  31. David Keen says:

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

    Many thanks

    • Hi, David,

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

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

      • David Keen says:

        Natalla

        Many thanks for your advice, much appreciated.

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

        I am using Excell for Mac 2011 version 14.7.3

        Apologies for any misunderstanding and thanks once again

  32. Raluca says:

    Hi,

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

    Many thanks

  33. Edilberto Riel says:

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

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

  34. nirpinder says:

    hi

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

  35. Nabeel says:

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

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

    Hello!,

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

    Thanks

  37. Justin says:

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

    I apologies if this doesn't make sense

  38. Nitai says:

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

    Thanks in advance.

  39. Ray says:

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

  40. Anthony says:

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

  41. Kieran Yeow says:

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

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

    Can someone please help

  42. Carlos Currea says:

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

  43. D Hilton says:

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

  44. Kenny says:

    How do I get excel to calculate the difference in dates in days?

    Thank you

    Kenny

  45. MONIK says:

    R/MAM,

    GOOD NOON,

    PL NOTE THAT A1 CELL HAS A VALUE "X", I WANT TO HIGH LIGHT CELL B1 WHEN IT'S VALUE LESS THAN 10% OF CELL A1.

    IN FORM OF MATHS
    CELL B1 SHOULD BE HIGHLIGHT WHEN
    A1 = < A1-(A1*0.1)

    HOW CAN I DONE BY CONDITIONING FORMATTING FACILITY?

    REGARDS,

    MONIK

  46. MONIK says:

    KINDLY IGNORE A1 =.

    THANKS

  47. Munir says:

    Hello Friends!

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

    M. Munir

  48. Tarique Khan says:

    Hello Experts..

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

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

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

  49. Ashok Sahu says:

    Sir/Mam
    I have 2 question.

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

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

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

  50. Karam says:

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

  51. Tarique Khan says:

    Hello Dear Experts..

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

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

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

    Awaiting for your response

  52. Brad Carlisle says:

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

  53. Emily says:

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

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

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

    Thanks so much!

  54. Rasheed Ibrahim says:

    Hi

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

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

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

    thank you

  55. Dragon says:

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

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

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

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

  56. swaroop says:

    Hi

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

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

  57. Ben says:

    Privet vsem!

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

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

    Ben

    • Hello, Ben,

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

  58. Sophie says:

    Hi all,

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

  59. Mohsin says:

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

  60. Ian says:

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

  61. Fong says:

    Hi,

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

    Thanks

  62. Carine says:

    Hi,

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

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

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

  63. Amy says:

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

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 for Excel Professionals
 
 
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