Compare Two Microsoft Excel Sheets Using Powerful Functions

Compare two spreadsheets automatically and find differences instantly

There has been a time when you had to compare two Microsoft Excel sheets and highlight the differences automatically. Maybe you were tracking inventory changes, trying to find discrepancies in a financial report, or auditing data from two different sources: knowing how to compare Excel sheets with formulas and conditional formatting is a real asset. This practical guide is designed to help you quickly and accurately compare two spreadsheets, using built-in Excel functions like IF, VLOOKUP, and Conditional Formatting.

🧠 Why Compare Two Excel Sheets?

• Reveal missing or mismatched data in big datasets.
• Show price or value changes between two periods.
• Reconcile monthly reports, customer lists, or transactions.
• Validate data consistency in teams or between departments.

🎯 4 Different Ways to Compare Excel Sheets

🔸 Cell Comparison with Formulas:

Use an IF formula to check if the value of corresponding cells across two sheets differs.

=IF(Sheet1!A1<>Sheet2!A1, "Mismatch", "Match")

The resulting formula-based grid shows which values are different. Suitable for structured data comparisons.

🔸 Highlight Differences Using Conditional Formatting:

Instead of using a third column, highlight the mismatches inside the original sheet visually. Here's how: 

• Select the original dataset (e.g., A1:A100)
• Go to Home -> Conditional Formatting -> New Rule
• Choose: Use a formula to determine which cells to format
• Enter: =A1<>Sheet2!A1 
• Choose a bright fill colour and apply

🔸 Match Values with VLOOKUP:

This is great for checking IDs, names, or SKUs in different sheets.

=IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)), "Not Found", "Found")

Use it to check values in Sheet1 that exist or do not exist in Sheet2.

🔸 COUNTIF for Missing Items:

Looking to find values that appear more than once or not even once in another sheet?

=IF(COUNTIF(Sheet2!A:A, A2)=0,"Missing","Exists")

This is great for cleaning duplicate contacts or performing reconciliation checks.

It's easier to compare Excel sheets than you think-it's a must for working with complex data, finance records, or reports from team members. Automate your comparisons with a few formulas and conditional formatting instead of making expensive manual checks.

Perfect for when you're preparing reports, cleaning inconsistencies, or validating several data sources, these techniques will make your Microsoft Excel workflow more accurate, professional, and efficient!

🎓 Learn More with Other Levels

Take your Excel skills to the next level:

• 🌐 Visit our Other Levels Website

• 📺 Watch free tutorials on our Other Levels YouTube Channel, and learn Microsoft Excel from beginner to advanced.

✍️ Other Articles, Tips & Tricks you would like:

• Manage Complex Spreadsheets in Microsoft Excel with Custom Views
• Build a Simple CRM (Customer Relationship Management) system in Excel
• How to use Microsoft Excel Fill Handle

Work Faster, Smarter, Productively and Efficiently with Other Levels Dashboard Template 

• Employee Turnover Analysis Excel Dashboard
• Employee Working Hours and OPEX Analysis Dashboard
• Finance Payroll Management Dashboard