Effortless Methods for Comparing Data in Spreadsheets Using Microsoft Excel
Excel offers several effective methods for comparing two columns or workbooks to find matching or non-matching data. In this article, we'll explore the common methods—Conditional Formatting and formula-based approaches—for both comparing columns within the same workbook and comparing separate workbooks. Additionally, we'll discuss the use of third-party tools for advanced comparisons.
Comparing Two Columns in the Same Workbook
Using Conditional Formatting (Highlight Matches or Differences)
- Select the cells in the first column you want to compare.
- Navigate to the Home tab and click on Conditional Formatting.
- Choose Highlight Cells Rules, followed by Duplicate Values.
- Decide whether to highlight duplicate or unique values and pick a formatting style, then click OK. This will highlight cells in the first column that are or are not present in the second column, making it easy to spot matches or mismatches visually.
Using Formulas (e.g., MATCH or VLOOKUP)
- In a new column next to your first list, use a formula like: This checks if the value in A2 exists anywhere in column B and returns "Match" or "No Match."
- Copy the formula down to compare all rows row-by-row.
Comparing Two Separate Workbooks
To compare two workbooks, you'll need to have two instances of Excel open on your computer.
- Open both workbooks side-by-side by clicking the View tab on either window and selecting View Side by Side.
- Use formulas like or referencing the other workbook, for example:
- Alternatively, save both workbooks and use specialized tools for more complex comparisons.
Additional Tools
For advanced, exact comparisons including key-columns and formula tracing, you can use dedicated third-party tools like xlCompare which supports Windows and offers folder synchronization, formula debugging, and detailed reports to pinpoint matches and differences between workbooks.
Summary
- For quick, built-in solutions, use Conditional Formatting to highlight duplicates/uniques or formula methods such as or for detailed results.
- For comparing entire workbooks and complex datasets, dedicated comparison software like xlCompare provides comprehensive, automated reports.
These methods work the same on Windows and Mac versions of Excel.
This article was co-authored by Rain Kengly, a tech writer and editor with over 5 years of professional writing experience.
How to use conditional formatting to highlight matching or non-matching data in the same workbook by following these steps: Select the cells in the first column, navigate to the Home tab, click on Conditional Formatting, Choose Highlight Cells Rules, followed by Duplicate Values, decide whether to highlight duplicate or unique values, pick a formatting style, then click OK.
For advanced, exact comparisons between two separate workbooks, consider utilizing third-party tools like xlCompare which offers folder synchronization, formula debugging, and detailed reports on both Windows and Mac versions of Excel.
