What if you would like to compare two columns of data and use conditional formatting to give users a visual representation of the comparison? For example, if column A is greater than column B, a particular icon would appear. If column A is less than column B, a second icon would appear; or if columns A and B are equal, a third icon would be displayed.
This conditional formatting can be accomplished by combining an icon set with an IF statement. As you can see, the worksheet includes data for two different weeks. We will compare the two weeks to see if there is a negative or positive change, or no change at all.
We will be using the IF function to compare our columns. The logic behind the IF function is this: if a condition is met, then do one thing, if the condition is not met, then do another. IF functions can be “nested,” meaning that instead of returning a result if the condition is not met, Excel will compare the data to a second condition. Excel allows up to 64 nested IF functions.
The syntax for a nested IF function is this:
=IF(condition1, value_if_true, IF(condition2, value_if_true, value_if_false))
We will construct our IF function to return number values. The values will then be used to apply conditional formatting. We can’t put a function in a cell that contains data without overwriting the data. Therefore, we will need to work in a third column.
Place your cursor in cell C2. In cell C2, type the nested IF function: =IF(A2>B2, 0, IF(A2<B2, 2,1)). Press Enter.
The result, “0″, should appear in cell C2.
Copy the IF function to the rest of the cells in column C:
To apply conditional formatting to the values in column C, if necessary, select the values in column C. On the Home tab, in the Styles Group, click the Conditional Formatting button. From the drop-down menu, click Icon Sets, then click More Rules….
The New Formatting Rule dialog box appears. The default setting is to compare percentages with the 3 Traffic Lights (unrimmed) icons. We want to use the 3 Arrows (Colored) icons to compare numbers. In the bottom half of the dialog box, we see the options for Value and Type of comparison.
Before making other changes, we need to choose our Icon Style. From the drop-down list, choose an icon set. We chose 3 Arrows (Colored). To change the values, in the first line (the green arrow), change the Type to Number and the Value to 2. In the second line (the yellow arrow), change the Type to Number and the Value to 1.
Before closing the dialog box, we want to click the check box to Show Icon Only. This will remove the values (0, 1, 2) and show only the icons. Since the values are there simply to facilitate the conditional formatting, there is no reason for users to see them. There is also an option to change the icon style. The dialog box should look like this:
To close the dialog box and apply the conditional formatting, click OK.
The conditional formatting icons have been applied to column C:
The red icons represent the negative changes (Week 2 is less than Week 1), green icons show the positive changes (Week 2 is more than Week 1), and yellow icons show the times when there was no change between the weeks. If we would prefer that green represents the negative changes and red the positive, we could return to the dialog box and click the Reverse Icon Order checkbox.