How to Compare Data in Excel

This TipsMake article will show you how to compare two different data sets in Excel, from two columns in the same spreadsheet to two independent Excel files.

Compare two columns

How to Compare Data in Excel Picture 1

Select the first cell of an empty column. When comparing two columns in a spreadsheet, you will have to display the results in an empty column. Make sure you start from the cell in the same row as the first cell of the two columns you want to compare.

For example, if the two columns you want to compare start at A2 and B2, select cell C2.

How to Compare Data in Excel Picture 2

Enter the comparison formula for the first row. This formula will compare A2 and B2. Change cell values if your columns start in other cells:

=IF(A2=B2,"Khớp","Không khớp")

How to Compare Data in Excel Picture 3

Double-click the Fill box in the lower corner of the cell. Thanks to that, the formula will be applied to all remaining cells in the column, automatically adjusting the corresponding value.

How to Compare Data in Excel Picture 4

Find Matches and Unmatches . They will indicate whether the data in two cells match or not. This method works with character strings, dates, numbers and times. Note that capitalization or lowercase is not considered when comparing ("RED" and "red" will be considered a match).

Compare two workbooks side-by-side

How to Compare Data in Excel Picture 5

Open the first of the two workbooks you want to compare. You can use Excel's Side-by-Side Display function to view two different Excel files on the screen at the same time. You also have the added benefit of scrolling two pages at once.

How to Compare Data in Excel Picture 6

Open the second workbook. At this point, you should have two Excel workbooks open on your computer.

How to Compare Data in Excel Picture 7

Click the View tab on any window.

How to Compare Data in Excel Picture 8

Click the View Side by Side option . You'll find this option in the Window area of the menubar. Both workbooks will appear on the screen and are arranged from top to bottom.

How to Compare Data in Excel Picture 9

Click Arrange All to change the sort direction.

How to Compare Data in Excel Picture 10

Click Vertical and then OK . The workbooks will change so that one book is on the left and one book is on the right.

How to Compare Data in Excel Picture 11

Scroll one window to move both. When Parallel Display mode is enabled, scrolling takes effect in both windows simultaneously. Thanks to that, you can easily find the difference when scrolling along the spreadsheet.

You can exit this feature by clicking the Synchronous Scrolling button on the Display tab.

Compare and find differences in two spreadsheets

How to Compare Data in Excel Picture 12

Open the workbook that contains the two worksheets you want to compare. To use this comparison formula, both worksheets must be in the same file.

How to Compare Data in Excel Picture 13

Click the + button to create a blank spreadsheet. You'll find it at the bottom of the screen, to the right of open spreadsheets.

How to Compare Data in Excel Picture 14

Place the cursor in cell A1 in the new worksheet.

How to Compare Data in Excel Picture 15

Enter the comparison formula. Type or copy the following formula into cell A1 in your new spreadsheet:

=IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

How to Compare Data in Excel Picture 16

Click and drag the Fill information box to the corner of the cell.

How to Compare Data in Excel Picture 17

Drag the Fill information box down. Drag down to the row where the data in the two worksheets ends. For example, if your spreadsheet extends to Row 27, drag this box down to that row.

How to Compare Data in Excel Picture 18

Drag the Fill in information box to the right. Once pulled down, drag it to the right to cover the original worksheet. For example, if your spreadsheet extends to Column Q, drag this box to that column.

How to Compare Data in Excel Picture 19

Find differences in cells that do not match. After spreading the Fill box in the new spreadsheet, you will get cells with information indicating whether there are differences between the spreadsheets. The cell will display the value of the cell on the first worksheet and the value of the same cell on the second worksheet.

For example, A1 in Spreadsheet 1 is "Apples" and A1 in Spreadsheet 2 is "Oranges". A1 in Worksheet 3 will display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.