Since Excel 2007 was introduced, there is a small, but powerful tool available in Excel that is in my estimation considerably underused. As we all know, Conditional Formatting can help focus the attention of the report user, but after all these years, merely coloring the cell background or font can be rather drab. That is why I embrace the comparatively new Conditional Formatting Icons.
For instance, in the table below, a Red Light Icon appears in the Alert column whenever the monthly sales were less than 20,000 (assuming that is the sales goal).
Here is How to Set this Up:
- Select the range of cells in which you want a Semaphore to appear the (Alert column in the example)
- Insert a simple IF formula that returns a “1” or a “3” (i.e. IF(C4<20 1="" 3="" b="">) Note: By default, a “1” will be a Red Light and “3” will be a Green Light 20>
- Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
- Go back to Conditional Formatting and choose Manage Rules
- Click Edit Rule and put a check in the Show Icon Only box and Apply