How to Compare Columns in an Excel Spreadsheet

Spreadsheet functions and formulas can quickly validate columns of data

Columns can be compared in an Excel spreadsheet by using functions inside formulas in a third column to output the comparison result. Useful functions for comparisons are "EXACT," "MATCH," "FIND" and "IF."

Comparing Values in Two Columns

If Column B and Column C contain monthly figures for different years, the year-on-year differences can be outputted in Column D by entering the formula "=C1'B1" in D1, then holding the SHIFT key while pressing the DOWN arrow to select the cells underneath, and then pressing "CTRL+D" to copy downward.

ColumnA ColumnB ColumnC ColumnD

Jan $100 $150 $50

Feb $200 $210 $10

Mar $300 $270 $30

Error-Checking Two Columns

If the data in Column A and Column B are supposed to be the identical, discrepancies can be found in Column C by entering the formula "=EXACT(A1,B1)" in C1, and then copying downward to produce the result TRUE or FALSE for each row.

ColumnA ColumnB ColumnC

223 223 TRUE

133 134 FALSE

240 240 TRUE

Data Validation Using FIND

Each cell in Column A can be validated against data listed in its adjacent cell in Column B by entering the formula "=IF(ISNUMBER(FIND(A1,B1)),"VALID","INVALID")" in Cell C1, and then copying downward.

ColumnA ColumnB ColumnC

JS JS,FJ,MC VALID

FJ JS,FJ,MC VALID

MC JS,FJ,MC VALID

HA JS,FJ,MC INVALID

Data Validation Using MATCH

Column A can be validated against a list of data in Column B by entering the formula "=IF(ISERROR(MATCH(A1,$B$1:$B$3)),"INVALID","VALID")" in Cell C1, and then copying downward.

ColumnA ColumnB ColumnC

JS FJ VALID

FJ MC VALID

MC JS VALID

HA INVALID

References

• Office.microsoft.com: Use Excel to compare two lists of data
• Office.microsoft.com: Check if a cell contains text (case-sensitive)