Highlighting Differences Between 2 Columns

March 24, 2011 at 12:26:51
Specs: Windows XP
I have an Excel 2007 spreadsheet in which a question is contained in cell A1. In columns B1 and C1, there are different responses to the question (Yes, No, or NA). I have different questions that continue down column A and different corresponding answers continuing down columns B and C. For example, the question in A1 could be "Do you like pizza?" and the answers in B1 and C1 could be any variation of Yes's, No's, or NA's. What I am trying to do is get Excel to highlight those cells in columns B and C where the responses differ. I have tried using conditional formatting, but I can't get CF to apply to a RANGE of cells. It will only highlight if a given cell differs from another single cell. I am able to use CF to highlight differences on a cell-by-cell basis, but that doesn't help me out much. Does anyone know if I can apply some sort of conditional formatting to a RANGE of responses to highlight those that are different? I appreciate it!

See More: Highlighting Differences Between 2 Columns

Report •

March 24, 2011 at 13:56:13
Why don't you just compare the two with a formula in column D

In cell D1:


If the contents of cell C1 equals the contents of cell B1 we put the number 1
If they don't match we put a zero.



Report •

March 24, 2011 at 14:01:53
Actually you can use the same formula to do Conditional Formatting:

1) Select range of cells: B1:C100 <both columns B & C

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF($B1=$C1,TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

Note where the dollar signs are in the formula, they need to be there.

This should light up the two cell with your color if the match.



Report •

Related Solutions

Ask Question