How to Compare Data in Excel
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.
You should read it
- How to compare data on 2 Excel columns
- How to compare data on 2 different sheets in Excel file
- How to compare data on 2 columns in an Excel file
- How to Compare Two Excel Files
- How to Compare Data in Excel
- How to compare 2 Excel files to see if there is any difference
- How to Match Data in Excel
- The use of the Split tool separates the Excel data table
- How to Compare Two Excel Files
- Summary of 50 Excel shortcuts you should know by 2023
- How to create duplicate data entry notifications on Excel
- How to compare documents, compare files, compare folders with WinMerge