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.
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:
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.
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:
=COUNTBLANK(B2:F2)
, where F is the last column in your table. This formula will count the number of blank cells in each row.You can verify the settings in the screenshot below:
And here is the result:
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.
=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.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.
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:
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:
In the next article we are going to investigate arcane twists of Excel conditional formatting formulas, please stay tuned!
138 responses to "Excel conditional formatting Icon Sets, Data Bars and Color Scales"
hi, first of, thank you for the tutor!
how do i do the icon condition if i want to set cell againts cell againts cell?
eg: say is the gross profit for the month
c1: 20,000 (jan20)
e1: 15,000 (dec19)
g1: 22,000 (nov19)
what i would like to create is there is the arrow showing up or down againts the previous month. not againts a "base number"
Hi Experts,
What values to type in dialogue box for conditioning formating arrow icon sets like positive percentage with green colour arrow, zero percentage with yellow arrow and negative percentage with red arrowðŸ˜