To compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across the data; for sheets whose rows may be in a different order, match records by a unique key instead. For a quick visual check, open both sheets side by side, while Power Query is a better fit for larger repeat comparisons.
What comparing two sheets means
“Compare” can mean several different things. You might want to find changed values in two versions of the same report, identify rows present in one sheet but absent from another, check that two lists contain the same records, or simply inspect two worksheets at once. The right method depends on which question you need answered.
A cell-by-cell comparison assumes the same item appears in the same position on both sheets. That works well when one sheet is a copy of the other and rows and columns have not moved. It can give misleading results when someone sorts one sheet, inserts a row, or changes the layout: the formula then compares different records just because they occupy the same address.
Before comparing, decide whether you care about exact values, formulas, formatting, or record membership. Two cells can display the same number while containing different formulas; similarly, a value can look identical even if one cell stores text and the other stores a number. Being clear about the goal prevents a technically correct comparison from answering the wrong question.
Compare matching cells with a formula
For a direct comparison, put a check formula in a third worksheet or an unused column. In the example below, the sheets are named January and February, and the values to compare begin in cell A2 on each sheet. Enter this in a result cell on a separate sheet or in an unused location:
With the value to compare in A2 on both January and February, enter =IF(January!A2=February!A2,"Match","Different"). If the two cells contain the same value, the result is Match; if January!A2 is 125 and February!A2 is 130, the result is Different.
In Microsoft 365 for Windows, select the formula cell and drag its fill handle across and down to compare the corresponding cells in a larger block. Relative references adjust as the formula moves. If a sheet name contains spaces or punctuation, surround it with apostrophes. For example, with the corresponding values in A2 on sheets named January Sales and February Sales, use =IF('January Sales'!A2='February Sales'!A2,"Match","Different"). The same comparison logic applies in Excel for Mac and Excel for the web.
If you prefer a simple TRUE or FALSE result, with the values still in A2 on both sheets, enter =January!A2=February!A2. It returns TRUE for equal values and FALSE for unequal values. A text label is often easier to scan in a report, while TRUE/FALSE can be convenient for filtering or further calculations.
Compare entire ranges and highlight differences
For two same-shaped ranges, a grid of comparison formulas is often the clearest solution. Create a result area with the same number of rows and columns, put one comparison formula in its upper-left cell, and fill it through the corresponding area. For example, if January and February both contain data in A2:D100, each result cell can compare the matching addresses. A difference grid reveals where changes occur without overwriting either source sheet.
To make results easier to scan, apply conditional formatting to the result cells. Select the comparison results, then choose Home > Conditional Formatting > Highlight Cells Rules and select a rule appropriate to the labels or values, such as Text that Contains for the word Different. Choose a format and confirm. The exact dialog options can depend on the selected cell type; alternatively, use a formula-based rule when the results are in a predictable layout.
For example, if your comparison results are in C2:C100 and each cell contains the words Match or Different, select that range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different and choose a fill color. This highlights discrepancies while leaving the formula results available for filtering, copying, or review. You can also filter a table of results to show only rows marked Different.
Formatting the source cells themselves is possible too, but formulas used in conditional formatting have reference and sheet-reference constraints. A separate comparison column or sheet is usually easier to maintain than a complicated rule that reaches across worksheets.
Compare records when rows are in a different order
If the same record can appear on different rows, compare by a stable identifier such as an invoice number, product code, or employee ID. A cell-by-cell check would report false differences after a sort because it compares unrelated rows. First confirm that the identifier is unique in each list; duplicate IDs make it unclear which matching record should be compared.
For a lookup-based check, assume January has IDs in A2:A100 and amounts in B2:B100, while February has IDs in column A and corresponding amounts in column B. In C2 on January, enter =IFERROR(IF(B2=XLOOKUP(A2,February!A:A,February!B:B),"Match","Different"),"Missing") and fill down. If January’s ID in A2 exists in February and its amount is equal, the result is Match; if the ID is absent, the result is Missing; if the amount differs, it is Different. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
This check looks for each January ID in February; it does not by itself identify IDs that exist only in February. To find those, reverse the lookup: place the equivalent check on the February list, searching for each February ID in January. Reviewing both directions catches additions and removals as well as changed values. If there are several fields to compare, use one lookup to find the matching record and then compare each relevant field, or use Power Query for a repeatable multi-column workflow.
XLOOKUP returns the first matching result if an ID appears more than once. Therefore, validate uniqueness before relying on it for reconciliation. Also consider whether blank identifiers are possible: a blank key can match a blank entry and produce a result that looks valid but does not represent a real record.
Use Power Query for repeat comparisons
Power Query is useful when sheets contain many rows, the comparison must be repeated, or the lists need to be joined by a key. In Excel for Microsoft 365 on Windows, turn each source range into a table by selecting it and choosing Insert > Table, or pressing Ctrl+T. Confirm whether the table has headers. Give each table a clear name on the Table Design tab so its source is easy to recognize.
Select a cell in the first table and choose Data > From Table/Range to open Power Query Editor. Repeat for the second table. In the editor, use Home > Merge Queries, choose the two queries, and select the matching key column in each preview. Choose a join type based on the question: an inner join keeps records found in both lists, while a full outer join retains records from both lists, including unmatched ones. Expand the merged column to bring the fields you want to compare into view.
After loading or transforming the merged result, compare the paired fields and review rows with differences or missing matches. Power Query can refresh the comparison after the source tables change, which is valuable for monthly or weekly checks. It does not silently make the two source sheets identical: it creates a transformed result, so review that output before using it to update a source.
Power Query is available in Excel for Microsoft 365 on Windows. Power Query capabilities and editing options vary by Excel version and platform; Excel for Mac has some Power Query support, but the Windows instructions and available connectors may not match exactly. If the Data tab does not offer the commands described, use formulas or check the capabilities of your specific Excel release.
View both sheets side by side
For a small comparison where visual judgment matters, open the workbook and choose View > New Window. This creates another window for the same workbook, not a second copy of its data. Then choose View > View Side by Side and select the other window if Excel prompts you. You can scroll through both views and inspect formatting, headings, or unusual entries without building a comparison table.
Side-by-side viewing is most useful for a short list or a layout check. It is not a reliable way to verify thousands of cells, and synchronized scrolling can make the windows move together rather than independently. In that case, turn off Synchronous Scrolling on the View tab if you need to inspect different sections. On Windows, Alt+Tab switches between open windows; on Mac, use the relevant macOS window-switching controls. Ribbon labels can vary slightly by platform or release.
A useful compromise is to arrange the sheets visually to locate a suspected change, then confirm it with a formula. That gives you context without relying on eyesight alone. Avoid editing one window while assuming it represents a separate workbook: both windows show the same underlying workbook, so changes affect the same file.
Choose the right comparison method
- Same layout and row order: Use direct cell formulas. They are quick, transparent, and easy to audit.
- Different row order but a unique key: Use XLOOKUP or Power Query to align records by that key.
- Large recurring comparison: Use Power Query if the required commands are available in your Excel version; refresh the query when new data arrives.
- Small visual or formatting review: Open two workbook windows side by side, then verify important differences with a formula.
- Need to compare formulas rather than calculated results: Inspect the formula bar or use a purpose-built formula comparison approach; a normal equality formula compares calculated values, not formula text.
For a one-off task, the simplest method that matches the data structure is usually best. A formula grid is easier to inspect than a query if the sheets are already aligned. Conversely, a lookup or query avoids the false alarms that come from comparing different records in the same row position.
Common problems and how to avoid them
Rows shifted by sorting or inserted records: A direct comparison reports differences even when the same records exist. Match by a unique ID instead, and check both directions for missing rows.
Numbers stored as text: A cell displaying 100 may store the number 100, while another stores the text string “100”. Excel may treat those differently in comparisons or later calculations. Check the cell format and value type, and standardize the data before interpreting a mismatch as a real business change.
Hidden spaces or inconsistent text: “North” and “North ” may look the same but are different text values. Leading spaces, trailing spaces, inconsistent punctuation, and spelling variations can all create apparent differences. Clean or normalize the data in a separate area before comparing; do not change original records until you have confirmed the intended correction.
Blank cells and formulas returning an empty string: A blank cell and a formula that displays an empty string can look alike. A simple equality check may not distinguish every situation that matters to your workflow. If the difference between an unused cell and a formula-generated blank matters, inspect the formulas or use a separate test designed for that distinction.
Duplicate keys: A lookup can return one matching row even when several records share the same ID. Check for duplicates or construct a genuinely unique key from multiple fields before treating a lookup result as definitive.
Case sensitivity: Standard Excel equality comparisons are generally not case-sensitive, so text such as “abc” and “ABC” can compare as equal. If capitalization itself is significant, use the case-sensitive EXACT function; otherwise, document that case differences are intentionally ignored.
Unexpected formula results: A comparison formula reports the result of the cells’ current values, and formulas may recalculate. If the workbook uses external links, volatile formulas, or stale data connections, confirm that the source values are current before drawing conclusions.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Excel does not automatically flag every difference between arbitrary worksheets just because they are in the same workbook. Set up formulas, conditional formatting, a Power Query comparison, or a visual side-by-side review according to how the data is arranged.
How do I compare two sheets and show only differences?
Create a result column or grid that labels matching and differing values, then filter the results to show only differences. For an aligned range, compare corresponding cells; for lists in different orders, match records by a unique key first.
Can I compare two sheets with different row orders?
Yes. Use a stable unique identifier to look up each record on the other sheet, or merge the lists in Power Query. Do not compare row 2 with row 2 unless you know those rows represent the same record.
Does a normal comparison check formulas or displayed values?
A formula such as an equality check compares calculated cell values. Two cells can therefore return the same result while containing different formulas. If formula differences matter, inspect the formulas themselves rather than relying on a value comparison.
Can I compare sheets in separate workbooks?
Yes. You can use formulas that reference another open workbook, import both tables into Power Query, or open both files and review them visually. External workbook links may need updating, so confirm that Excel is using the intended, current source file.
Keep the comparison reviewable
Preserve the source sheets and put results in a separate area, sheet, or query output. Label what was compared, note the matching key and date, and keep the criteria consistent between runs. For consequential changes, review a sample of matches and mismatches manually before updating records. A clear comparison should show not only that two values differ, but which records were matched and why the method is appropriate.
Related
- How to Import CSV to Excel: Steps and Common Fixes
- Consolidate Data in Excel: Methods and Steps
- Can’t Merge Cells in Excel? Common Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Add Page Numbers in Word Bottom Right CornerTo add page numbers at the bottom right in Word for Microsoft 365 on Windows, select Insert >…
- Text After an Excel Formula: Add Labels and Sheet NamesTo put text after an Excel formula, join the formula result to a quoted text string with an…
- Excel Time Plus Minutes: Formulas and FixesTo add minutes to an Excel time, add the minutes divided by 1,440: with a time in A2…
- Excel Merge Cells Per Row: The Right Way to Do ItTo merge cells separately across each row in Excel for Microsoft 365 on Windows, select the full range…
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.