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?

excel conditional formatting visual stop lights

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".

excel excel conditional formatting visual stop lights

b) Select "Icon Sets" in the Format Style field.

excel excel excel conditional formatting visual stop lights

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.

excel excel excel excel conditional formatting visual stop lights

d) Click OK. The data range with "Actual - Target" performance data will appear as below.

excel excel excel excel excel conditional formatting visual stop lights

 

You can find similar Excel Questions and Answer hereunder

1) How to read a value from a cell in vba in Excel

2) I have a WorkBook that loads a form automatically when it is opened. In Excel, how can I suppress the form from loading on file open when required?

3) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

4) Vlookup to return max value from multiple hits in Excel

5) How do I know which cells on the worksheet contain Conditional Formatting?

6) How can I prevent users from entering duplicate values in a range?

7) How do I find the median salary of employees with the same skillset in my Organization?

8) Given a raw data Table, how can I find the value of a field for a specific value of another field?

9) Is there a way to get a log value of a number using Excel?

10) How can I add and link a Chart Title to a cell value?

11) How can I filter a table to get all records that have less than a particular value in a specific column?

12) How can I find the slab in which a given value fits?

13) Conditional formatting with if statement in Excel

14) How can I ensure that user enters only certain acceptable values in an input cell?

15) Is there a way I can easily identify the duplicate values in a dataset?