conditional formating visual stop lights in Excel
Q43. Is there a way to create an visually impactful report with stop lights indicating automatically if Target is met?
For example, if Target is met, there should be a Green light, if actual is closer to Target but not met, there should be a Yellow light and finally a Red light in case Actual is way off from Target.
In the dataset below, instead of looking at the Actual vs Target figures for each salesperson and then finding out if Target has been met, can there be lights automatically indicating performance of each salesperson?
To do it in Excel, here is the answer:
a) Select the data range with "Actual - Target" performance data. Under "Home" tab, click on "Conditional Formatting" -> "New Rule".
b) Select "Icon Sets" in the Format Style field.
c) Enter the criteria for highlighting by selecting Type and value fields. In the example shown below, any value above 0 in the selected range will have Green light.
Similarly, any value between 0 and -500 will have yelow light and anything below -500 will have red light.
d) Click OK. The data range with "Actual - Target" performance data will appear as below.