I want to use a 3 colour dashboard which reference off another cell. So I can quickly ID if I am on or over budget.

i.e. Ref cell is 100 and the formatted cell = 80 then I want the cell to go Green. If the formatted cell = 100 I want the cell to go Yellow. If the formatted cell = 120 I want the cell to go Red. Basically if the formatted cell if 90% less than ref cell (green), 100% (yellow) and 110% (red). I want to apply this to many cells with different numbers.

Hi, Condtional formatting will do this for you.

If cell F2 contains the current spend and cell G2 contains the target spend or maximum:

Select cell F2

From the Menu select Format - Conditional Formatting ...

In the conditional formatting dialog box that opens, in the Condition 1 drop-down, select 'Formula Is'

In the box to its right enter this formula:=IF(F2>G2,TRUE,FALSE)

Click Format and in the Patterns Tab select a red color, click OK.

Back in the Conditional formatting dialog box click Add.

Under Condition 2 select 'Formula Is' and enter this formula:=IF(F2>=G2*0.9,TRUE,FALSE)

Select a yellow color and add a third Condition with this formula:=IF(AND(ISNUMBER(F2),F2>0),TRUE,FALSE)

and format the pattern Green,

Click OK

F2 will have no background color if the cell is empty or contains zero.

It will be green if less than 90% of the value in G2

Then yellow for 90% to 100% of G2, and if greater it will be red.If the relative positions of the Value (F2) and the Maximum (G2) are the same for other cells, you can copy and paste or drag to extend the formula and the conditional formatting will follow.

Note: In the formulas TRUE and FALSE do not have double quotes around them, they are Excel's logical states true and false.

Use Ctrl+V to paste the formulas into the formula boxes in the conditional formatting dialog boxes (there is no right-click menu - paste available). Thanks to DerbyDad03 for this tip.

Regards

That is fantastic, I have been trying for months to get this!!!

Great to hear that it worked. Regards

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History