Icons in Excel Cells

You can format your cells so that they have icons in them. The icons are used to illustrate the values. For example, here's a task list in Excel without icons. The 1 indicates that a task was complete, while the 0 indicates it is not complete:

Task list with no icons

Now here's the same task list with icons:

Task list with icons

The second task list is far more useful! Let's see how it's done.

Create the same simple spreadsheet as the one above. Now highlight the numbers from cell B2 to C5. With the cells highlighted, locate Conditional Formatting on the Home ribbon at the top of Excel. From the Conditional Formatting menu, select Icons Sets:

Conditional Formatting menu showing the Icon Sets item

The Icons Sets are grouped into four categories: Directional, Shapes, Indicators, and Ratings. Click on the circles under Indicators. Your B column will then look like ours from the image at the top of the page.

If you hold your mouse over each icon set, you'll see that it says something like "3 indicators", "4 Traffic Lights", "5 Ratings". Some of these work better than others. For example, suppose the values in your column went from 1 to 3, and you wanted stars for your ratings system. You'd think you'd get 1 star for each rating. However, you don't. You get one star in total, and the star is then shaded depending on the value in the cell. Here's an image of what such a 3-star rating system would look like:

Icon sets for a ratings system in Excel

A bit disappointing, to say the least!

Going back to our task list, though, we could have values like 0, 1 and 2. The 1 would indicate that a task is only partially complete. The list would then look like this:

Icons in Excel cells

There is now an exclamation point to the left of the 1 value. We've given a completed task a value of 2, and it has a white tick in a green circle. A task that has not even been started has an X next to it.

Icons in cells can be quite useful, but they are a bit hit and miss.

 

In the next lesson, we'll move on from Conditional Formatting and take a look at the CountIf function.