Yes. In Excel for Microsoft 365 on Windows, compare matching cells on two sheets with a formula such as =Sheet1!A1=Sheet2!A1; it returns TRUE when Excel considers the cell values equal and FALSE when they differ. For a clear review, put that formula in a comparison sheet, fill it across and down, and optionally highlight the FALSE results.
What a cell-by-cell comparison checks
A cell-by-cell comparison checks corresponding positions: A1 against A1, B1 against B1, and so on. It is the simplest approach when both worksheets have the same layout and the records have not been rearranged. It can reveal changed amounts, missing entries, or different text without requiring you to inspect every cell manually.
The basic equality formula compares the values Excel evaluates in the two cells. It does not tell you why they differ, and it does not automatically compare formatting, comments, data validation, or the formulas that produced the displayed results. A TRUE result means the comparison evaluates as equal under Excel’s rules, not that every property of the two cells is identical.
For the formula above, the assumed layout is that both worksheets are in the same workbook, are named Sheet1 and Sheet2, and contain corresponding data beginning at A1. If a sheet name contains a space or punctuation, enclose it in single quotation marks: with sheets named Sheet 1 and Sheet 2 and corresponding data in A1, use ='Sheet 1'!A1='Sheet 2'!A1.
Build a comparison grid with a formula
A separate comparison sheet keeps the original worksheets untouched and makes the results easy to scan. Create a blank sheet, click its A1 cell, enter the comparison formula, then copy it over the same-sized area as the data on the two source sheets.
- Confirm that the two source sheets use matching row and column positions.
- Add a worksheet for results, such as Compare, and select A1.
- With source values in A1:Z100 on sheets named Sheet1 and Sheet2, enter
=Sheet1!A1=Sheet2!A1in Compare!A1. - Copy the formula across 26 columns and down 100 rows. Relative references adjust so the next result compares B1 with B1, then A2 with A2, and so forth.
- Read TRUE as equal and FALSE as different. Filter or search the result grid for FALSE if you only need to investigate discrepancies.
For example, if Sheet1!A1 contains 42 and Sheet2!A1 contains 42, the result is TRUE. If Sheet2!A1 contains 45 instead, the result is FALSE. In a large comparison, label the result sheet and retain the original data so another reviewer can identify which source is which.
If the worksheets have different dimensions, use the larger relevant range for the comparison grid. Cells beyond the smaller sheet may be blank, so inspect those results carefully: blank cells can behave like zero in some Excel comparisons. A formula result alone may not make it obvious whether one sheet is missing a value or both cells are genuinely empty.
Highlight differences visually
Conditional formatting can make mismatches stand out without replacing the TRUE/FALSE results. The most dependable simple setup is to apply the rule to the comparison grid, where each cell already contains a Boolean result. This avoids a common snag: a conditional-formatting rule applied directly to one worksheet cannot always use an ordinary direct reference to a different worksheet.
With comparison results in A1:Z100 on the Compare sheet, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =A1=FALSE, choose a fill color, and confirm. The formula is written relative to the top-left cell of the selected range, so Excel adjusts it for the other cells. FALSE results will be colored; TRUE results will remain unhighlighted.
You can also use a filter on a comparison grid, or simply use Find to locate FALSE. If you want to highlight the original values rather than the comparison results, consider adding a helper column or grid first. That extra step makes the rule easier to audit and avoids fragile references across worksheets.
Compare text, formulas, and formatting
Excel’s ordinary equals comparison is not case-sensitive for text: “North” and “north” generally compare as equal. If capitalization itself matters, use the EXACT function. With corresponding text values in Sheet1!A1 and Sheet2!A1, enter =EXACT(Sheet1!A1,Sheet2!A1) in a result cell. EXACT returns TRUE only when the text strings match, including capitalization. It is intended for text comparisons, so use the ordinary equality formula for general mixed data unless you specifically need a text-sensitive test.
To compare formulas rather than their calculated results, a simple equality formula is not enough. For instance, =A1+B1 and =C1 can display the same number while being different formulas. You can inspect the formula bar, or use Excel’s formula-view options and a dedicated workbook comparison tool when formula changes are important. Also note that formatting differences—such as a changed number format, font, or fill—do not make two cell values unequal.
Dates and times are stored as numeric values with display formats. Two cells may look different because one shows a date and another shows a serial number, even if their underlying values match. Conversely, displayed values may look the same while the underlying values have extra decimal precision. When a result surprises you, check the formula bar, number format, and underlying value before concluding that the comparison is wrong.
When rows or columns are not aligned
Position-based comparison assumes that a record in one sheet occupies the same row and column as its counterpart in the other. If someone sorted one sheet, inserted rows, or changed the order of customers, comparing A2 with A2 can report many differences even when the same records exist in both sheets.
In that situation, align records using a stable identifier—such as an invoice number or employee ID—before comparing the other fields. You can sort both sheets by that key, or use lookup formulas to bring matching records together. Check that the key is unique: duplicate IDs can cause the wrong record to be matched or make results ambiguous. If one sheet has records missing from the other, identify those records separately rather than treating every shifted row as a changed cell.
When columns have moved, compare fields by their headers instead of assuming that column C means the same thing in both sheets. A practical workflow is to make a normalized copy of each dataset with columns in a consistent order, then run the comparison. This takes longer than a direct grid but prevents misleading results when worksheet structure has changed.
Other Excel comparison options
A formula grid is transparent and works in ordinary Excel workbooks, but it is not the only option. Choose a method based on whether you need to compare values, formulas, formatting, or whole workbook structure.
- View Side by Side: Useful for visually inspecting two open workbooks. It does not automatically identify every changed cell, so it is best for a manageable amount of data.
- Spreadsheet Compare: This Microsoft tool can report differences between workbooks in supported Office editions. Availability depends on the installed edition and configuration; if the Inquire tab or Spreadsheet Compare is absent, your Excel license may not include it or the add-in may not be enabled.
- Power Query: Useful for importing, cleaning, and joining larger tables when records need to be matched by keys. It is a data-transformation workflow rather than a one-click visual comparison, and the exact steps depend on the shape of the tables.
- Manual inspection: Reasonable for a small range, especially if the differences involve layout or formatting that a value formula does not check.
For a quick comparison of same-shaped tables, the formula grid is usually the easiest method to explain and verify. For a formal workbook audit, check whether Spreadsheet Compare is available and whether its report covers the properties that matter to you. For reordered or differently structured records, align or join the data before comparing fields.
Common problems and checks
Most surprising results come from assumptions about layout or data type rather than a broken formula. If results look inconsistent, work through these checks before changing the formula:
- Wrong sheet reference: Confirm the spelling of both worksheet names. Sheet names with spaces need single quotes in a worksheet reference.
- Shifted ranges: Check that the top-left formula compares the intended pair and that filling the formula did not use an incorrect starting cell.
- Numbers stored as text: Imported data can contain text that looks numeric. Check the cells and clean or convert the data consistently before interpreting the comparison.
- Blanks and zeros: A blank cell and a cell containing zero are not always distinguishable through a basic equality test. Inspect the source cells if missing data matters.
- Extra spaces or invisible characters: Text that looks alike may contain leading spaces or nonprinting characters. Clean the data consistently before comparing it.
- Displayed values versus stored values: Rounding and number formats can make different underlying values appear identical, or matching values appear different.
Keep a copy of the source workbook before performing bulk cleanup. Cleaning imported values can change the data, and comparison results are only as reliable as the alignment and consistency of the source ranges.
Frequently Asked Questions
Can Excel automatically compare two sheets?
Yes. A formula grid can compare matching cell positions, and conditional formatting can highlight mismatches. Spreadsheet Compare may also be available in some Office editions for workbook-level analysis.
How do I compare two sheets and highlight differences?
Make a result grid with a formula comparing each pair of cells, then apply conditional formatting to the grid with a rule that highlights FALSE. This keeps the original data intact.
Does the equals formula compare formulas or displayed values?
It compares the values Excel evaluates, not the formula text. Cells with different formulas can return equal results. Use formula inspection or a workbook comparison tool when formula changes matter.
Can I compare sheets when the rows are in a different order?
Not reliably by position alone. Match records by a unique identifier or sort and align both datasets first, then compare the fields for corresponding records.
Can I compare two worksheets in different workbooks?
Yes. Open both workbooks and build references to the other workbook’s cells, or use an appropriate comparison tool. External workbook links can depend on file names and locations, so keep the files accessible and verify the references.
Does a cell comparison check color or formatting?
No. A standard equality formula checks evaluated values, not formatting, comments, or other cell properties. Use visual inspection or a workbook comparison feature that explicitly reports the properties you need.
Related
- Circular Cell Reference in Excel: Causes and Fixes
- Circular Reference Settings in Excel: How They Work
- Change the Print Area in Excel: Steps and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Name: Find, Change, and Use Names in FormulasAn Excel table name is the workbook-level identifier assigned to a formatted Excel table, such as Table1 or…
- Excel IF Formula Examples for Everyday Spreadsheet TasksExcel’s IF formula checks whether a condition is true and returns one result if it is, and another…
- How to Add Single Quotes in an Excel Formula CorrectlyIf A2 contains North and you want the result to display as ‘North’, enter this formula in B2:…
- Excel CONCATENATE Formula Examples and UsesIn Excel, join text with =CONCAT(A2," ",B2) or the ampersand operator, as in =A2&" "&B2; if A2 contains…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.