This tutorial explains how to use Excel formulas to format cells and entire rows based on the values you specify or based on another cell's value, and provides a handful of formula examples. Continue reading
by Svetlana Cheusheva, updated on
This tutorial explains how to use Excel formulas to format cells and entire rows based on the values you specify or based on another cell's value, and provides a handful of formula examples. Continue reading
Comments page 6. Total comments: 1062
Hiya,
I wondered if anyone could assist ? I am trying to do some conditional formatting as per below :
IF values in P13 is less than 100% AND the date in Column D is Less than today - make the whole line red with black text. I am using the below formula and choosing the format but it just doesn't seem to do anything ! Confused !
=AND($P13"<100",$d13<TODAY())
Thanks in advance
G
Sorry mis-copied formulae I am using I have :
=AND($P13="<100",$D13<TODAY())
Hello,
I have a spread sheet that tracks vehicle hours. In column B is the vehicle hours which are updated weekly. In column G is the last service hours. Starting at row 2 I would like G2 to be green when B2 is equal to or greater than G2 and less than G2+499, would like G2 to be orange when B2 is greater than G2+500 but less than G2+999 and would like G2 to be red when B2 is greater than G2+1000.
Could you help me with the formular please as no mater what I try I can't seem to get it to work.
Thanks
Hello, Carl,
you need to create the rules for conditional formatting that would apply to G2 (G:G if for the column). Try the following rules:
For green:
=AND(B2=G2,B2<(G2+499)) For orange: =B2>(G2+500)
For red:
=B2>(G2+1000)
Hope this helps!
Hi, I want to use conditional formatting to highlight a cell a particular colour dependant on whether other cells are filled in.
To put into context, I use to spreadsheet as a checklist for traders:
Trader Name - Risk Assessment Received - Hygiene Certificate etc..
Then once I have checked the box to show I have received all the information I want the cell at the end of the row to turn green after all the cells in the range are shown to not be empty.
I have achieved this for one row using a method listed here. However, I have several rows I want this to apply to and if I copy/paste or drag down it doesn't apply to formatting to the specific row, it just bases it on the first row.
I hope this makes sense.
Hi, Dom,
Supposing your data ranges from A to H column, try this formula as a formatting rule:
=COUNTIF($A2:$H2,"")=0
where "=0" checks whether the cell is blank. Notice, that we use absolute references for columns and relative for rows (to apply the rule to the next rows of the same column). Also, keep in mind that the formatting rule may multiply itself, when you drag it to another cell. For more info: COUNTIF.
Is there a way to highlight 'A-F' if 'B' has specific text in it? To happen infinitely, not solely on one column. Database purposes.
To highlight 'A-F' according to the text in 'B' create a formatting rule. To do that, go to Home tab, Styles box, Conditional Formatting > New rule. Use a formula below there and apply the formatting to the range of columns that you wish to highlight
=$B1="ENTER_THE_TEXT_TO_LOOK_FOR"
This point of the topic will provide you with example pictures on how to create and edit the formatting.
My question is: Can Excel automatically insert a column with information from one book into another. This would either push a column over to insert the data or put in the column at the end of the current data. So this would be on going adding a new column up to a total of approximately 20 columns. The other book would always copy over the same column. This may be some high level stuff. Thank you very much in advance for your help.
Kindest Regards,
Todd Haig
Hello, Todd,
the only way for Excel to do that automatically is with a help of VBA code. Try asking for it on MrExcel forum.
This is a very helpful article, Thank you.
I do have one question. I am trying to fill in a cell with text based on the text in another cell. For example if A1=Apple put Applesause in D5, but if A1=Orange then put Orangejuice in D5.
I am not the best with formula's or conditional formatting, and am hoping someone can help me out.
Thanks - Tia
Hello Tia,
place the following formula in D5:
=IF(A1="Apple","Applesause",IF(A1="Orange","Orangejuice",""))
Keep in mind, that if there's neither apple nor orange in A1, D5 will remain empty.
What a great article - Thanks!
I have solved a lot of my requirements from the information I have learned here. However I have one formatting problem that has got me stumped. I have a row of data that contains numbers in some cells and text in others. What I really want to be able to do is automatically highlight a cell if the number in it is larger that the previous number in that row bearing in mind that number might not be in the immediately previous cell. e.g A1 - 1000, B1 - 1000, C1 - 1100, D1 - 1100, E1 - "text", F1 - "text", G1 - 1200, H1 - 1200 In this example I would want C1 to be highlighted as well as G1. C1 because it is larger than B1 (the previous number in row and it is larger than it) and G1 because it is larger than D1, again the previous number in the row.
Hopefully that (a) make sense and (b) someone can put me out of my misery.
Thanks in advance - AJ.
Hi AJ,
I'm afraid it won't be possible, because you will need to construct a complicated array formula, and array formulas can't be used in conditional formatting.
I am trying to format cells in a single column that are greater than a value for a single in the column. To describe better, I have a list of values in the column, all corresponding to people. I need to highlight those that out donated our Leader. So I can compare all the values in the column to a value of a single cell. So all cell values in the range/column will be compared to a single cell. I can't figure out how to have every value compare to the single cell. Below is what I have and I5 is the 'leader' value.
=$I2>I5
This doesn't work because as $I2 moves down the list, I5 turns to I6, etc.
Hello Stuart,
This happens because of relative references. To fix the cell you are comparing to, add dollar signs before the column as well as the row reference, i.e.:
=I2>$I$5
If you still have difficulties, please specify what range of cells you are comparing to I5.
HI! I'M TRYING TO FORMAT A WORK SHEET WHERE EVERY VALUE ENTERED IN COLUMN SAY L, SHOULD PRECEDED BY THE 'TRUE' TEXT IN COLUMN M.
SO IF I ENTER ANY PERCENTAGE VALUE IN COLUMN L, I SHOULD GET THE VALUE TRUE IN THE CORRESPODING CELL IN COLUMN M. i tried data validation and conditional formatting,they both seem not to work
PLEASE HELP,IT'S an urgent request.
Thanks!
Hi Kamo,
You can use the IF function in column M that will check if the cell in column L is blank, e.g.:
=IF($L1<>"","TRUE","")
I hope this helps
Hi, I am hoping that someone could help!
I am trying to colour format rows depending of the information of two columns.
Column E is limited to YES or NO
Column F are years
I want to add conditional formatting so:
= YES and under or equal 30 go green
= YES and over 30 go yellow
= NO and under or equal 30 to blue
I would appreciate any assistance you could offer.
Thanks so much!
Nuria
Hi Nuria,
You need to create a conditional formatting rule for each color using the AND function:
1) =AND($E2="YES",$F2<=30) for green cells
2) =AND($E2="YES",$F2>30) for yellow cells
3) =AND($E2="NO",$F2<=30) for blue cells
Hi Irina,
Is that possible to paste that resulted cell to another excel without any condition in new excel.
For Ex: Pasting the values to another excel.
Hi, I have dataset,as below, how can I write formulae which will output a Y into column B if there is a "Y" present in column A? Is it possible to extend this also for further similar conditions? Thanks
A B
1 XXZ1 N
2 XYX2 Y
3 ZSD3 N
Hi Aaron,
You can use the combination of ISNUMBER and SEARCH functions to look for specific text within your cells:
=IF(ISNUMBER(SEARCH("Y",$A1)),"Y","N")
I'm trying to do something that should be fairly easy, but I'm having trouble making it happen.
Simplified: I have dropdown lists in C3 (Where did we go?). And the distance from home to the place gone in E3. The options for the dropdown list are stored in I3-I33 (Names of locations) with an associated value in J3-J33 (Distance traveled).
What I would like is for E3-E36 to return the values in J3-J33 if the C3-C36 dropdown option is entered.
C3-C36= Dropdown List
E3-E36= Result from J3-J33
I3-I33= Dropdown List options
J3-J33= Distances for each option chosen
So, if I choose "Grand Canyon" from C3s drop down, and it is located in I8, the distance of 2000 miles is listed in J8. I would like that value to show up in E3.
I can make it work for one specific number but not for the entire list.
"=IF(C4=I3:I33,J3:J33)" only returns the correct answer if C4=I4. I need it to return the value in the J3-33 cells.
Thanks for any help,
-Rob
Hi Rob,
The easiest way to go would be using the VLOOKUP function in column E:
=VLOOKUP(C3,I3:J33,2,FALSE)
If will match the value from C3 to a value in column I and return the corresponding record from column J. Please see this tutorial to learn more about VLOOKUP.
What an awesome blog! great stuff here!
I am trying to find a solution to a conditional formatting using a formula issue (I THINK!)...
Please see image at: to see what I am hopeful of achieving.
Thx!
well - I tried to post a url in that...
trying again here without the brackets
sbonham.com/ganba/GymSheetMockup.jpg
Thank you very much for your kind words, Steve, and thank you for the screenshot.
I'm sorry, but there is no easy way to check several words that are in cell B3. One possible solution would be taking the key words into separate cells, selecting the range with comments, and creating a Conditional Formatting rule for each key word with the following type of formula:
=SEARCH($B$3,E6)
Here $B$3 is the cell with the key word and E6 is the first cell of your range with comments.
Hi
I want to format a series of cells 2*4 (represents 1 day)to green if the combined value is 0/blank.
=SUM($B$6:$C$9)=0 goes for the same cells =$B$6:$C$9 so far no problem. But how do i autofill this formua over a sheet with hundreds of cells working 2*4.. Is it even possible or do i nedd macro?
Thanks.
/fred
Hi Fred,
Could you describe your data structure in more detail? Do you want to sum values in range B6:C9 and then B10:C13, or are 2*4 groups arranged in a different way?
Thats correct, downwards would be B10:C13 an to the right D6:E9 and so on.. And if the sum of these 8 combined cells are 0/blank i want them all (ex. B6:C9) to be green, if the value is something else they should become white. All 8 of them. The formula above does this, but it wont autofill over the sheet like i want..
Thank you for the details, Fred,
Please select all your data and use the following formula for the Conditional Formatting rule:
=SUM(INDIRECT( ADDRESS((INT((ROW()-2)/4)*4+2),(INT((COLUMN())/2)*2))&":"&ADDRESS((INT((ROW()-2)/4)*4+2+3),(INT((COLUMN())/2)*2+1)) ))=0
You can read about the INDIRECT function in this blog post.
Another way to go is use OFFSET function:
=sum(OFFSET(INDIRECT(ADDRESS((INT((ROW()-2)/4)*4+2),(INT((COLUMN())/2)*2))), , , 4, 2))=0
Sorry, i don´t get this.. Im about to give up on this.. Thanks anyway!
So I am having a particular issue that this seem to be the closet I am getting. I have a column I am trying to get to fill with x color based on the rest of the row. simple enough I have found ways to do that even here with the search function. however even using conditional formatting it doesn't seem to work if its scanning in-cell drop down.
so there is a header roll the I have names in the first column, second column for color indicator and then column 3:28 is for putting a checkmark or x. if the person knows that task listed at the columns header like forklift certified then john doe gets a ✓ and the status column with be green and a × would be red to indicate he is on restrictions and not perform one or all of his tasks.
any thought that allow for later addition of employees without having to modify the function parameters?
Hi Dear!
you are always there like a teacher for me to learn new formulas and option in excel thank you so much dear.
Hello,
I am trying to create conditional formats that uses icons where I can say if the value is >30%, then green, between 30% and 29% is yellow, and less than 28% is red. Somehow, the yellow one is not yielding results. Also, is it possible to use the conditional format when the data is negative?
Thanks a lot,
Maud
Hello Maud,
Please try to use one of the following formulas for the rule that doesn't work, these examples assume that the numbers are in column A:
=AND($A2>=29%,$A2<=30%)
You can also try this one:
=AND($K2-29%>=0)
You can use negative numbers for conditional formatting as well.
Hi,
I'm looking for the formula for conditionally formatting in situation where we have 2-set of values.
Let's say - If I've 50-locations actual sales Vs their INDIVIDUAL targets (Which are different for each location) and need to conditionally format RED/GREEN for their given achievements (Achieved : Yes = Green / No = Red)
I need a versatile formula to set in entire column - I tried few options and it fails :(
Please help - I appreciate it in advance
Thanks and Regards
Hello,
If I understand your task correctly, you need to create two Conditional Formatting rules:
1) One will highlight the locations Green if the value in the column with their "Actual sales" is equal to or more than the value in their "Individual target":
=$B2>=$C2
1) The other rule will highlight the locations Red if the value in the column with their "Actual sales" is less than the value in their "Individual target":
=$B2<$C2 Please replace column B with the name of the column with actual sales, and column C with the name of the column with individual targets. I hope this helps.
Hello,
Is there a way to make the application of a formula in one cell conditional upon whether or not another cell is blank?
E.g. If cell C1 contains a number, I want cell D1 to return the value of C1/366. If cell C1 is blank, I want cell D1 to be blank.
At the moment, I have got to a stage where if cell C1 contains a number, D1 displays (in text) "=C1/366" and does not process the formula.
Hi Josie,
Sure, you need to use the IF function in D1:
=IF(C1<>"",C1/366,"")
It says if C1 is not empty, divide it by 366, otherwise leave the cell blank.
I hope this helps.
Hi, I need to highlight values on a spread sheet in column F if the value is bigger than the value in column D. How do I write the formula for the whole column without doing it one for one.
Hi Willie,
Just enter the dollar sign before the names of the columns in your formula, e.g.
=$F2>$D2
You also need to make sure "Applies to" range contains all records in column F, so when you click "Manage rules", you will see "=$F$2:$F$350" under "Applies to" if you have 350 rows.
Let me know if you have any other questions.
HI, i need to put in a column:
in green: different texts, aleatory
in red: NO
in yellow: DEF
gray:nothing
i already put red, yellows and gray conditions.
so, for green: edit formating rule/format only cell contain/ not equal to/=AND("NO";"DEF")
doont work, dont bring me anything in green.
help¡?
Hi Zak,
Please include a cell reference into your formula:
=AND($H2<>"NO",$H2<>"",$H2<>"DEF")
I hope this helps.
Hi,
Svetlana,
We need your help we want to use conditional formatting on a sheet 1 column A but references is on another sheet 2, If in Sheet 2 Column B Value is greater than Column A, than Sheet 1 Column A should be highlight reflect as color
Hello,
To compare values in different sheets, you need to include the name of the sheet to the cell reference, so your formula will look this way:
=Sheet2!$B2>Sheet1!$A2
Please also make sure you apply the rule to column A in Sheet 1.
Hi Svetlana, Can I use conditional formatting on one sheet and monitor a cell on another sheet? So I want a cell on one sheet to change colors when the cell on another sheet has specific text or numbers.
Hi Todd,
Sure, you just need to add the name of the sheet to the reference in your formula, e.g. if you want to monitor the value in A2 in Sheet 2, here is how it should look:
=Sheet2!$A$2="Value"
Good afternoon,
Can you please assist me with the following:
If cell B4 contains the two letter code v6, reflect in
cell R4: September 15th.
Hello Gerardo,
You need to enter an IF statement into R4:
=IF($B4="v6","September 15th","")
You can read more about the IF function in this blog post:
https://www.ablebits.com/office-addins-blog/if-function-excel/
If cell J8 value is 5 and cell K8 value is 2 and I want cell G8 to list the value from J8 when K8 is 2...how do I formulate that?
Hello Todd,
If you want to display the value from J8 when K8 is 2, then you need a simple IF statement in G8:
=IF(K8=2,J8,"")
If you want to get the value from J8 if both conditions are true: K8 contains 2 AND J8 contains 5, then you need the following formula in G8:
=IF(AND(J8=5,K8=2),J8,"")
I have an issue with letters:
Column V Column W
01/01/2016 Y
01/02/2016 Y
01/02/2016 N
I need column V to be highlighted red if there is an "N" in column W and if the date has passed today's date
I also need column V to highlight Orange if there is an "N" in column W and the date is within 2 weeks before today's date
Is this possible?
Thank you
Hi Martha,
You need to create 2 conditional formats for column V using formulas.
Please use the following formula for the red color:
=AND(DAYS(TODAY(),$V24)>=0,DAYS(TODAY(),$V24)14,$W24="N")
Hi guys. I am trying to have a different number in one columns depending on how big the amount is in the other columns. so use 0.002635 if other column is >100mill, use 0.002531 if other column is >200mill. The following formula is not working:
=IF(H5>=0,"0.002635",IF(H5>=200000000,"0.002531",IF(H5>=300000000,"0.001879",IF(H5>=400000000,"0.001545","0"))))
Can you please help?
Hi Agata,
Please try this formula:
=IF(H5>=400000000,"0.001545",IF(H5>=300000000,"0.001879",IF(H5>=200000000,"0.002531",IF(H5>=0,"0.002635","0"))))
Hi!
I'm trying and if formula that if there is a date in a cell will return the date, and if not then leaves cell blank.
=if(number(a1),"a1"," ")
The problem I have is this formula works in a few cells and not on most of them.
What could be the reason? and if there is a better formula I could try I'll be happy to give it a go.
Cheers
Hi Daniela,
Try this formula:
=IF(ISNUMBER(A1), A1, "")
Please note, in Excel formulas, cell addresses should not be enclosed in quotation marks, otherwise Excel will treat them as text strings, and return the characters "a1" instead of the value in cell A1.
I am inputting a date in a cell and I would like the date to start off in green, then after 240 days, it automatically turns yellow, then after 320 days, it turns red..how do I do this? Thanks!
It's unusual that you wouldn't compare it to a starting date placed in another cell but as you've spelled it out you would need to place the literal starting date into the Conditional Format formula itself. You need to set up two "Greater than or equal to" conditions. The first will be for the Red condition after 320 days. You would set it up with the formula "="9/20/2016"+320" (yes, you need the quotes around the literal date you need). Make the format background Fill the Red you desire. The second conditional format will be for the Yellow. If you do Yellow first (above the Red condition in the Conditional Format list) once you pass 240 days the cell will forever be Yellow and NEVER turn Red because the first condition to be met sets the format. You can reorder the list using the up and down arrows on the Conditional Formatting Rules Manager to test what I mean. The formula for Yellow would be something like "="9/20/2016"+240" and set the background Fill to Yellow.
Hi,
I would like to know how will I arrange the names in Last Name/First Name/Middle name in another cell.
For example:
Cell A1= Shannon
Cell B1= Smith
Cell C1= McKinley
Cell D1 should appear as: McKinley, Shannon Smith
Thanks in advance!
Mary
Place the formula =CONCATENATE(C1 & ", " & A1 & " " & B1) in cell D1. This will put all the strings in the cells together in the right order with the literal strings inside the quotes for the commas and spaces in between.
Hi Everyone
I need help on something. (Excel 2010)
I need to place into a formula this: If the text in any of my active Cells which are Cell A2 all the way to Cell A4000 is exactly the same as the text that is typed into Cell A1, then the Active Cell that have the same text will shade RED.
I can do this for an individual active cell through conditional formatting no problem using the formula =$A$1=$A$2 but as soon as I do =$A$1=$A$2:$A$4000 it doesn't work.
Please help, what am I missing in this formula?
Hello Simon,
Please use this formula for your conditional formatting rule:
=Countif($A$2:$A$4000,$A$1)>0
You can find more information about COUNTIF here:
https://www.ablebits.com/office-addins-blog/excel-countif-function-examples/#countif-text-exact
I have 02 G:8 and J:8 cell with text G:8="Moderate" and J8= Panding both have drope down list under G8 I have another option "Not Accaptable"
I want to get out put in J8 Pending if the text in G8 is moderate or Not accaptable otherwise completed.
I am using =IF(AND(G8="Moderate"),"Pending","Completed") but it is getting only one text.
Thanks
You only have one conditional test and that's IF G8="Moderate". If it is your Boolean result is TRUE and the output will be "Pending". The FALSE result for anything other than "Moderate" in G8 will return the value "Completed". It seems like you want an IF/OR test. Your syntax would be "=IF(OR(G8="Moderate", G8="Not acceptable"), "Pending", "Completed")". If G8 is either "Moderate" or "Not acceptable", the result will be "Pending", otherwise the resulting output will be "Completed".
i want to highlight red if A20 is greater than A2 but the cells contain dates.. not numbers
Dates are just "serial numbers" started at 1/1/1900 so that date is "1", 1/2/1900 is "2", and so on until today (2/14/2019) is 43,510 (i.e. the 43,510th day since 12/31/1899). To illustrate, type the numeral "1" into a cell and then change the format to "Date" and you'll see what I mean. Likewise, type "Today()" into a cell which is the function to enter the current day's date and then change the format to "Number" and you'll see the opposite happen. Now that we've gone over that...dates are just numbers. So it's still just a numerical comparison so the same logical operators you learned in basic math apply: >, =, <=, (from l-r greater than, less than, greater than or equal to, less than or equal to, not equal to). I'm not sure from your question if you want to turn Red cell A20 or cell A2. Assuming for explanation's sake you want to turn A20 Red if it is greater than A2, select cell A20 and then Conditional Formatting > Highlight Cells Rules > Greater Than. In the dialog box that pops up for the left box select the cell A2 either by overwriting the entry with "A2", clicking back on your spreadsheet and selecting cell "A2", or by selecting the range selector at the right-hand side of the text box and selecting "A2". The right box of the dialog for Red you'll need to select Custom Format in the drop down menu and use the Fill tab to select the Red you so desire. Otherwise, there are some default options, one of which is a light red with red text. If you don't mind the default options for your purposes it can be handier than selecting a custom format every time.
The information here is great, so many questions and answers I am hoping this isn't a duplicate question.
I have a spreadsheet in which if C43, J34 and J46 equal each other highlight the cell in green, if they don't highlight the cell in red.
not sure how to use the conditional formatting on this....thanks in advance for any help you can give me
Format the 3 cells Red to start with because when the conditional format returns FALSE the Red will just be uncovered (i.e. they won't be Green). Then set up your conditional format to be "=AND($C$43=$J$34, $C$43=$J$46)" with a format of Green. Make sure the conditional format applies to "C43, J34, J46" separated by commas.
Please, what is the actual formular to use such that numbers in a table less than 10000 be highlighted and greater than 10000 be highlighted with a different colour
Hi, just use the conditional formatting tool, it has greater and less than all set up and you just input your value and range.
Hi Svetlana,
I was wondering if I could use this function to highlight in yellow a range of cells based on a customer number?
Thanks in advance for your help!
Jim
Hi Jim,
Sure, you can. If you explain the task in more detail, I may be able to suggest an appropriate formula.
Hello,
I'm trying to auto-highlight a spreadsheet. Column A is the Company Name, B is Invoice #, C is Amount, D is Due Date, and E is Date Paid. I'm using the following rules already:
Highlight Red if past due: =NOW()
Highlight Yellow if due within 7 days: =NOW()+7
Leave blank if no value in Due Date: =ISBLANK(A1)=TRUE
I need to know the formula to highlight the entire row GREEN if a date is entered in Column E.
Thanks!
Jenny
Solved it!
I created a new column that "Paid?" and used this formula so when you enter "Yes" in the paid column it highlights the entire row green.
=$E1="YES"
Oh, the formulas that I'm already using are highlight column D only.
Hello,
I am sorry if this has been asked earlier, but i could not find it, neither here nor on google. is there a way to compare cells in column A to their respective cells in column B, and color the cell green if the numbers are the same, and red of they are not? SO, if A1 reads 3, and B1 reads 3, i want B1 to color green. and the same for A2 and B2, A3 and B3, and so on. i could just do it cell by cell, but that would take days, and i cannot find a quicker way to do it. even with a macro, doing it cell by cell would take ages i think.... I am using Excel 2013.
Thanks a lot in advance!
Hi RengerD,
Please try to do the following:
1. Select a range in column B. For example B1:B4.
2. Click Conditional Formatting -> New Rule.
3. Select the "Use a formula to determine which cells to format".
4. In the Formula field type "B1=A1".
5. Click the Format button to set the format you need.
6. Click OK.
Excel 2010 conditional formatting.
I cannot copy my conditional formatting down the rows, I need my row reference to change i.e. A1 to A2, A3 etc. but it retains at absolute reference A1 when I copy it down the rows. When I try to remove the dollar sign in edit rule, it automatically adds the dollar sign back. The rule I am using is:
Format only cells that contain.
Cell value not between:
=$A$1 and =$A$2, (Have test reference number here)
When I copy this down, the rows $A$1 and $A$2 remain absolute where I need them to change to reflect the row numbers I have copied to, removing the dollar sign has no effect as it just defaults back to dollar within the rule.
Thanks in anticipation.
Hi Dave F,
I've tried on my Virtual PC with Excel 2010 and everything works as expected.
To be able to assist you better please describe your task in more detail.
Hi i am new to Excel sheets and having trouble in creating dependent dropdown list for columns
Hi Neeraj,
Please look at the following article, it should help:
https://www.ablebits.com/office-addins-blog/dependent-cascading-dropdown-lists-excel/
Hi,
I'm trying to use conditional formatting to format cells that go over 6 months and 12 months.
All cells have different dates and it's not based on today's date. It's based on a meeting date?
Hi Catherine,
Please try to do the following:
1. Select a range in column A. For example A1:A10.
2. Click Conditional Formatting -> New Rule.
3. Select the "Use a formula to determine which cells to format".
4. In the Formula field type the following formula:
=OR($A1 > DATE(YEAR($B$1), MONTH($B$1)+6, DAY($B$1+$A$1)), $A1 < DATE(YEAR($B$1), MONTH($B$1)-6, DAY($B$1)))
5. Click the Format button to set the format you need.
6. Click OK.
The date values in A1:A10. The meeting date is in B1.
I want my D column to highlight red under conditional formatting if it is more than 14 days after/later than column c.
Can this be done on conditional formatting? and if so what would the answer be?
Thank you.
Hi Lauren Dicker,
Please try to do the following:
1. Select a range in column D. For example D1:D10.
2. Click Conditional Formatting -> New Rule.
3. Select the "Use a formula to determine which cells to format".
4. In the Formula field type the following formula:
=ABS(DAYS(D1,C1))>14
5. Click the Format button to set the format you need.
6. Click OK.
Is it possible to change a cell to 0 (with a strikethrough) if a cell is less than or equal to 0 using conditional formatting? Thanks in advance for any help!
Hi Brad,
Please try to do the following:
1. Select a range with your data.
2. Click Conditional Formatting -> New Rule.
3. Select the "Format only cells that contain".
4. Select the "less than or equal to" option = 0.
5. Click the Format button.
6. Select the Number tab and Custom format.
7. Fill "-;-" value in the Type field.
8. Click OK. Click OK.
sorry if this has been listed but I cannot find it.
I need to fill a cell with RED and Bold Black text stating "PLEASE SPECIFY DETAILS".
But only if another cell has a particular selection made from a validation list.
So B40 is blank. if B38 = "Other (specify)" (seleceted from a validation drop down list) then B40 turns red with black bold text stating "PLEASE SPECIFY DETAILS".
I really would appreciate any help with this. Conditional formatting is not one of my best areas of knowledge
Thank You
Hi Ben,
Please try to do the following:
1. Enter the following formula for cell B40:
= IF($B$38="Other (specify)", "PLEASE SPECIFY DETAILS", "")
2. Select cell B40.
3. Click Conditional Formatting -> New Rule.
4. Select the "Use a formula to determine which cells to format".
5. In the Formula field type the following formula:
=$B$38="Other (specify)"
6. Click the Format button to set the format you need.
7. Click OK.
I would like to format one cell (fill yellow) if another cell contains one of roughly 80 different text values. I've tried formatting cell C2 with =OR($W$2=“I63.00”, $W$2=“I63.011”, $W$2=“I63.012”, $W$2=“I63.01”, $W$2=“I63.02”, $W$2=“I63.031”, $W$2=“I63.032”, $W$2=“I63.039”, $W$2=“I63.09”, $W$2=“I63.10”, $W$2=“I63.111) for example, but it doesn't seem to work. Suggestions?
Thanks!
Hi Damon,
Please check the format for cell $W$2. It should be "Text" if you compare the text values, not "General".
Hi, I have a list of all colleagues in our coy, some have left already, so for those I enter the exit date.
Now what I want is to highlight not only the cell which contains a date value but "the whole row" based on the exit date column.
Could I get some advice please.
Many Thanks
Hi Nel,
To highlight the whole row please add the absolute reference symbol "$" before the cell reference. For example, if you have the dates in column B:
=$B1=DATE(2016,1,1)
Hi, I'm looking to make a formula that highlights the days Friday-Sunday but I'm hitting an issue with my cells. The cells that I want highlighted have a formula in them that takes the date from say C2 and adds 14 days to get the date I need. I'm assuming that's why the weekday formula isn't working for me because there's a formula in the cell and not a date. Any suggestions?
Hi Cynthia,
Do you use the following formula?
=OR(WEEKDAY(A1) = 6, WEEKDAY(A1) = 7)
Hi
I have been trying to do a sum but I am not sure how
I have a 2 rows of numbers
Cell A Cell B Cell C
58.50 56.33€ in cell c What I would like to do is something like this =if cell B is > than Cell A minus Value e.g 0.50
58.50 51.90€
59.70 56.92€
60.30 57.32€
69.00 68.90€
I hope this makes sence
Hi George,
You need a formula like the following:
=IF(B1>A1, value-if-true, value-if-false)
I have a training matrix that has the date of when the subject or test was last taken. Each year is a new refresher
I want those dates to turn green 45 days before the next year, Yellow for 30 days before, and Red for 15 days before and over.
How would I set up the formatting for this. I have been through so many different ways to do the conditional formatting and it just won't work for me.
Hi Heather,
You need to add 3 separate rules using the "Use a formula to determine which cells to format" option with the following formulas:
1. For the red color:
=DAYS(DATE(YEAR(A1), 12, 31),A1) <= 15
2. For the yellow color:
=DAYS(DATE(YEAR(A1), 12, 31),A1) <= 30
3. For the red color:
=DAYS(DATE(YEAR(A1), 12, 31),A1) <= 45
Please add the rules in same order.
Hello,
I have a issue in excel conditional format sheet that
I have 6 cell is for formatting .
I want if continuously cell value is 0 and then last cell value is 1 then cell value should 60
if 5th cell value is 1 then cell value should 50
if 4th cell value is 1 then cell value should 40
if 3rd cell value is 1 then cell value should 30
if 2nd cell value is 1 then cell value should 20
if 1st cell value is 1 then cell value should 10
&
same in 2 new cell but 1 cell value is 10,20,30,40,50 then should only next not previous cell value again in next cell
Thank You in advance.
Hi krishna,
Please show us how your data looks like.
Hello Svelana,
I have a simple issue where I need the color of the values in a cell to change based on the text in another cell. So for instance if cell B2 = "TED" then the value in cell A1 should have a font of say red. I have list of 10 names I would like it to go through and each name should have different font color. So "Jane" would be blue :Chris" yellow etc.
Can I create a formula that could have all possible 10 names and font colors.
Thanks,
Eke
Thanks,
Eke
Hi Eke,
You can create 10 separate rules using the "Use a formula to determine which cells to format" option.
For example, the formula for the red color will look like the following:
=B2="TED"