by In this tutorial, we will continue exploring the fascinating world of Excel Conditional Formatting. If you do not feel very comfortable in this area, you may want to look through the previous article first to revive the basics - How to use conditional formatting in Excel.
Today are going to dwell on how to use Excel formulas to format individual cells and entire rows based on the values you specify or based on another cell's value. This is often considered advanced aerobatics of Excel conditional formatting and once mastered, it will help you push the formats in your spreadsheets far beyond their common uses.
Excel conditional formatting based on another cell value
Excel's predefined conditional formatting, such as Data Bars, Color Scales and Icon Sets, are mainly purposed to format cells based on their own values. If you want to apply conditional formatting based on another cell or format an entire row based on a single cell's value, then you will need to use formulas.
So, let's see how you can make a rule using a formula and after discuss formula examples for specific tasks.
How to create a conditional formatting rule based on formula
To set up a conditional formatting rule based on a formula in any version of Excel 2010 through Excel 365, carry out these steps:
- Select the cells you want to format. You can select one column, several columns or the entire table if you want to apply your conditional format to rows.
Tip. If you plan to add more data in the future and you want the conditional formatting rule to get applied to new entries automatically, you can either:
- Convert a range of cells to a table (Insert tab > Table). In this case, the conditional formatting will be automatically applied to all new rows.
- Select some empty rows below your data, say 100 blank rows.
- On the Home tab, in the Styles group, click Conditional formatting > New Rule…
- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Enter the formula in the corresponding box.
- Click the Format… button to choose your custom format.
- Switch between the Font, Border and Fill tabs and play with different options such as font style, pattern color and fill effects to set up the format that works best for you. If the standard palette does not suffice, click More colors… and choose any RGB or HSL color to your liking. When done, click the OK button.
- Make sure the Preview section displays the format you want and if it does, click the OK button to save the rule. If you are not quite happy with the format preview, click the Format… button again and make the edits.
Tip. Whenever you need to edit a conditional formatting formula, press F2 and then move to the needed place within the formula using the arrow keys. If you try arrowing without pressing F2, a range will be inserted into the formula rather than just moving the insertion pointer. To add a certain cell reference to the formula, press F2 a second time and then click that cell.
Excel conditional formatting formula examples
Now that you know how to create and apply Excel conditional formatting based on another cell, let's move on and see how to use various Excel formulas in practice.
Tip. For your Excel conditional formatting formula to work correctly, please always follow these simple rules.
Formulas to compare values (numbers and text)
As you know Microsoft Excel provides a handful of ready-to-use rules to format cells with values greater than, less than or equal to the value you specify (Conditional Formatting >Highlight Cells Rules). However, these rules do not work if you want to conditionally format certain columns or entire rows based on a cell's value in another column. In this case, you use analogous formulas:
| Condition | Formula example |
|---|---|
| Equal to | =$B2=10 |
| Not equal to | =$B2<>10 |
| Greater than | =$B2>10 |
| Greater than or equal to | =$B2>=10 |
| Less than | =$B2<10 |
| Less than or equal to | =$B2<=10 |
| Between | =AND($B2>5, $B2<10) |
The screenshot below shows an example of the Greater than formula that highlights product names in column A if the number of items in stock (column C) is greater than 0. Please pay attention that the formula applies to column A only ($A$2:$A$8). But if you select the whole table (in our case, $A$2:$E$8), this will highlight entire rows based on the value in column C.
In a similar fashion, you can create a conditional formatting rule to compare values of two cells. For example:
=$A2<$B2 - format cells or rows if a value in column A is less than the corresponding value in column B.
=$A2=$B2 - format cells or rows if values in columns A and B are the same.
=$A2<>$B2 - format cells or rows if a value in column A is not the same as in column B.
As you can see in the screenshot below, these formulas work for text values as well as for numbers.
AND and OR formulas
If you want to format your Excel table based on 2 or more conditions, then use either =AND or =OR function:
| Condition | Formula | Description |
|---|---|---|
| If both conditions are met | =AND($B2<$C2, $C2<$D2) |
Formats cells if the value in column B is less than in column C, and if the value in column C is less than in column D. |
| If one of the conditions is met | =OR($B2<$C2, $C2<$D2) |
Formats cells if the value in column B is less than in column C, or if the value in column C is less than in column D. |
In the screenshot below, we use the formula =AND($C2>0, $D2="Worldwide") to change the background color of rows if the number of items in stock (Column C) is greater than 0 and if the product ships worldwide (Column D). Please pay attention that the formula works with text values as well as with numbers.
Naturally, you can use two, three or more conditions in your AND and OR formulas. To see how this works in practice, watch Video: Conditional formatting based on another cell.
These are the basic conditional formatting formulas you use in Excel. Now let's consider a bit more complex but far more interesting examples.
Conditional formatting for empty and non-empty cells
I think everyone knows how to format empty and not empty cells in Excel - you simply create a new rule of the "Format only cells that contain" type and choose either Blanks or No Blanks.
But what if you want to format cells in a certain column if a corresponding cell in another column is empty or not empty? In this case, you will need to utilize Excel formulas again:
Formula for blanks: =$B2="" - format selected cells / rows if a corresponding cell in Column B is blank.
Formula for non-blanks: =$B2<>"" - format selected cells / rows if a corresponding cell in Column B is not blank.
Note. The formulas above will work for cells that are "visually" empty or not empty. If you use some Excel function that returns an empty string, e.g. =if(false,"OK", ""), and you don't want such cells to be treated as blanks, use the following formulas instead =isblank(A1)=true or =isblank(A1)=false to format blank and non-blank cells, respectively.
And here is an example of how you can use the above formulas in practice. Suppose, you have a column (B) which is "Date of Sale" and another column (C) "Delivery". These 2 columns have a value only if a sale has been made and the item delivered. So, you want the entire row to turn orange when you've made a sale; and when an item is delivered, a corresponding row should turn green. To achieve this, you need to create 2 conditional formatting rules with the following formulas:
- Orange rows (a cell in column B is not empty):
=$B2<>"" - Green rows (cells in column B and column C are not empty):
=AND($B2<>"", $C2<>"")
One more thing for you to do is to move the second rule to the top and select the Stop if true check box next to this rule:
In this particular case, the "Stop if true" option is actually superfluous, and the rule will work with or without it. You may want to check this box just as an extra precaution, in case you add a few other rules in the future that may conflict with any of the existing ones.
For more information, please see Excel conditional formatting for blank cells.
Excel formulas to work with text values
If you want to format a certain column(s) when another cell in the same row contains a certain word, you can use a formula discussed in one of the previous examples (like =$D2="Worldwide"). However, this will only work for exact match.
For partial match, you will need to use either SEARCH (case insensitive) or FIND (case sensitive).
For example, to format selected cells or rows if a corresponding cell in column D contains the word "Worldwide", use the below formula. This formula will find all such cells, regardless of where the specified text is located in a cell, including "Ships Worldwide", "Worldwide, except for…", etc:
=SEARCH("Worldwide", $D2)>0
If you'd like to shade selected cells or rows if the cell's content starts with the search text, use this one:
=SEARCH("Worldwide", $D2)>1
Excel formulas to highlight duplicates
If your task is to conditionally format cells with duplicate values, you can go with the pre-defined rule available under Conditional formatting > Highlight Cells Rules > Duplicate Values… The following article provides a detailed guidance on how to use this feature: How to automatically highlight duplicates in Excel.
However, in some cases the data looks better if you color selected columns or entire rows when a duplicate values occurs in another column. In this case, you will need to employ an Excel conditional formatting formula again, and this time we will be using the COUNTIF formula. As you know, this Excel function counts the number of cells within a specified range that meet a single criterion.
Highlight duplicates including 1st occurrences
=COUNTIF($A$2:$A$10,$A2)>1 - this formula finds duplicate values in the specified range in Column A (A2:A10 in our case), including first occurrences.
If you choose to apply the rule to the entire table, the whole rows will get formatted, as you see in the screenshot below. I've decided to change a font color in this rule, just for a change : )
Highlight duplicates without 1st occurrences
To ignore the first occurrence and highlight only subsequent duplicate values, use this formula: =COUNTIF($A$2:$A2,$A2)>1
Highlight consecutive duplicates in Excel
If you'd rather highlight only duplicates on consecutive rows, you can do this in the following way. This method works for any data types: numbers, text values and dates.
- Select the column where you want to highlight duplicates, without the column header.
- Create a conditional formatting rule(s) using these simple formulas:
Rule 1 (blue):=$A1=$A2- highlights the 2nd occurrence and all subsequent occurrences, if any.
Rule 2 (green):=$A2=$A3- highlights the 1st occurrence.
In the above formulas, A is the column you want to check for dupes, $A1 is the column header, $A2 is the first cell with data.
Important! For the formulas to work correctly, it is essential that Rule 1, which highlights the 2nd and all subsequent duplicate occurrences, should be the first rule in the list, especially if you are using two different colors.
Highlight duplicate rows
If you want apply the conditional format when duplicate values occur in two or more columns, you will need to add an extra column to your table in which you concatenate the values from the key columns using a simple formula like this one =A2&B2. After that you apply a rule using either variation of the COUNTIF formula for duplicates (with or without 1st occurrences). Naturally, you can hide an additional column after creating the rule.
Alternatively, you can use the COUNTIFS function that supports multiple criteria in a single formula. In this case, you won't need a helper column.
In this example, to highlight duplicate rows with 1st occurrences, create a rule with the following formula:
=COUNTIFS($A$2:$A$11, $A2, $B$2:$B$11, $B2)>1
To highlight duplicate rows without 1st occurrences, use this formula:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1
Compare 2 columns for duplicates
One of the most frequent tasks in Excel is to check 2 columns for duplicate values - i.e. find and highlight values that exist in both columns. To do this, you will need to create an Excel conditional formatting rule for each column with a combination of =ISERROR() and =MATCH() functions:
For Column A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE
For Column B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE
Note. For such conditional formulas to work correctly, it's very important that you apply the rules to the entire columns, e.g. =$A:$A and =$B:$B.
You can see an example of practical usage in the following screenshot that highlights duplicates in Columns E and F.
As you can see, Excel conditional formatting formulas cope with dupes pretty well. However, for more complex cases, I would recommend using the Duplicate Remover add-in that is especially designed to find, highlight and remove duplicates in Excel, in one sheet or between two spreadsheets.
Formulas to highlight values above or below average
When you work with several sets of numeric data, the AVERAGE() function may come in handy to format cells whose values are below or above the average in a column.
For example, you can use the formula =$E2<AVERAGE($E$2:$E$8) to conditionally format the rows where the sale numbers are below the average, as shown in the screenshot below. If you are looking for the opposite, i.e. to shade the products performing above the average, replace "<" with ">" in the formula: =$E2>AVERAGE($E$2:$E$8).
How to highlight the nearest value in Excel
If I have a set of numbers, is there a way I can use Excel conditional formatting to highlight the number in that set that is closest to zero? This is what one of our blog readers, Jessica, wanted to know. The question is very clear and straightforward, but the answer is a bit too long for the comments sections, that's why you see a solution here :)
Example 1. Find the nearest value, including exact match
In our example, we'll find and highlight the number that is closest to zero. If the data set contains one or more zeroes, all of them will be highlighted. If there is no 0, then the value closest to it, either positive or negative, will be highlighted.
First off, you need to enter the following formula to any empty cell in your worksheet, you will be able to hide that cell later, if needed. The formula finds the number in a given range that is closest to the number you specify and returns the absolute value of that number (absolute value is the number without its sign):
=MIN(ABS(B2:D13-(0)))
In the above formula, B2:D13 is your range of cells and 0 is the number for which you want to find the closest match. For example, if you are looking for a value closest to 5, the formula will change to: =MIN(ABS(B2:D13-(5)))
Note. This is an array formula, so you need to press Ctrl + Shift + Enter instead of a simple Enter stroke to complete it.
And now, you create a conditional formatting rule with the following formula, where B3 is the top-right cell in your range and $C$2 in the cell with the above array formula:
=OR(B3=0-$C$2,B3=0+$C$2)
Please pay attention to the use of absolute references in the address of the cell containing the array formula ($C$2), because this cell is constant. Also, you need to replace 0 with the number for which you want to highlight the closest match. For example, if we wanted to highlight the value nearest to 5, the formula would change to: =OR(B3=5-$C$2,B3=5+$C$2)
Example 2. Highlight a value closest to the given value, but NOT exact match
In case you do not want to highlight the exact match, you need a different array formula that will find the closest value but ignore the exact match.
For example, the following array formula finds the value closest to 0 in the specified range, but ignores zeroes, if any:
=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))
Please remember to press Ctrl + Shift + Enter after you finished typing your array formula.
The conditional formatting formula is the same as in the above example:
=OR(B3=0-$C$2,B3=0+$C$2)
However, since our array formula in cell C2 ignores the exact match, the conditional formatting rule ignores zeroes too and highlights the value 0.003 that is the closest match.
If you want to find the value nearest to some other number in your Excel sheet, just replace "0" with the number you want both in the array and conditional formatting formulas.
I hope the conditional formatting formulas you have learned in this tutorial will help you make sense of whatever project you are working on. If you need more examples, please check out the following articles:
Why isn't my Excel conditional formatting working correctly?
If your conditional formatting rule is not working as expected, though the formula is apparently correct, do not get upset! Most likely it is not because of some weird bug in Excel conditional formatting, rather due to a tiny mistake, not evident at the first sight. Please try out 6 simple troubleshooting steps below and I'm sure you will get your formula to work:
- Use absolute & relative cell addresses correctly. It's very difficult to deduce a general rule that will work in 100 per cent of cases. But most often you would use an absolute column (with $) and relative row (without $) in your cell references, e.g.
=$A1>1.Please keep in mind that the formulas
=A1=1,=$A$1=1and=A$1=1will produce different results. If you are not sure which one is correct in your case, you can try all : ) For more information, please see Relative and absolute cell references in Excel conditional formatting. - Verify the applied range. Check whether your conditional formatting rule applies to the correct range of cells. A rule of thumb is this - select all the cells / rows you want to format but do not include column headers.
- Write the formula for the top-left cell. In conditional formatting rules, cell references are relative to the top-left most cell in the applied range. So, always write your conditional formatting formula for the 1st row with data.
For example, if your data starts in row 2, you put
=A$2=10to highlight cells with values equal to 10 in all the rows. A common mistake is to always use a reference to the first row (e.g.=A$1=10). Please remember, you reference row 1 in the formula only if your table does not have headers and your data really starts in row 1. The most obvious indication of this case is when the rule is working, but formats values not in the rows it should. - Check the rule you created. Double-check the rule in the Conditional Formatting Rules Manager. Sometimes, for no reason at all, Microsoft Excel distorts the rule you have just created. So, if the rule is not working, go to Conditional Formatting > Manage Rules and check both the formula and the range it applies to. If you have copied the formula from the web or some other external source, make sure the straight quotes are used.
- Adjust cell references when copying the rule. If you copy Excel conditional formatting using Format Painter, don't forget to adjust all cell references in the formula.
- Split complex formulas into simple elements. If you use a complex Excel formula that includes several different functions, split it into simple elements and verify each function individually.
And finally, if you've tried all the steps but your conditional formatting rule is still not working correctly, drop me a line in comments and we will try to fathom it out together :)
In my next article we are going to look into the capabilities of Excel conditional formatting for dates. See you next week and thanks for reading!
1645 comments
Hi,
Is there any formula that will define the function of one cell depend upon the result of another cell. If the result of one cell is "Yes" then the formula in another cell will be executed accordingly, otherwise not.
Can you help me?
Thank you
Hi Deepesh,
You can wrap your formula in the IF function to have it executed only when another condition is met. For example:
=IF(A1="yes", formula, "")
A real formula may look similar to this:
=IF(A1="yes", SUM(A2:A4), "")
Thank you So much!!!
Hi Svetlana, my job is to keep track of approaching oil changes needed. I have the (A)column with the Vehicle #s, the(B)column with the current mileage on the vehicles, and the (C)column with the mileage when the oil change is due. I've been trying to find a formula that I could use to automatically highlight the current mileage cells that are within 500 miles of the mileage when oil change is due (C).
I would greatly appreciate your help.
Thank you!
I am trying to create a spreadsheet using multiple numbers as status and I want the entire row to turn the correct color for that status. Example 1 new job want the color to be white, 2 contacted customer want the color to be pink 3 ordered parts want the color to be yellow, etc. to the final 6 green ready to invoice. I will have multiple columns in the row but I want the color to change based on the final column where I update status.
I have been using a simple formula (=D3>C3) and its reverse (=D3<C3) to decide whether a cell is coloured red or green. The rule applies to all the cells in the column (=$D$3:$D$45). I have applied this rule to three columns in my spreadsheet, Columns C,D and E. The formula works fine in Columns C and D, but does not produce any formatting in E. In every way that I can assess, there is absolutely no difference in the cells in the E column than in the others. I have checked everything I can and I just cannot figure out why it will not work.
Can you help me?
Hi Rob,
Just use absolute column references (with the $ sign) like this:
=$D3>$C3 and =$D3<$C3
Hi Svetlana,
I am trying to do highlighted conditional formatting for certain cells. When each cell is equal to 9.5%, it will be highlighted green. I pressed selected each cell using ctrl and entered the conditional formatting desired. However, it is working for every cell but one (it is being highlighted red when the cell is equal to 9.5%). I doubled checked in manager and the rule is correct, so I'm not sure why this is happening!
Any help you can provide would be much appreciated.
Thank you!
Hi Nicole,
I would advise to double check 3 most obvious things:
1. The rule applies to the correct range of cells (not including the column headers if any).
2. If you created a rule with a formula, the formula is written for the left-most cell in the rage. For example, if you highlight rows 2 through 10, you should write the formula for row 2, e.g. $A2=9.5%
3. Check the value in that stubborn cell. It may happen that the cell displays just 1 decimal place, while the real value in the cell is, for example, 9.51%. So, select that cell and view the actual value in the formula bar.
I need to compare 2 columns in 2 tabs in excel
Tab 1 & Tab 2
Column D has project#'s
Column L has dates
- both tabs have the same amount of columns, but count of rows will change
Project#'s are not necessarily in order, but if they do match and the dates in column L match, it is ok.
If the date in column L changes, i would like to see it highlighted in column L in Tab 1.
Any help would be appreciated!!
Here is my dilemma:
I have 1 spreadsheet with 2 specific columns containing times in minutes (Column V and W). I want to generate a conditional format that will highlight the entire row if both column V and W are greater than 60 minutes. I was successful if V alone is >60 using the following formula:
=INDIRECT("V"&ROW())>60
I just can't figure out how to get it to do it for both V and W.
Thanks For the tutorial..
Hi, my data starts in cell A23. I will be adding numbers to column A, that will range from 1 to 300 or sometimes more. Once all of the numbers are entered, I highlight my work sheet starting with Row 23 and Sort by Smallest to Largest. I'm trying to do conditional Formatting (Excel 2007) so that I can immediately spot if I have omitted a number in the sequence. The formula I'm using in A23 is:
$A24-1
Then I choose a color to format the cell if said condition is true.
It shows up in the Conditional Formatting editing bar as the following and is not working:
="$A24-1"
I also have a second condition in Column A for Duplicate Values which is working fine:
=$A$23:$A$526
Could the two be different conditions be the problem?
Thanks!!
Hi, I have spreadsheet with several columns and currently 151 rows. New rows are added regularly. After new row is added, I want to highlight lowest eight (8) values from the last 20 rows in one particular column. So right now, I want to highlight 8 lowest values in range (L132:L151). I know how to use array formula to find those 8 lowest values, but cannot figure out how to highlight them...
Greetings! I read the areticle and most of the questions and responses above and still haven't a clue how to manage this conditional formatting issue.
Working with dates makes it a little more complex, yet I think this can be done:
For a row of data, compare the date in column F to the date in column C and the date in column E. Highlight cell in column F if either or both dates in C and E are later than the date in column F. In other words, the dates in C and E must have occurred before the date in column F.
Example:
C = 3/8/15; E = 3/8/15; F = 3/9/15 == no highlight
C = 3/9/15; E = 3/4/15; F = 3/8/15 == HIGHLIGHT
C = 3/15/15; E = 3/15/15; F = 3/15/15 == no highlight
C = 3/19/15; E = 3/20/15; F = 3/18/15 == HIGHLIGHT
Please help!
Hello Svetlana,
I do not know if this has been asked.
In my spreadsheet I want to highlight rows based on the content of colomn H which contains the open (red) or closed (green) condition.
-Denise
hi,
my data contains lots of row & columns containing Numerical value data like:-
A, B, C, D
90, 80, 50, 40
65, 45, 54, 55
54, 85, 45, 26
what i want is highlight the lowest value in each row in one attempt.
I can highlight the lowest value in each row by applying Conditional formatting formula
=A1=MIN($A1:$D1).by this formula i can highlight each row one by one but i need a formula by which i can highlight all the lowest values in each row in one attempt please help me on this its urgent
Hi Svetlana,
I am trying to implement conditional formatting wherein if a cell contains any of the special characters it should be highlighted..i'm using the "text that contains" option for it..its working fine for every character except *..when i use it..it selects the entire column..any suggestions ?..another thing i wanted to ask was is it possible to do this entire thing through a formula using OR operator rather than setting a condition for each and every special character ??
Thanks,
Nemish
Hi,
can you help please?
In an excel sheet like this and I want to highlight the highest value in each group, what formula should I use?
Value Group
819.23 1
814.08 1
808.47 1
809.33 1
805.7 1
799.23 2
796.79 2
796.73 2
791.25 2
791.97 2
784.52 3
783.13 3
788.61 3
793.16 3
789.4 3
777.52 4
773.03 4
769.87 4
760.69 4
756.2 4
Thank you so much!
Maggie
Hi Svetlana,
Very helpful! In your Formulas to compare values (numbers and text), I didn't see anything to indicate "between." I need conditional formatting in Col C to reference numbers in Col D. Cells in Col C should be light red if the numbers in Col D are between 51-79. How would I write that in a formula? =D1>=51...<=79
Think I got it:
=AND(D1>=51,D1<=79)
Hi Fylum,
Oops, that way my omission. You are right, the AND function works for Between rules, just added it to the corresponding section in the post, thanks!
Hello,
Your blog is very informational. Excel is fascinating. Thank you for posting.
Is there any way to link cells with conditional formatting without Excel displaying the 0.00 and coloring the cells red? Excel 2010
I have a spreadsheet where I have different tabs for each locations and then the last tab is all locations combined. I entered data in the locations combined tab and used the following conditional formatting to mark everything smaller that 0.85 red, but no coloring for the empty cells: AND(D1"",D1<0.85)
It works perfect. HOWEVER, when I link these cells to the ones in the other tabs, all conditionally formatted cells turn red, and show the value 0.00. When I check the formula used for conditional formatting it is the same as the above with the cell name corresponding to the new one.
Thank you in advance for your help.
My requirement is ,
I am trying to create check list . In column level I have all the days from JAN to dec. In row level I have assets to check. I need to highlight cells whcih will be 30 days next to the last updated cell.
eg : I have ABC asset check on 1 st of JAN and the cell coresponds to 30 of JAn should be highlighted , which shows service is pending for that asset.
I am using Excel 2013
When I copy values to cells that already have conditional formatting I find that sometimes the CONDITIONAL formatting is duplicated.
Is there a way of copy / paste that stops CONDITIONAL duplications??
Is there a way to remove CONDITIONAL format duplications
Your help would be appreciated!!
I am almost positive this cannot be done with Conditional Formatting, but I figured I would ask anyways.
Is it possible to Temporarily for a selection of Cells on Sheet2 based on Cell selection on Sheet1? Example: If Cell A1 is selected on Sheet1, I want Cells $A$1:$B$10 to be filled with Yellow, Bold, Italic, and Size 14 Font.
Thanks in advance.
Hi Svetlana, I have created rules that are now working just fine in a table I created (Rules 1-4 below). However, I want to create another formula that will override the first formatting when create a new rule (#5) so that the row turns purple when a new value is the designated column is entered.
Rules:
1) When YES=1.0, row from colA to colK turns GREEN
2) When NO=1.0, row from colA to colK turns RED
3) When ABSTAIN=1.0, row from colA to colK turns LIGHT ORANGE
4) When ABSENT=1.0, row from colA to colK turns LIGHT GRAY
5) When NP=X, row from colA to colK turns PURPLE
Please help!
any formula which help me if i change value of 1st column and then change in amount in other column .....
Pretty great post. I simply stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I'll be subscribing for your rss feed and I hope you write once more soon!
Helpful information. Lucky me I discovered your website accidentally, and I'm stunned why this twist of fate did not came about earlier! I bookmarked it.
Hi,
Good day!
Need help here please. I have data that needs conditional formatting as follows:
If Person 1 achieves 1 to 5 points it will show result "POOR"
But if the Person 1 achieves 6 to 10 points it will show result "GOOD"
But if the Person 1 achieves 11 to 15 points it will show result "VERY GOOD"
But if the Person 1 achieves 16 to 20 points it will show result "EXCELLENT"
Can anyone help me to have the right formula for this? Thanks in Advance guys!
A B C D E F
start end est. work time|work days|days complete|days remaining
2/4/2015 3/18/2015 30 30 10 15
3/5/2015 3/19/2015 10 10 16
2/23/2015 4/3/2015 29 29 27
I NEED COLUMN F TO AUTOMATICALLY CHANGE TO RED OR GREEN. I NEED IT BASED OF COLUMN C.
if F is more than C then I need it to turn green, and if F is less then C I need it turned red......
Please help
I have the same issue. I noticed all her examples are based on ROWS but i need columns.... I need my Column L to be wither RED OR GREEN according to cell Q1 which is a formula for TODAY "=TODAY()" I tried everything it seems to only apply ONE rule but it does not want to apply multiple. have you figured this out?
Thank you, this tutorial was very helpful to me!
I'm trying to apply a conditional format to a cell based on the value of another cell. Here's what I'm trying to do:
if the value in Cell G10 is 0 (based on this formula (f11-g9), then cellH11 will be green. HOWEVER, because cell G10 contains a formula, I can't make it work. If G10 is hardcoded with "0", then it will work. Any ideas?
Hello,
I am trying to use the conditional formatting tool. I have 2 columns quantity ordered, quantity shipped, and the percent difference. I would like the percent difference to be colored red if it is +/-10% and yellow if it is +/-5%. Can you please help?
If some one wants expert view concerning blogging and site-building then i suggest him/her to go to see this blog, Keep up the good work.
I have been exploring for a little for any high quality articles or blog posts on this kind of space . Exploring in Yahoo I at last stumbled upon this web site. Studying this information So i'm happy to convey that I've a very good uncanny feeling I came upon just what I needed. I such a lot for sure will make sure to don?t fail to remember this website and provides it a glance regularly.
I wander if it is possible to conditionaly format the cells in a table where in a top row is writen which cell in a coresponding column (below) will be highlighted (the values in that column are not in direct relation with values in a heading row.
Here is fragment of such table, where i want highlight 1st, 8th and 9th value (which coresponds to the first column (bins) - althogh that column is redundant)
1 8 9
bins A B C
1 70 0 0
2 53 16 0
3 36 20 1
4 32 20 6
5 28 24 7
6 25 27 10
7 16 26 18
8 17 25 21
9 9 25 14
10 7 16 20
thanks a lot for your help.
I was just wondering if it is possible to format a cell (or a whole row in a table ideally) based on an other cell's color? For instance I have a budget sheet with two tables. In my table where I add expenses, I created a Macro to add a new row, and pick a random color for that row. In that row, I have a cell indicating the day of the month that expense is due. I would like it if in my other table, the cell (or row) that corresponds to that date can automatically use the same background color excel formulas: https://www.youtube.com/watch?v=ayVH-Y9-0M4.
I hope I am not being too confusing haha. If so, I would just like to know if I could say: If this cell is that color, then this cell is also that color.
I have a conditional formatting challenge I'm hoping to get some direction on.
I have two columns.
-First column is forecasting a "due" date (this date is auto filled based on a previous start date)
-Second column is requesting the "actual" date
I'd like to conditionally format the second "actual" column/cell to go red in the event that it is blank and that the current date is equal to or passed the "due" column date. Once there is a date in the "actual" column and is no longer blank no color will apply.
Thanks for your help.
I'm hoping to get some help with a conditional formatting problem I'm having.
I have two columns
-First is a forecasting due date.
Two
Thank you for this very detailed tutorial!
I was just wondering if it is possible to format a cell (or a whole row in a table ideally) based on an other cell's color? For instance I have a budget sheet with two tables. In my table where I add expenses, I created a Macro to add a new row, and pick a random color for that row. In that row, I have a cell indicating the day of the month that expense is due. I would like it if in my other table, the cell (or row) that corresponds to that date can automatically use the same background color.
I hope I am not being too confusing haha. If so, I would just like to know if I could say: If this cell is that color, then this cell is also that color.
Thanks!
Hi, I want to conditional formating rows based off of what is in two different columns
Column1 Column2
1 NG
2 NG
1 OK
3 NG
2 NG
4 OK
I have it set up right now that if its OK it hightlights the row gray and if NG it shows orange. I want to make it that if another entry is entered and it is OK but the first time it is NG it will change the row to gray. So for the example the first row 1 is orange because of NG but when you add in the second 1 with OK it will change the first row 1 to gray also.
Thanks for the help!!
Hi there! I could have sworn I've been to this blog before but after browsing through some of the post I realized it's new to me. Nonetheless, I'm definitely delighted I found it and I'll be book-marking and checking back often!
Hi Svetlana,
I have a table in excel. The cells in the table contain formula. When I click on one cell(say cell B2) how can I get those cells in the table highlighted where the values are greater than equal to {59/(clicked cell value)}.
I am trying to use conditional formatting but can't implement both cell reference and condition function.
Please help.
Thank you...
Hi!
I have a problem with conditional formatting. I have columns A and B.
Row; Column A ; Column B
1; -0,87 ; -0,96
2; -0,57 ; -0,23
3; -0,66 ; -0,65
4; -0,74 ; -0,76
...
300; -0,78 ; -0,87
I have altogether 300 rows and two columns with changing values. I would like to highlight the values in Column B in green or red whether the value is greater than or less the value in Column A at same row that the value of Column B is. I know it is possible to manually create the rule for each cell separately, but I have 300 (!!!) rows. Can the rule be copied to other cells so that the reference changes like the rows change (Column B3 refers to value in Column A3 and B5 to A5 etc.)? So I don't have to create the rule for each cell separately.
Thanks in advance!
Hi there I need solution for
If cell A10 = any value
Then cell A11 = 345 (fixed value)
Any 1 can help?
Can someone help me with this conditional formatting problem?
I have a spreadsheet as follows:
Col A Col B
10 74%
9 72%
8 70%
7 68%
6 66%
5 64%
4 62%
3 60%
2 57%
1 54%
Raw Score 57%
Rating 2
In general, how can I highlight the specific cell in Column B where it corresponds to the Rating of 2 (in Column A and reported in cell B13)?
In this example I want to highlight the cell containing 57% in Column B since it corresponds with the Rating of 2 in Column A (and reported in cell B13).
Thanks.
Hello Alan,
Select the range from B2 till B11 and create a conditional formatting rule using this formula:
=$A2=$B$13
Hi,
In Cell M4 I need to calculate whether a particular price (which is in another tab called pricing matrix)should be shown dependent on the figures in cell range C3:I52 (if these cells are zero value then the price in M4 is zero.
However if there is a Y in any cell within the range K5:K52 then the price in M4 should be shown. I have inputted the below formula which sort of works however the cell M4 has lost the formatting for currency and decimal places. I have tried conditional formatting (I am no pro!) to no avail.
CONCATENATE(IF(SUM(C3:I52)>0,'Pricing Matrix'!D26,0)&IF(K5="Y",'Pricing Matrix'!$D$26,0))
Please help!
Many thanks
Catherine
Hi
I am trying to show icons for what I would think would be a simple task but I can't seem to make it work.
In column "N" I have a current rate percentage. In column "O" I have a target rate percentage. I want to use the icons to show a green check if the target has been met, a yellow exclamation point if the target has not been met, and a red stop light if the current rate is 50% or more below the target rate. Basically just showing which rates have been met, are almost there, and are in danger of not being met.
Can anyone help with this?
Thanks,
Heather
Hi Heather,
If you're content with just using color indicators instead of icons, the following should help:
***Please note, I'm using Office 2007, so this answer may vary depending upon which version you're using.***
Assuming your data starts on row 2 (N2 & O2 in the example you describe; perhaps because you have a column header title), you should be able to get the appropriate colors to show by entering the following formulas into the Conditional Formatting -> "Use a formula to determine which cells to format" in this order:
1) =$N2 Fill -> Background Color to RED)
2) =$N2 Fill -> Background Color to YELLOW)
3) =$O2 Fill -> Background Color to GREEN)
Unfortunately, it'll take me a bit more time to figure out the solution using the icons you requested. I'm actually at work right now, was on this blog searching for an answer to a question of my own, ran across your question & figured I may be of service within a reasonable amount of time.
I hope that helps.
Best regards,
Randy
Hi again Heather,
I see that the equations I typed got messed up (not sure why), so I'm going to attempt to type these again, this time with spaces between items. Please note that the other info remains the same.
1) = $N2 < $O2 / 2
2) = $N2 < $O2
3) = $O2 <= $N2
Best regards,
Randy
Thank you Randy! I will give that a try. My boss had her heart set on using the specific icons instead of colors, however I think I have convinced her that it just doesn't seem possible. :)
Sorry for the delayed response, been in training.
Heather
I am wanting to conditionally format case sensitive values. For example, I want upper case "F" to be in green text and lower case "f" to be in blue text. I am using Excel 2010 and so far have not been able to google-cure my problem. Can you help?
Hello Sarah,
Please use the Exact function and create two rules using these formulas:
Green: =EXACT(H8, "F")
Blue: =EXACT(H8, "f")
Alexander, thank you for the help. I discovered that my problem was not related to the formula (as I had tried the one you gave above) but that in place of the cell number I was putting in the full array of cells i wanted the formula applied to. I should have left it as exactly the formula you have above and then applied that formula to the array of cells, not actually include the array of cell names in the formula.... if you get what i mean.
Thanks for your help!!!
I have two columns, column A has various dates entered, column B has a formula =A1+30 meaning I want to see a date that is 30 days past the date in column A. I would like to add a conditional format to highlight the cells in column B when they have reached that 30 day mark. How do I do that?
Hello Betty,
Select column B and create a rule using the formula below to mark only today's date:
=B1=today()
If you need to mark today and past dates, then please use this formula:
=B1<=today()
Hi Svetlana,
A very happy new year to you!!
Please can you help on below query.
Date From Organisation Details/Requirements Sent to Report Status
10/02/2015 Pending
14/02/2015
A is a date column that records the email receive date and F shows the status pending.
Now i want F to turn "pending" word in red if it crosses 5 days from date in A column. Please can you advise if i can conditional format it and how.
Thanks,
Rachana
Hello Rachana,
Please select your column F from F2 to the end and then create a conditional formatting rule using the following formula:
=AND($F2="pending",$A2+5
Hi Svetlana, Thanks for this article, it's very helpful. I just wanted to ask you how to highlight one empty cell in a column (say "B") when other cell(s) (one or more, say "A" & "C") in the same raw is filled with some values. And the highlight remains until the cell in "B" is filled with some values. Is that possible? your reply will be very helpful and is highly appreciated. Thanks in advance..
Hello John,
Select column B and create a rule using this formula:
=AND($B2="",$C2<>"",$A2<>"")
Hello Alexander,
Thank you very much for the formula, this is the exact one which I've been looking for. It really works !!! Thank you once again for your time for helping me...
Hi. This is a very helpful article. I am having difficulty solving this. I have a table where every cell is a formula. Some are straight references to cells, "=C2", and some are vlookups, "=VLOOKUP(C2,...)".
I would like to highlight the cell only if the formula is a vlookup. Everything I see indicates that the conditions refer to the calculated values not the formulas themselves. I cannot use ISFORMULA() since all cells have formulas.
I appreciate any help.
Hello Curt,
If you use Excel 2013, then you are lucky. To fulfill your task, just select the table and create a rule using this formula:
=ISERROR(SEARCH("vlookup(",FORMULATEXT(A2)))=FALSE
Where A2 is the top-left cell in the selection.
If you have Excel 2010 or earlier, then you need to replace FORMULATEXT with VBA user-defined function (UDF)
Function formulatext2(cellSrc As Range)
formulatext2 = cellSrc.Formula
End Function
If you are not quite familiar with VBA, please see how to add a VBA macro (UDF) to your worksheet here:
https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/
Replace FORMULATEXT with FORMULATEXT2 correspondingly:
=ISERROR(SEARCH("vlookup(",FORMULATEXT2(A2)))=FALSE
I appreciate the quick and helpful reply. I do have Excel 2013 and your solution worked perfectly. Unfortunately, my client uses Excel 2010 and will not allow vba in the workbook I am creating. So I am back to square one. If you have other suggestions I would be anxious to try them.
Thanks.
I am sorry, but I don’t have any other suggestions.
So if i want color fill in other cells by follow the date that i am input, How can i setup the formatting formulas?