How to Compare Data in Excel

Compare two columns

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.

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

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.

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

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.

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

Click the View tab on any window.

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.

Click Arrange All to change the sort direction.

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

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

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.

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

Place the cursor in cell A1 in the new worksheet.

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, "")

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

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.

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.

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.

Kareem Winters

Update 10 November 2023