Excel conditional formatting Icon Sets, Data Bars and Color Scales

The article provides the detailed guidance on how use conditional formatting Icon Sets, Data Bars and Color Scales in Excel 2016, 2013, 2010 and 2007. It will teach you how to extend these conditional formats beyond their common uses and apply icons based on another cell's value.

Last week we started to explorer various features and capabilities of Conditional formatting in Excel 2016, 2013 and 2010.  If you have not got a change to read that article, you may want to do this know. If you already know the basics, let's move on and see what format options you have with regard to Excel's icon sets, color scales and data bars and how you can leverage them for your projects.

When you click the Conditional Formatting button, a drop-down menu offers you a handful of pre-defined formatting rules. Let's quickly go through them, one at a time.

• Icon sets
• Data Bars
• Color Scales

Excel conditional formatting Icon Sets

Excel conditional formatting icon sets will help you visually represent your data with arrows, shapes, check marks, flags, rating starts and other objects.

You apply the icon sets to your data by clicking Conditional Formatting > Icon Sets, and the icons appear inside selected cells straight away.

In the screenshot above, you can see a table listing the household spendings with a green circle representing the highest value and red circles lower values. I cannot say that Excel has interpreted the data in the way I wanted, so let's customize the icon set a bit. To do this, click More Rules... underneath the icon sets list to bring up the New Formatting Rule dialog.

From here you are able to choose other icons and assign them to different values. I have decided to stick with the same icons but assign them in a different way:

How to apply an Excel icon set based on other cells' values

A common opinion is that Excel conditional formatting icon sets can only be used to format cells based on their own values. But this is a delusion. With just a little creativity, you can assign icons depending on the values of other cells in a row or based on another cell's value, as demonstrated in the following examples.

Example 1. Apply an icon set based on other cells in a row (blanks vs. non-blanks)

Suppose, you have a list of products that includes units in stock, delivery and other details. What you want is to add different icons at the beginning of each row depending on whether other cells in the same row are blank or non-blank. That is, you will add a check mark in Column A if all cells in a row are filled in with data, an exclamation mark if some cells are blank, and a cross icon if all cells in a row are blank. To use an Excel icon set in this way, perform the following steps:

1. Add an empty column in front of your table (Column A).
2. Copy the following formula across Column A: =COUNTBLANK(B2:F2), where F is the last column in your table. This formula will count the number of blank cells in each row.
3. Apply the conditional formatting icon set rule to Column A by clicking More Rules…, as explained above.
4. In the New Formatting Rule dialog, do the following:
   • Click the Reverse Icon Order button to change the order of icons.
   • Select the Icon Set Only checkbox.
   • For the cross icon, set >=5 (where 5 is the number of columns in your table, excluding the first "Icon" column).
   • For the exclamation mark icon, set >=1.
   • Set Type to "Number" for both icons.

   You can verify the settings in the screenshot below:
   

And here is the result:

Example 2. Add icons based on another cell's value

Suppose, you have a list of orders and you need to add a green flag to column A when a certain product is delivered, i.e. there is any value in the corresponding cell in column D (Delivery Date). If there is no value in column D, a red flag should be placed in column A.

In fact, this example is very similar to the previous one, with the difference that you apply a 2-icon set based on the value in another column.

1. And again, you start by adding a formula to column A. This time we will use the IF function: =IF($D2<>"",3,1). The formula says to put 3 in column A if there is any value in the corresponding cell in column D, otherwise put 1. Naturally, you will need to replace D with the column you base your formatting on.
   
2. Select all cells in column A, except for the column header, and create a conditional formatting icon set rule by clicking Conditional Formatting > Icon sets > More Rules...
3. In the New Formatting Rule dialog, select the following options:
   • Click the Reverse Icon Order button to change the icons' order.
   • Select the Icon Set Only checkbox.
   • For the green flag, set >=3.
   • For the yellow flag, set >2. As you remember, we do not really want a yellow flag anywhere, so you set a condition that will never be met, i.e. a value greater than 2 and less than 3.
   • Set Type to "Number" for both icons.

   

And here is the result of your effort - the green and red flag icons are added to column A based on the value in column D.

Excel conditional formatting Data Bars

You use conditional formatting Data Bars to represent data graphically inside a cell. The longest bar represents the highest value and, as you can easily guess, shorter bars represent smaller values. Data bars can help you spot large and small numbers in your spreadsheets, for example top-selling and bottom-selling products in your sales reports.

You apply data bars in Excel  in the already familiar way - simply select the cells, click Conditional Formatting > Data Bars and choose the bar type you want. Once you do this, the colored bars will be placed inside the selected cells.

As you can see in the screenshot above, Excel conditional formatting data bars work very well to visually show your household spendings.

If you want to apply your own data bar style, click More Rules... as usual to bring up the Edit Formatting Rule window, where you choose the desired options:

• Place a check in the Show Bar Only checkbox to hide the cells' values and display the colored bars only.
• To select the Minimum and Maximum data types, click the little black arrow next to the corresponding box. While the Automatic type works fine in most cases, you can also choose some other data type such as percent, number, formula, etc.
• Experiment with Fill color, Border and Bar direction and click OK when you are happy with the Data Bar Preview.

Excel conditional formatting Color Scales

Using conditional formatting Color Scales, you can format your cells with two or three color gradients, where different color shades represent different cell values.

You can start by clicking Conditional formatting > Color Scales and hovering over the thumbnails of ready-to-use color scales Excel has already created for you. The 2-color green-white color scale seems to have worked pretty well too for my household budget:

But if you really want to impress someone and have the time and desire to experiment, try out the More Rules... option again and play with the colors a bit. First off, you choose either a 2-Color or 3-Color scale, then select your own colors and assign them to the minimum, maximum, and midpoint values.

This is how you use Excel's icon sets, color scales and data bars. Very intuitive and user-friendly, aren't they? If you are curious to learn other conditional formatting rule types, the following tutorials may come in handy:

• How to change the row color based on a cell's value
• How to apply banded rows and columns in Excel 
• How to change a cell's background color based on its value
• Excel conditional formatting for dates
• How to count and sum cells by color in Excel 2010 and 2013
• How to automatically highlight duplicates in Excel
• Conditional formatting in Excel PivotTables

In the next article we are going to investigate arcane twists of Excel conditional formatting formulas, please stay tuned!