Skip to Main Content

go to Indiana University Indiana University


Comparing Columns Using Conditional Formatting Icon Sets

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.

CondFormApplied

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:CopiedValues

 

 

 

 

 

 

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:

 EditRuleDescription2

To close the dialog box and apply the conditional formatting, click OK.

The conditional formatting icons have been applied to column C: CondFormApplied

 

 

 

 

 

 

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.


6 Responses to "Comparing Columns Using Conditional Formatting Icon Sets" to “Comparing Columns Using Conditional Formatting Icon Sets”

  1. John Weber Says:

    I realize you might have put the nested if in for educational purposes, but sign(a2-b2) + 1 is much cleaner.

  2. April Law Says:

    Thanks for pointing this out. There are often several different ways to accomplish the same task.

  3. Prashant Says:

    Hi,
    I tried (a2-b2)+1, it doesnt work for….i guess i m missing something, can you pls explain that further.

  4. Rag Says:

    I need the exact values of A-B just beside the indication mark, how do we do it

  5. Dabby Says:

    Actually, in order to get the correct 0,1,2 as described in the post without using the IF statement you could use:

    =sign(b2-a2)+1

    This is the reverse order of what John Weber mentioned – he just had the column order backwards.

  6. Wishlist Says:

    Great hint April :-) Thanks.

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.