How to Compare Data in Excel

|

|

|

This How.com.vn teaches how to compare different sets of data in Excel, from two columns in the same spreadsheet to two different Excel files.

Method 1

Method 1 of 3:

1
Highlight the first cell of a blank column. When comparing two columns in a worksheet, you'll be outputting your results onto a blank column. Make sure you are starting on the same row as the two columns you're comparing.
- For example, if the two columns you want to compare start on A2 and B2, highlight C2.
2
Type the comparison formula for the first row. Type the following formula, which will compare A2 and B2. Change the cell values if your columns start on different cells:
- =IF(A2=B2,"Match","No match")
Advertisement
This will apply the formula to the rest of the cells in the column, automatically adjusting the values to match.
These will indicate whether the contents of the two cells had matching data. This will work for strings, dates, numbers, and times. Note that case is not taken into consideration ("RED" and "red" will match).^{[1]XResearch source}

Method 2

Method 2 of 3:

You can use the View Side by Side feature in Excel to view two different Excel files on the screen at the same time. This has the added benefit of scrolling both sheets at once.
You should now have two instances of Excel open on your computer.
You'll find this in the Window section of the ribbon. Both workbooks will appear in on the screen, oriented horizontally.
The workbooks will change so that one is on the left and the other is on the right.
7
Scroll in one window to scroll in both. When Side by Side is enabled, scrolling will be synchronized between both windows. This will allow you to easily look for differences as you scroll through the spreadsheets.
- You can disable this feature by clicking the Synchronous Scrolling button in the View tab.

Method 3

Method 3 of 3:

To use this comparison formula, both sheets must be in the same workbook file.
You'll see this at the bottom of the screen to the right of your open sheets.
4
Enter the comparison formula. Type or copy the following formula into A1 on your new sheet:
- =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
Drag it down as far down as the first two sheets go. For example, if your spreadsheets go down to Row 27, drag the Fill box down to that row.
After dragging it down, drag it to the right to cover the original sheets. For example, if your spreadsheets go to Column Q, drag the Fill box to that column.
8
Look for differences in the cells that don't match. After dragging the Fill box across the new sheet, you'll see cells fill wherever differences between the sheets were found. The cell will display the value of the cell in the first sheet and the value of the same cell in the second sheet.
- For example, A1 in Sheet1 is "Apples," and A1 in Sheet2 is "Oranges." A1 in Sheet3 will display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.^{[2]XResearch source}

Community Q&A

Search