I have two columns of percentages, where the first column is a current value and the second column is a threshold. I want to be able to format the threshold differently based on the current value as follows:
- Green background: threshold <= current value
- Yellow background: threshold <= current value + 10
- Red background: threshold > current value + 10
A supplementary question is how, once I have a formula, I apply to all cells in the threshold column so that relative formulas are applied.
Even the simplified rules I've applied don't seem to have worked, and haven't been able to apply rules to whole columns without manually editing the row references for each.
E.g.
Red = B$1$ > A$1$ + 10
Yellow = B$1$ < A$1$ + 10
Green = B$1$ < A$1$1 Answer
To do that, select the values in the A column. Then go to Home -> Styles -> Conditional Formatting -> New Rules. Choose the 'Use a formula to determine which cells to format'. Type in the formulas you just wrote and format the cells accordingly. To trick point here is that when selecting the cells, Excel automatically adds the $ symbol to make it an absolute reference. Just remove the $ symbol before the line numbers and when you click ok it should work.
The formula in the conditional formatting should be
=$B1 > $A1 + 10
=$B1 < $A1 + 10
=$B1 < $A1You can even conditional format just the first cell and then copy it and Paste Special -> Formats over the other values in the A Column.
1
