How to Use Comparison Icon Sets in Excel & Google Sheets

This tutorial will demonstrate how to use conditional formatting comparison icon sets to compare data visually in Excel and Google Sheets.

 

IconSets start

 

Conditional Formatting Icon Sets

Comparison icon sets are useful for seeing trends in prices, stock markets, shares etc. Icon sets are a type of conditional formatting that allows you to compare cell values with other cells and show data trends.

Consider the following worksheet.

The data for the sales of mobile phones for January and February is in the worksheet. The trend is to see if the February sales are greater than the January sales. Column E is 0 if the sales have decreased, 1 if they have stayed the same, and 2 if they have increased.

 

IconSets nested if formula

 

Directional Icon Set

  1. To apply comparison icon set formatting, start by highlighting the cells to be compared (E5:E8). In the Ribbon, select Home > Styles > Conditional Formatting > Icon Sets.

 

IconSets ribbon icons

 

  1. There are a variety of different icon sets to select from. For this example, select Directional to show the trend.

 

IconSets ribbon directional

 

The conditional formatting rule is applied to the selected cells.

 

IconSets formatting

 

  1. To remove the 0 and 1 from the cells, edit the applied conditional formatting rule.
    In the Ribbon, select Home > Styles > Conditional Formatting > Manage Rules.

 

IconSets manage rules

 

  1. Select the Icon Set rule, and then click Edit Rule.

 

IconSets manage rule

 

  1. Check the box next to “Show Icon Only“. You can also customize the icon set that is used for the rule – to see how to do this, click here.
  2. Click OK twice to return to Excel.

 

IconSets show icon only

 

  1. Then center the icons in the cells.

 

IconSets alignment

 

Shapes Icon Set

  1. You can also use the Shapes Icon sets to apply “traffic light” icons to the data.

 

IconSets traffic lights

 

The color of the indicators is based on the values that are stored in the icon set rule.

  1. To amend these values, in the Ribbon, select Home > Styles > Conditional Formatting > Manage Rules.

 

IconSets edit traffic rule

 

  1. Select the required rule, then select Edit rule.

 

IconSets manage traffic rule

 

  1. Edit the values if required, and then click OK twice to return to Excel.

See also…

Comparison Icon Sets in Google Sheets

Google Sheets does not have icon sets in conditional formatting. You can, however, apply color codes to your data.

  1. Select the data you wish to color code. In the Menu, select Format > Conditional Formatting.

 

IconSets gs conditional

 

  1. Select Color scale and then select the scale you want to use. Finally click Done.

 

IconSets gs color scale

The conditional formatting rule is applied to the data.

 

IconSets gs formatted