Excel Icon Sets – Simple Example

They say that picture is worth a thousand words and that is even more accurate when dealing with numbers.

Sometimes in Excel you want to show Icons that provide quick meaningful indication for the value of the rows. A simple way to do that is by using Excel’s icon sets conditional formatting.

For a simple example, let’s assume that we have a table with product pipeline week-over-week and we want to provide visual indication if the pipeline is growing or declining using one of the following icons:

arrows

Let’s assume our pipeline table looks something like this:

pipeline

And we want to add an icon beside the “Delta Prev Week” column with the icon.

To do that (on a Excel:Mac 2011) we will use conditional formatting:

1. In the menu select “Format” and then “Conditional Formatting…”. You should see the following popup:

conditional format

2. Click on the Plus sign and select Style as “Icon Sets”.

3. Microsoft provides multiple predefined sets of icons so select the Icon Set that works best for your scenario. In this case we want use the arrows.

4. Setup the value ranges. In this case, everything above zero is green, zero is yellow and everything below zero is red. The result should be something like this:

format rule

5. Finally select the range for activating the conditional formatting by typing directly in the “Applies to” column. In this case I selected the entire column D:

format rule scope

6. The result is an icon assigned to column D with the needed icon:

pipeline with arrows

There are also options to show only the icons and change the order of the icons.

See more information at Microsoft Office Talk here (It was written for Office 2007 for Windows but is still relevant).

Note that unfortunately at least in this release Microsoft does not provide an elegant way to load your own preferred icons. This is unfortunate and requires a weird workaround.

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

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