For most people, the best way to compare data in two Excel sheets is to match records using a unique ID, then use an Excel formula to flag missing or changed values. It is transparent, works in Microsoft 365, and avoids treating rows as different just because they are in a different order.
What to Look For
Whether rows can move. First decide if both sheets have records in the same order. If they do, a cell-by-cell comparison is simple. If rows may have been sorted, added, or deleted, match them by a stable key such as an invoice number, employee ID, or product code. Comparing row 12 with row 12 is not reliable when the same record could be on row 30 in the other sheet.
What counts as a match. Are you checking that a record exists, that selected fields are equal, or that every value and formula is identical? Decide whether spaces, capitalization, dates, blanks, and numeric precision should count as differences. For example, a quantity stored as the number 5 is not the same underlying value as text that looks like 5.
Duplicate keys and missing records. A lookup assumes the matching key identifies the right record. If an ID appears more than once, a lookup may return only one occurrence and hide a duplicate. Check key columns for duplicates, and compare in both directions if you need to find records present in Sheet 1 but absent from Sheet 2, and vice versa.
Repeatability, scale, and access. A quick formula suits occasional checks and leaves a visible audit trail. Power Query is better for recurring imports and larger tables. A dedicated comparison tool can save setup time, but may require a particular Excel edition or a paid add-in. Consider whether colleagues need to repeat the check, whether the file contains sensitive data, and whether everyone has the same tools.
1. Use a Key and Lookup Formula for the Best General-Purpose Check
For an ordinary comparison where records may be in a different order, use a unique key to find the corresponding record, then compare the fields that matter. In this example, both worksheets have IDs in column A, product names in B, and quantities in C. Sheet1 is the version to check; Sheet2 is the reference. Make sure each ID is unique before relying on the result.
With IDs in A2:A100 on both sheets and product names in B2:B100, enter this in Sheet1 cell D2 and fill it down. It returns Match if the names agree, Different if the ID exists but the names do not agree, and Missing from Sheet2 if the ID is not found. For example, if ID 104 is on both sheets and both name cells contain Widget, the result is Match; if Sheet2 says Gadget, it is Different; if 104 is absent from Sheet2, it is Missing from Sheet2.
With the layout above, using Microsoft 365 or Excel 2021 or later, enter: =IFERROR(IF(B2=XLOOKUP($A2,Sheet2!$A$2:$A$100,Sheet2!B$2:B$100),"Match","Different"),"Missing from Sheet2")
To check quantity as well, make another comparison column using the same pattern, changing the compared value from B2 to C2 and the return range from Sheet2 column B to column C. This makes each difference easy to locate. To find records that appear only in Sheet2, reverse the sheets and run the check in the other direction. XLOOKUP is not available in older Excel versions; for those, use a carefully constructed INDEX and MATCH formula or Power Query instead.
Strengths: The result is explicit, sortable, and easy to review, and the formula can be reused on the next pair of sheets. Trade-offs: You must choose the correct key and comparison columns. A duplicate key can make a lookup misleading, and a formula for one field does not automatically verify every other field. Best for: People who want an explainable, flexible check without installing anything.
2. Compare Aligned Cells with Conditional Formatting
If both sheets have the same layout and records remain on the same rows, put copies of the two ranges side by side on a temporary comparison sheet. For example, place the first version in A2:D100 and the second in F2:I100. Select A2:D100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use a rule based on the relative comparison below, and choose a fill color so differences stand out.
With the first range in A2:D100 and the corresponding second range in F2:I100 on the same worksheet, use this rule formula while A2:D100 is selected: =A2<>F2 Excel evaluates each selected cell against the corresponding cell four columns to its right. A2 and F2 both containing North produce FALSE and no highlight; if F2 contains South, the result is TRUE and the first range’s A2 cell is highlighted. Relative references let the rule adjust for each cell in the selected range.
This is a visual scan, not a row-matching method. A shifted row can create a screen full of apparent differences, and it does not by itself identify records present in just one sheet. It also compares the values Excel sees, not necessarily the displayed formatting. Use a temporary copy if rearranging data, and avoid overwriting the original sheets.
Strengths: It is quick to set up and makes differences visible without adding a result column. Trade-offs: It depends on identical row order and layout, and the highlights are less useful as a report than a status column. Best for: A fast check of two aligned exports, forms, or small tables. Conditional Formatting is also available in Excel for Mac; menu labels or rule options can differ slightly by version.
3. Use Power Query for Repeatable, Larger Comparisons
Power Query is a good fit when you receive fresh versions of two tables regularly or need to compare many rows without building a sheet of lookup formulas. Format each data range as an Excel table, give the tables clear names, and create a query for each with Data > From Table/Range. In Power Query Editor, use Home > Merge Queries, select the two queries, and choose the matching key column in each preview. The key values must have compatible data types.
For records that exist in one table but not the other, choose a Left anti join to return rows found only in the first table, or a Right anti join to return rows found only in the second. A Full outer join can retain records from both sides, including unmatched records. For records with a matching key, expand the columns from the second table and compare the fields you care about. Then choose Home > Close & Load to return the result to Excel. When source tables are updated, refresh the query rather than rebuilding the comparison.
Power Query merges records; it does not automatically decide what your business considers a meaningful difference. You still need to compare the relevant fields, handle duplicate keys, and interpret the output. A merge on a non-unique ID may produce multiple matches, so investigate duplicates rather than assuming the result is one row per ID. Power Query is built into current desktop Excel for Windows. Excel for Mac has Power Query in Microsoft 365, but supported sources and features vary by version; Excel for the web is not a like-for-like replacement for the full Windows editing experience.
Strengths: It is refreshable, handles larger data sets well, and can clearly separate unmatched rows from matched records. Trade-offs: The editor and join concepts take some learning, and the query needs maintenance if source columns change. Best for: Recurring monthly or weekly reconciliations, imports, and comparisons that should produce the same steps each time.
4. Check for Spreadsheet Compare on Windows
Spreadsheet Compare is a Microsoft utility for comparing two workbook files. If it is installed, open it from the Windows Start menu by searching for Spreadsheet Compare, then choose Compare Files and select the two workbooks. It presents detected differences in a comparison window, which can be useful when the question is broader than whether two lists contain the same records: you may also need to inspect changed formulas, values, or workbook structure.
Availability depends on the Office edition and installation; it is commonly associated with certain professional or enterprise editions, and is not included in every Microsoft 365 plan. If you cannot find it in the Start menu, check your organization’s Office installation or license rather than assuming it is a standard Excel button. It is a Windows desktop utility, not a normal feature in Excel for the web or Excel for Mac. For a data reconciliation based on IDs, a formula or Power Query may give a clearer, more targeted result than a whole-workbook comparison.
Strengths: It can inspect workbook-level changes without requiring you to build comparison formulas. Trade-offs: It may not be available in your edition, and a workbook-level report can include differences that are irrelevant to a particular data check. Best for: Windows users with access to the utility who need to compare two versions of a workbook, not just align two tables.
5. Consider Ablebits Compare Sheets for a Guided Add-in
A commercial Excel add-in such as Ablebits Compare Sheets can provide a guided workflow for selecting two ranges, identifying matching records, and highlighting or reporting differences. This can be appealing if non-technical colleagues need to run comparisons and the built-in methods feel too manual. Before buying, review the current feature list, supported Excel versions, license terms, and whether the add-in works in the environment where the workbook will be used.
An add-in is not a substitute for deciding how records should match. You still need a reliable key, a policy for duplicate IDs, and clarity about which columns matter. Also consider your organization’s rules for installing third-party software and sending data to any connected service. Test the workflow on a copy of a representative workbook before relying on its output for important reconciliation work.
Strengths: A purpose-built interface can reduce setup and make repeat comparisons easier for occasional Excel users. Trade-offs: It may involve a purchase, installation approval, and compatibility checks; it also adds a dependency beyond Excel itself. Best for: Teams that compare sheets often and value a guided interface enough to justify the cost. For a one-time task, formulas or Power Query are usually a sensible first try.
How to Choose
Start with the shape of the data. If the records are in the same order and the columns line up, use conditional formatting for a quick visual check. If order can change, choose a stable unique ID and use XLOOKUP to compare fields. If there is no unique ID, first determine how a record should be identified, perhaps by combining several fields, or ask the data owner to provide a reliable key. Guessing at matches can create false results.
Match the method to how often you work. For one small comparison, a formula is usually the lowest-cost option and gives a clear result for review. For a repeated process with refreshed exports, Power Query’s setup pays off because the steps can be refreshed. If you need a broad workbook-to-workbook audit, see whether Spreadsheet Compare is available. Consider a paid add-in when the time saved and easier handoff matter more than the license and installation overhead.
Plan for exceptions before trusting the output. Check whether IDs are duplicated, blank, or stored as different types; decide how to treat extra spaces, blank cells, dates, and rounding. Compare in both directions when you need a complete list of additions and deletions. Keep the original source sheets unchanged, save a separate result, and inspect a sample of both matches and flagged differences. For a high-stakes reconciliation, have another person review the matching rules and a sample of results.
In short, the choice is not simply about which tool has the most features. It is about matching records correctly, showing the differences you care about, and being able to explain or repeat the check. Excel for the web can handle many formula-based checks, but desktop Excel is the safer choice for Power Query setup and desktop-only utilities.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Not as one universal command in every Excel edition. You can use formulas and conditional formatting, Power Query, or Spreadsheet Compare if it is available in your Windows Office installation.
What is the best way if rows are in a different order?
Match records using a unique key such as an order ID, then compare the needed fields with XLOOKUP or a Power Query merge. Do not compare by row number when order can change.
Can I find rows that are missing from either sheet?
Yes. Run a key-based check in both directions, or use Power Query anti joins to return rows found only in one table. A one-way check cannot reveal records missing from the other side.
Does XLOOKUP work in every Excel version?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not older perpetual versions such as Excel 2019. Use a compatible lookup approach or Power Query if your version lacks it.
Can I compare sheets in Excel for Mac or the web?
Formulas and conditional formatting are available in both, though menus can differ. Power Query support on Mac varies by version and source, while Spreadsheet Compare is a Windows utility; use desktop Windows Excel when those features are required.
Should I buy a comparison add-in?
Only if you compare sheets often enough to justify the cost and its workflow fits your data. For occasional checks, first try formulas for keyed records or Power Query for repeatable comparisons.
Bottom Line
For most Excel users, start with a unique key and a formula that reports Match, Different, or Missing from Sheet2. It is inexpensive, easy to inspect, and works even when rows have been reordered. Choose Power Query for recurring or larger reconciliations, conditional formatting for rows that already align, and a workbook comparison utility or add-in only when its broader report or guided workflow addresses a real need. Whatever method you choose, validate the keys and check both directions before treating the results as complete.
Related
- Excel Named Range: How to Create One
- 2-Way Data Table in Excel: Setup, Examples, and Tips
- Yes or No Column in Excel: Create and Use a Dropdown List
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Page Numbers to an Essay in Microsoft WordTo add page numbers to an essay in Microsoft Word, open the Insert tab, choose Page Number, select…
- Track Changes in Word 365: A Practical How-To GuideTo track edits in Word 365, open your document, go to Review > Track Changes, and choose whether…
- Google Sheets FILTER Function: Basics and ExamplesGoogle Sheets’ FILTER function returns only the rows or columns that meet conditions you specify, while leaving the…
- Consolidate Multiple Excel Files into One WorkbookYes. In Excel for Microsoft 365 on Windows, the most repeatable way to consolidate several similarly structured files…
- Out of Office in Outlook for iOS: Set Automatic RepliesTo set an out-of-office reply in Outlook for iOS, open the app’s Settings, select the email account you’ll…
- Excel Table Color Combinations: Choose and CustomizeExcel table color combinations come from built-in table styles or a custom style you create: select a table,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.