Data Comparison take two, equals sign
This is a series of articles with solutions that evolve as Excel evolves.
- Data Comparison take one, AND or EXACT
- Data Comparison take two, EQUALS
- Data Comparison take three, EQUALS and HYPERLINK
- Data Comparison take four, PivotTable
- Data Comparison take five, Power Query
Excel 365 (Jan-2020+)
Do you need to compare two lists of data in Excel and find differences?
How can you do it quickly?
Compare each cell with a single formula
First copy your original data into a worksheet.
Copy the new data into the same worksheet, next to the original data.
Sort both lists into the same order.
Make sure that the comparison range is empty. Otherwise a #SPILL error will be returned.
Now you are ready to start comparing the lists.
We are going to use the equals sign (=) to do the comparison.
=A3:B6=D3:E6
When you press enter, the formula will ‘spill’ across the comparison area. It’s dimensions matching the original data. The formulas in the spill cells will be greyed out in the formula bar. If the formula needs to be changed, the change needs to be made in the top left cell of the range.
If the cells match then the formula returns TRUE otherwise it returns FALSE. The comparison ignores the case of the data.
Ignore spacing differences
Some data will have extra spaces or use spaces for padding. This will cause the formula to return FALSE.
In the screenshot, an extra space has been added to the end of the address in the first row. This isn’t visible unless you edit the cell.
If you consider the data as matching regardless of the spacing, the CLEAN and TRIM functions can be combined.
CLEAN removes non-printable characters. TRIM removes any leading or trailing spaces as well as reducing all internal spacing to single spaces.
=TRIM(CLEAN(A3:B6))=TRIM(CLEAN(D3:E6))
Conditional Formatting
If the lists contain a large amount of data, it is difficult to immediately see cells marked as FALSE. Conditional formatting can be used to highlight cells containing the FALSE value.
Select the comparison data range. Navigate to the Home menu tab and click on the Conditional Formatting icon. Select Highlight Cell Rules and Equal To.
Type FALSE into the box and click on the OK button.
Any cells in the comparison range containing FALSE will be highlighted.
