How to Compare Columns in an Excel Spreadsheet

Written by ian low
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Compare Columns in an Excel Spreadsheet
Spreadsheet functions and formulas can quickly validate columns of data (number background image by kuhar from Fotolia.com)

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."

Other People Are Reading

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 £65 £97 £32

Feb £130 £136 £6

Mar £195 £175 £19

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.