Conditional Formatting Dashboard and Percent

Microsoft Excel 2003 (full product)
February 2, 2010 at 22:58:51
Specs: Windows XP
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.

See More: Conditional Formatting Dashboard and Percent

February 3, 2010 at 04:48:02

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:


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:

Select a yellow color and add a third Condition with this formula:

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.


Report •

February 3, 2010 at 18:16:14
That is fantastic, I have been trying for months to get this!!!

Report •

February 4, 2010 at 04:37:43
Great to hear that it worked.


Report •
Related Solutions

Ask Question