Knowing how to compare data in Excel is useful for financial record keeping and list management. For example, comparisons can be used to make sure the same data isn’t entered twice, to check that the correct information has been entered for a record and to verify that quarterly or annual information has been entered according to schedule. Comparing data in Excel is a skill many employers require, so it's worth taking the time to learn how to do it!

Ad

Steps

Method 1 of 4: Use a Formula

1

Use the IF statement. Using a conditional statement makes it so that you're not processing all the info in the spreadsheet, just the parts you want to check.

Identify the two columns that contain the data to be compared. To make this easier, copy the data into columns A and C on a new worksheet.

Click on cell B1 or a blank cell in the spreadsheet. Type the formula “=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)”. This formula assumes that you are working with ranges A1-5 and C1-5. Change these values to the cells that you are working with, if necessary.

Highlight cells B1 to B5. Click “fill,” then click “down.” If working with a column other than column B, highlight the applicable cells in that column.

Check column B to see the duplicate numbers that have been found in columns A and C.

Ad

2

Use VLOOKUP or HLOOKUP. In these formulas, “V” means vertical and “H” stands for horizontal. Use VLOOKUP when the comparison values are located in a column to the left of the data to be searched for. Use HLOOKUP when values are located in a row across the top of a table of data. It will search a specified range of rows.

Enter the formula “VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).” In this formula, lookup value is the value to search for. Table array is the columns to search. Col_index_num is the column number the matching value must be returned from. Range lookup is an optional command used to specify whether to return an exact or approximate match. An exact or closest value is returned if there is no range lookup.

Type “HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)” to use HLOOKUP. The commands are the same as VLOOKUP except for row index, which specifies the row that data is to be returned from.

Method 2 of 4: Write a Visual Basic Macro

1

Click the “Alt” and “F11” keys to open the VB editor in Excel. If you are using Excel 2010, select "Visual Basic" under the Developer tab.

Place your mouse in the module sheet. Touch the “Ctrl” and “V” keys to paste the code into the module sheet. Modify the cell ranges, if necessary, to correspond to your data. The default code will compare the A1:A5 and C1:C5 ranges.

5

Hit the “Alt” and “F11” keys to return to Excel, or click the Excel icon at the upper left hand part of the page.

6

Highlight range A1:A5 in your worksheet, then click "Macro" under the Developer tab. In earlier versions of Excel, "Macro" is found under “Tools” from the menu.

7

Choose “Find Matches” then select “Run.”

8

See Column B for a list of duplicate values.

9

Visit the Excel Tip.com website to learn how to compare data in Excel with more complex macros. The macros compare data between tables and worksheets and are free to use. Follow the same steps to open the Visual Basic editor and insert data into the module.

Method 3 of 4: View Side by Side

1

Open two comparison files.

“View Side by Side” allows easy checking by looking.Scrolling are synchronized.

2

Select the “View”-“View Side by Side”.

3

If needed, select “View”-“Arrange All”-“Vertical”.

Method 4 of 4: Download specialized software to compare data in Excel

1

There are many shareware applications available for use in comparing data in Excel. Do an Internet search for the function that you want to perform, such as comparing data in two tables or two spreadsheets.

Use “Diff EngineX” from Florencesoft to generate reports of matching cells in worksheets or workbooks.

Download “Excel Compare” from Formula Software to compare data and generate reports in ranges of sheets or entire spreadsheets.

Try the ”Excel Diff” to generate graphical compare result between two workbooks.

Ad

We could really use your help!

Can you tell us aboutbuilding and engineering?

Yes

No

building and engineering

how to build a strong catapult

Can you tell us aboutbox braids?

Yes

No

box braids

how to style box braids

Can you tell us aboutreference requests?

Yes

No

reference requests

how to ask for a reference

Can you tell us aboutFacebook applications?

Yes

No

Facebook applications

how to create a Facebook application

Thanks for helping! Please tell us everything you know about

...

Tell us everything you know here. Remember, more detail is better.

Tips

Provide Details.

Please be as detailed as possible in your explanation. Don't worry about formatting! We'll take care of it.
For example:Don't say: Eat more fats.Do say: Add fats with some nutritional value to the foods you already eat. Try olive oil, butter, avocado, and mayonnaise.