Indicator Dots are used to highlight a value in a spreadsheet cell that is over (or under) a specified value.
This can be used to make it easier for the reader to identify values of interest quickly and clearly.
Using standard icon sets
The simplest option is to apply a standard set of icons in the same cell as the numbers.
To do this:
Select the range of cells you want to add indicator dots to.
Then, from the Home tab on the Ribbon, select Conditional Formatting, Icon Sets and choose the indicator type you wish to use.
A couple of things to remember if you take this route.
First, many of the icon sets include red and green icons. This may cause a problem for readers who suffer from colour blindness.
Second, the default ranges for the three icon sets are: Lowest values (red) <33 percent; Mid-range values (amber) >=33 and <67 percent; Highest values (green) >=67 percent.
So, they can be good if you simply want to show which values sit in all three equally spaced ranges.
Modifying the basic icon sets
However, including the dots in the same cell as the numbers, or including all three (or four) icons in a set, can sometimes lead to a cluttered display. This in turn can reduce the clarity of the display, negating the reason for including the dots in the first place.
So, instead of applying the icon sets (indicator dots) in same cell as the numbers, I often like to use a simple formula to create a separate column for the dots.
Then you can select this new column of figures and choose More Rules …, from Conditional Formatting, Icon Sets on the Ribbon Home tab.
When the More Rules dialog box appears, you can: Tick the Show Icon Only option; turn off one or two of the icons; and modify the range value where each of the icon(s) appear.
For example, ticking Show Icon Only, turning off the amber and red icons, and setting the green icon range to >=1000 (number value rather than percent),
So here, we have a simple indicator that flags if a value is 1000 or higher.
Simple, clear and easy to interpret – assuming the value of 1000 hold some significance of course.
Try conditional formatting icon sets to create indicator dots the next time you want to highlight a given range of values in your data.