Conditional Formatting with Dynamic Targets

Question

I want to apply conditional formatting to a spreadsheet. In the spreadsheet, each month has a different budget and actual spent. I want to set up the conditional formatting to look at the above cell and then apply the "good" or "bad" formatting.

How can I apply conditional formatting that will simply look at whether the a given cell is >/< the other and then format in green or red without having to create a new rule for each month as the target changes.

Currently, my attempts have only yielded the actual from any given month compared against January's budget.

For Example: January is on budget, however February's actual will also indicate it is over budget because it is comparing February's actual to January's Budget.

This example is what I "fighting":

| | January | February | March | | Budget | $100 | $120 | $90 | | --- | --- | --- | --- | | Actual | $99 | $105 | $91 |

This is what I am attempting to achieve:

| | January | February | March | | Budget | $100 | $120 | $90 | | --- | --- | --- | --- | | Actual | $99 | $105 | $91 |

Answer 1

Let's say that Actual is B3, C3, D3 etc.

Select these values. I will assume that B3 is the active cell in the selection.

Set the font color to green. This will be the default.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'greater than' from the second drop down.

Enter the formula =B2 in the box next to it. This refers to the cell above the active cell.

Please note that the formula does not contain $ characters.

Click Format...

Activate the Font tab.

Select red as font color.

Click OK, then click OK again.