השאלון למובייל

Comparing columns in excel

I have been teaching advanced excel for many years but have never released this tidbit lets say you have to columns with almost the same data except for a few places how to compare . immediate response: next column ==> =IF(A2=A3,"","NO") but what if you want several columns to represent different test results?

Advanced conditional formatting to the rescue

The biggest issue students have with conditional formatting is its inquiry of the cell it is located on, so you cannot color a cell according to another's value.
INDIRECT to the rescue, INDIRECT is a function that allows you to write the address of the cell you wish to query and it will deliver the cells value.
So using concatenation and a few other text tricks:
CODE("A") will give me 65 which is the ascii number of A
CHAR(65) will return the char representation of the number 65 which is A
So CHAR(CODE("A")-1+2) will return B
COLUMN() returns the number of the column
ROW() returns the row number
If I am on row 3 column D (4)
Then CHAR(CODE("A")-1+COLUMN()-1)&ROW() will return 'D3'
INDIRECT('D3') will return the value of D3
So the following statement
=INDIRECT(CHAR(CODE("A")-1+COLUMN()-1)&amp;ROW()) <> INDIRECT(CHAR(CODE("A")-1+COLUMN())&amp;ROW())
Is actually    C3 <> D3
which means return TRUE when the statement is FALSE since the conditional format requires a true/false response, the statement returns True when the format needs to change.