strings in conditional formatting excel 2011 mac

#Strings in conditional formatting excel 2011 mac how to

In my spreadsheet, I use a formula to calculate # of days overdue, but only if there is a value in, where A4 and C4 are dates (MM/DD/YYYY). I am also using a 3-color scale but it doesn't work across the entire row, only for some cells that contain dates or numbers. Thank you, it is very kind of you to dedicate your time to this website

#Strings in conditional formatting excel 2011 mac how to

If the student has a C in G4 I want it to turn yellow, if the student has a D, E, F or G I want it to turn red.Īlthough, I am not very proficient in Excel I am quite good with ICT and should be able to navigate around Excel once I know how to do this If Column B4 says B and in column G4 the student has got a *, A or B I want the cell to turn green. If the student has got a B in G4 I want it to turn yellow, if the student has a C, D, E, F or G I want it to turn red.

The data in column B is different for each student, so if Column B4 says A and in column G4 the student has got a * or an A I want the cell to turn green. So I was wanting Excel to look at the data in column G and compare it with what the students should have got (e.g. Column B is students' required grades, column G is their actual grades. When I try this I receive which says 'You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets.Īlso, I don't think I was clear in my initial message.

There are other approached, but given your experience in Excel, this seems to be the easiest method for you. Repeat above steps, except use the formulas for conditions 2 and 3Ĭondition-3 formula: =OR(G4='D',G4='E',G4='F',G4='G') Click the Format button and set the fill color as desired for the condition, then click OK Menu > Home tab > click on Conditional Formatting > select New Ruleįormula for first condition: =OR(G4='*',G4='A',G4='B')Ĥ. With G4 (or the range of cells) selected:ġ. You can apply the conditional formatting to an entire range of cells at once, like G4:G100, by first selecting them before you apply the CF. You will need three conditional formats using the formula option. I don't get the connection between B4 and G4, and all your references/criteria point to G4, so I will focus only on that cell. Since you only have three conditions, this works for Excel 2003 and earlier as well. You don't say which version of Excel you are using, so I will assume Excel 2007 and there are no conditional formats currently applied (if so, delete them) Re: Conditional formatting - using a formula in 3 color scale