Excel Conditional Icons

table_exampleDeciphering a spreadsheet is easier with Excel conditional icons, which visually communicate changes and differences in numbers with directional arrows, stop light colors, flags, and bars. In the example below, a simple sales report has been dressed up with Excel conditional icons.

Inserting Excel conditional icons

Select the cells in which you want to place your Excel conditional icons. Then, click on Conditional Formatting under the Home tab and choose Icons Sets. I selected the check mark indicators, but any set will do.

We now need to edit the rules for the icons. Click again on Conditional Formatting, then Icon Sets and More Rules.

This pop up window describes the rules for placing icons in the cells. You’ll need to set the values that will determine if the icon is green, yellow, or red. Excel can base this decision on exact numbers if you type numbers in the Value column or by a formula if you use the cell selector icon to choose a cell.

formatting_rules

In this example, I’m comparing each month’s sales against quota in cell e11, so I used the cell selector icon to choose that cell. If the value is greater than quota, the cell gets a check mark. If it is less then quota, it gets an X.

By default, Excel offers three icons. I only want to use 2, so I changed the Icon for the final value to No Cell Icon. So if the sales figure is greater than or equal to 0, but less than quota, the cell will have an X excel conditional icon. If the sales figure is greater than quota, the cell will have a check mark.

For more excel techniques see our Excel Training Class.