Compare Two Excel Sheets Easily in Microsoft Excel

How-to basics · Updated November 21, 2026

To compare two Excel sheets quickly, put their values in the same row and column order, then use a helper column with an IF formula to label matching and different cells. If the rows are arranged differently, compare records by a shared key, such as an ID, instead. Before starting, save a copy of the workbook and check that both sheets contain the same kind of data.

What You’ll Need

  • A workbook containing both sheets, or two workbooks you can open at the same time in Excel for Microsoft 365 on Windows.
  • A shared layout for a direct cell-by-cell comparison, or a unique identifier column—such as an order number or employee ID—if rows may be in a different order.
  • A spare column for comparison results. For large or important datasets, keep an unchanged copy of the original files as a reference.

Step-by-Step

  1. Choose the comparison method. Look at how the data is organized before writing a formula. If each row represents the same record in both sheets and the records appear in the same order, a direct comparison is simplest. If the rows may have moved, match them using a key column. If you only need to compare two whole workbooks, not two sheets, Excel’s View Side by Side feature can help you inspect them, but it does not automatically identify changed cells.
  2. Prepare the sheets. Confirm that headers mean the same thing and that corresponding columns use compatible data types. Remove any filters that hide rows, and note whether blank rows or totals are part of the data. For a direct comparison, make sure the same record is on the same row in both sheets and the same field is in the same column. You can copy one sheet to a new worksheet in the same workbook if that makes formula references easier; keep the original data unchanged.
  3. Add a helper column for aligned data. On the first sheet, insert a blank column beside the data and label it Comparison. For example, if Sheet1 and Sheet2 have corresponding values in A2, enter the following in Sheet1 cell C2. This assumes both sheets have the same records in rows 2 onward, with the first value to compare in column A. It returns Match when the two cells are equal and Different otherwise: =IF(Sheet1!A2=Sheet2!A2,"Match","Different"). If you enter the formula on Sheet1, the Sheet1 reference can also be written as A2. For worksheet names containing spaces, enclose the sheet name in apostrophes, as in 'January Data'!A2.
  4. Fill the formula across and down. To compare more than one column, copy the formula across the helper area so it checks each corresponding field, then fill it down to the last data row. Excel adjusts relative cell references as you copy. Alternatively, make a helper column for each field if you want to see which particular column differs. Check the first and last few formulas in the filled range: a range that stops early can leave records unexamined, while one that extends too far can flag empty rows as matches.
  5. Make differences easy to see. Select the comparison results and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different, choose a formatting style, and select OK. This colors the result labels, rather than changing the underlying data. You can then filter the helper column to show only Different. If you prefer a plain review, use the filter arrow in the Comparison header and select only that result.
  6. Compare rows that are not in the same order. Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100, while Sheet2 has an ID in A2 and its amount in B2. Put this formula in Sheet2 C2, then fill it down. It looks up the ID from Sheet2 in Sheet1 and compares the associated amount. For a matching amount it returns Match; for a different amount it returns Different; if the ID is not found, it returns Missing from Sheet1: =IFERROR(IF(B2=XLOOKUP(A2,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100),"Match","Different"),"Missing from Sheet1"). XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The formula assumes IDs are unique; duplicate IDs need extra checking because XLOOKUP returns the first match.
  7. Check the reverse direction. A lookup from Sheet2 into Sheet1 finds records on Sheet2 that are missing from Sheet1, but it cannot identify records that exist only in Sheet1. Repeat the key-based comparison in the other direction, swapping the lookup and return ranges, to find those records. For a small comparison, sorting both sheets by the unique key and checking the lists is another practical way to spot unmatched IDs.
  8. Review before acting on results. Filter for Different and missing records, then inspect a few results directly on both sheets. Confirm that formulas refer to the intended columns and that the first and last records are included. A comparison formula reports whether its selected values match; it does not determine whether a change is correct or explain why it happened. Record or copy the results to a review sheet before making any edits to the source data.

Common Mistakes

Comparing rows that do not represent the same records. A direct comparison checks cell positions, not meaning. If row 12 is a different customer in each sheet, Excel can report a difference even when the customer records exist in both places. Align the rows first or compare using a unique ID.

Overlooking hidden or filtered rows. Filters can make a sheet look shorter than it is. Clear filters or check the row numbers and formula fill range before drawing conclusions. Also check for records below the apparent end of the data.

Assuming visual similarity means equal values. A number stored as text may look like a number but behave differently in formulas and sorting. Dates can also be stored as text, and displayed values may be rounded while the underlying values differ. Check the cell format and formula bar when a result seems unexpected.

Forgetting duplicate or blank keys. A lookup based on a repeated ID may select only one of several matching records. Blank keys can also create confusing results. Use a genuinely unique identifier, check for duplicates, and decide how blank IDs should be handled before comparing.

Overwriting original information. Sorting one sheet without sorting all its columns together can scramble records. Keep an untouched backup, and select the whole data range before sorting so each row stays intact.

Tips

Use clear sheet names and keep the comparison output separate from source values. A label such as “Check” or “Comparison” makes it less likely that someone will mistake a formula result for original data. For a recurring task, use the same column layout and key each time, and document what the comparison considers a match.

Decide how blanks should be treated. In the direct formula above, two empty cells compare as Match, while a blank compared with a populated cell returns Different. That may be appropriate, but if blank means “not reviewed” in your files, treat those results separately instead of assuming they are valid matches.

Excel’s ordinary equality comparison is not case-sensitive for text, so abc and ABC are treated as equal. If letter case matters, use the EXACT function in a helper formula. For example, with corresponding text values in Sheet1 A2 and Sheet2 A2, enter =IF(EXACT(Sheet1!A2,Sheet2!A2),"Match","Different") in Sheet1 C2; it returns Different when the capitalization differs. EXACT checks text content, not formatting such as font color or cell borders.

For a quick visual review of two open workbooks, choose View > View Side by Side. Excel scrolls the windows together when Synchronous Scrolling is enabled. This is a manual inspection aid, not an automated comparison, so it is most useful for shorter sheets or for checking a suspicious section after formulas have flagged it. On Excel for Mac, use the View tab’s side-by-side viewing options where available; labels and window arrangement can vary by version.

Troubleshooting

Everything says Different. First verify that the formula compares the intended cells and that each sheet’s rows are aligned. Then inspect the values in the formula bar. Differences can come from extra spaces, text-versus-number mismatches, dates stored as text, or small decimal differences that are hidden by cell formatting. Fix or standardize the underlying data only after confirming the intended value; avoid broad replacements that could alter legitimate entries.

Some changed records are missed. Check the formula’s fill range and confirm that the comparison includes all relevant columns. With a key-based lookup, verify that the IDs are spelled and typed consistently and that the lookup range includes the full list. Look for duplicate IDs: a lookup that returns the first matching record may not compare the intended duplicate.

The formula displays an error or does not calculate. In Excel, formulas usually recalculate automatically, but a workbook set to manual calculation may need Formulas > Calculate Now. Make sure the formula begins with an equals sign and that text results are enclosed in straight double quotes. If your worksheet name contains spaces, surround it with apostrophes in the reference. If Excel does not recognize XLOOKUP, use Microsoft 365 or Excel 2021 or later, or choose a method supported by your Excel version.

Conditional formatting highlights the wrong cells. Select the intended helper-column range and review the rule under Home > Conditional Formatting > Manage Rules. The rule should look for the result text Different, and its Applies to range should cover the comparison results—not the original data by mistake. Reapply the rule after correcting the range.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. A formula can label corresponding cells or records, and conditional formatting can make differences visible. Excel does not infer which records correspond unless you align the rows or provide a shared key.

Can I compare two separate Excel files?
Yes. Open both workbooks and create formulas that refer to the other workbook, or copy the sheets into one workbook before comparing. External references can depend on the source file’s name and location, so keep both files available while setting up and reviewing the comparison.

Does the comparison check formatting?
No. The equality formulas described here compare values, not formatting such as colors, fonts, or borders. Inspect formatting separately if appearance is part of the requirement.

What is the easiest way to compare sheets with different row orders?
Match records by a stable, unique key, then compare the values returned for that key. Sorting both lists by that key can also help, but only sort the complete data range so the fields stay attached to their records.

Can I compare more than one column?
Yes. Copy the cell-by-cell formula across a helper area to compare corresponding columns, or create a separate result for each important field. With key-based data, use a lookup for each field you want to check so a change in one field is not hidden by an unchanged one.

What should I do with a Different result?
Use it as a prompt to investigate, not as an instruction to overwrite data. Check both source cells, confirm whether the difference is intentional, and document the correct value before updating either sheet.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.