The Red Light

Nothing grabs your attention like a Red Light.  (At least, we hope that’s the case when you’re driving.)  Cold, hard data in Excel can be, well, Cold.  Numbers can be rather numbing and even the best analyst may have moments when she or he has lapses in attention.

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:

1. Select the range of cells in which you want a Semaphore to appear the (Alert column in the example)
2. 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  
3. Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
4. Go back to Conditional Formatting and choose Manage Rules
5. Click Edit Rule and put a check in the Show Icon Only box and Apply

 Your Alert field will instantly take on the engaging Stop/Go formatting that will be sure to catch the attention of any of the report users.  Very Cool!