Simple Excel conditional RAG icons comparing yearly fluctuations

The conditional icon set in Excel is a great feature for visualizing data values. A simple usage will be showing up / down arrows in red / amber / green color. In its default Excel will do the calculation across the cells to determine which icon to use. This is nice but for some other usage it might be necessary to have some customization and it can be achieved with the respective Conditional Formatting dialog. For some usage, however, the restrictions imposed in this dialog like relative reference prohibited the presentation of certain commonly used intuitions.

For example, the data below depict sales value country-wise. Using automatic icon set in defaults, Excel will not present the desired icons in the way of showing sales moving up or down for each country, but instead it will show the icons by the yearly value.

excelautoicon1

A simple solution is to add a calculation column using the if() function to return either -1, 0, or 1 corresponding to the sales value comparison by country. The auto formatting icon can then be applied to this column. With a final touch of column width setting, the result is nicely presented.

excelautoicon2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s