To compare values in the same positions on two Excel sheets, enter a formula such as =IF(Sheet1!A2=Sheet2!A2,"Match","Different") in a results column and fill it down. If the records may be in a different order, compare a unique key instead; matching rows by position would give misleading results.
Choose what “compare” means
Before choosing a formula, decide whether the sheets have the same layout and whether you want to compare individual cells, whole rows, or records that may have moved. These are different tasks. A row-by-row test checks whether corresponding cells agree. A key-based test asks whether a record on one sheet exists on the other, regardless of its row number. A third option is to compare a whole table and report differences in several columns.
For example, suppose one sheet contains last month’s product list and another contains this month’s. If both lists are sorted identically, comparing cell A2 with cell A2 on the other sheet may be appropriate. If products were added, deleted, or sorted differently, compare product IDs instead. A formula can be perfectly valid and still answer the wrong question if the comparison method does not fit the data.
Compare cells in corresponding rows
For a simple position-by-position check in Excel for Microsoft 365 on Windows, add a results column beside the data. Assume the worksheets are named Sheet1 and Sheet2, the values to compare are in column A, and row 2 is the first data row. Enter this formula in a results cell on Sheet1:
=IF(Sheet1!A2=Sheet2!A2,"Match","Different")
If both cells contain 125, the result is “Match”; if Sheet1!A2 is 125 and Sheet2!A2 is 130, it is “Different.” Fill the formula down using the fill handle at the lower-right corner of the cell. The relative row references change as the formula is copied, so the next result compares row 3 on each sheet. You can also use the simpler comparison =Sheet1!A2=Sheet2!A2 in the results column; it returns TRUE or FALSE rather than a readable label.
When sheet names contain spaces, enclose each name in single quotation marks. For example, with data in A2 on worksheets named January Data and February Data, the same-row comparison is:
=IF('January Data'!A2='February Data'!A2,"Match","Different")
Excel usually adds the quotation marks automatically when you select a cell on a sheet with a space in its name. If your workbook uses different sheet names or columns, adjust the references before filling the formula down.
Compare whole rows across several columns
When a record is made up of several fields, checking just one column can miss a change elsewhere. Suppose the sheets have corresponding records in columns A through C, with a name, region, and quantity in those columns, and the first record is on row 2. To label a row as a match only when all three fields agree, use:
=IF(AND(Sheet1!A2=Sheet2!A2,Sheet1!B2=Sheet2!B2,Sheet1!C2=Sheet2!C2),"Match","Different")
For instance, if the name and region are unchanged but the quantity differs, the result is “Different.” Add another comparison inside AND for each additional field that matters. This method is transparent, but a long formula can become awkward when a table has many columns. In that situation, use helper columns for important fields or consider Power Query for a repeatable table comparison.
Find records that are missing or reordered
When records can appear on different rows, use a stable identifier such as an order number or product ID. Assume Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, and enter the following formula beside the first ID on Sheet1:
=IF(COUNTIF(Sheet2!$A:$A,A2)=0,"Missing from Sheet2","Found")
If A2 contains ID P104 and that ID appears anywhere in column A on Sheet2, the result is “Found”; if it does not, the result is “Missing from Sheet2.” Copy the formula down to check the other IDs. The dollar signs keep the search range fixed as the formula moves. This checks whether a value exists, not whether the complete record matches, so compare the other fields after finding the corresponding ID.
To identify IDs that appear only on Sheet2, put the equivalent check beside the IDs on Sheet2 and change the searched sheet to Sheet1. Check that the chosen ID is genuinely unique: if the same ID appears more than once, a presence check will still say “Found” and will not reveal duplicate counts or determine which duplicate record corresponds to which one. Exclude blank IDs from the check, since empty cells are not useful identifiers and can produce confusing results.
Understand case, spaces, blanks, and number formats
Ordinary equality comparisons in Excel do not distinguish uppercase from lowercase text. Thus, “West” and “WEST” compare as equal with the standard equality test. If capitalization itself matters, and the text to compare is in A2 on Sheet1 and A2 on Sheet2, use:
=IF(EXACT(Sheet1!A2,Sheet2!A2),"Match","Different")
EXACT is case-sensitive, so “West” and “WEST” return “Different.” Spaces are another common source of apparent mismatches. A value with a trailing space may look the same on screen but not compare as expected. With text in A2 and B2 on the same sheet, this test ignores ordinary leading and trailing spaces:
=TRIM(A2)=TRIM(B2)
TRIM does not remove every unusual whitespace character, such as some nonbreaking spaces copied from websites. It also changes repeated internal spaces to a single space. Use it only if those differences should be ignored; otherwise, clean the source data deliberately and retain a way to inspect what changed.
Formatting is not the same as value. A cell may display a date or a currency amount because of its number format, while the underlying content is stored as a number. Two cells can have equal underlying values but look different because their formats differ; conversely, rounded display can make different stored values look identical. For a numeric comparison that should allow a small difference, assume the amounts are in A2 and B2 on the same sheet and that differences below 0.01 are acceptable:
=IF(ABS(A2-B2)<0.01,"Close enough","Different")
This returns “Close enough” when the absolute difference is less than 0.01, not when it is exactly 0.01. Choose a tolerance that fits the data; do not use one just to hide meaningful discrepancies. The comparison expects numeric values, so check for text-formatted numbers or blanks before relying on it.
Make differences easier to see
A results column is often the clearest visual check: filter it to “Different” or “Missing from Sheet2” to focus on exceptions. You can also apply conditional formatting to that results column. Select the cells containing the labels, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter “Different.” Repeat for missing-record labels if useful. The exact wording in the dialog can vary slightly by Excel build, but the Conditional Formatting controls are on the Home tab in Microsoft 365 for Windows.
For a quick manual inspection, place the sheets side by side using View > View Side by Side, then scroll through corresponding rows. This is useful for small tables or checking context around a mismatch, but it is not a substitute for a formula when you need a dependable audit across many rows. Avoid assuming that matching colors or displayed text prove that the underlying values are identical.
Use Power Query for recurring or larger comparisons
For repeated comparisons of larger tables, Power Query can load the two datasets, match them using one or more key columns, and produce a result that can be refreshed when the source data changes. In Excel for Microsoft 365 on Windows, select a source table and use Data > From Table/Range to open it in the Power Query Editor. In the editor, use Home > Merge Queries, select the other query, and choose the matching key column in each table. The join type determines whether the output includes matches, records present on only one side, or both.
Power Query is especially useful when a comparison needs several cleanup steps, such as setting consistent data types, removing unwanted columns, or trimming text before matching. It is less convenient than a cell formula for a one-time check of a few values, and it requires care when choosing keys: duplicate keys can create multiple joined rows. After changing the source data, refresh the query to update its output. Menu availability and connectors can vary with Excel edition and organizational settings; Excel for the web does not always provide the same Power Query authoring experience as the Windows desktop app.
Common problems and a sensible workflow
A mismatch does not always mean the source information is wrong. It may indicate that rows are out of order, a key is duplicated, a value contains an invisible space, a number is stored as text, or a formula result differs from its displayed rounding. Investigate the original cells before replacing data. If formulas refer to another workbook rather than another sheet in the same workbook, the external link may also depend on that file being available and its link being updated.
A careful workflow is straightforward:
- Keep an untouched copy of the source workbook before cleaning or changing values.
- Confirm that the sheets have the same row order, or identify a reliable unique key.
- Choose whether the comparison should include case, spaces, formatting, and small numeric differences.
- Put results in a separate column and inspect the exceptions rather than overwriting source values.
- Check a few known matches and known differences manually before trusting a large result.
This process also helps separate two questions that are often confused: “Are these cells equal?” and “Does this record exist in both datasets?” Use same-row equality for the first; use a key-based lookup or merge for the second.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Yes. A formula can compare corresponding cells or check whether keys occur on the other sheet. For a recurring comparison, Power Query can merge tables and refresh the result. The right choice depends on whether rows line up and how often the check must be repeated.
How do I compare two columns and show only differences?
Compare the corresponding cells in a helper column, fill the formula down, then filter that column for “Different.” This works when each row in one column corresponds to the same row in the other. If items may be reordered, match them by a unique ID instead.
Why does Excel say two values are different when they look the same?
Check for leading or trailing spaces, hidden characters, different underlying numeric values, or a number stored as text. Also consider whether the cells only look equal because their number formats round or otherwise change how values display.
Does a normal Excel comparison distinguish uppercase and lowercase?
No. Standard equality comparisons are not case-sensitive. Use the EXACT function when a capitalization difference must count as a mismatch.
Can I compare sheets with different row orders?
Yes. Do not compare row 2 with row 2 simply because both are on row 2. Use a unique identifier to find corresponding records, then compare the fields you care about. For large or repeated jobs, a Power Query merge can make that process easier to refresh.
Related
- Enum in Programming: Meaning, Uses, and Examples
- Java Enum Values: Meaning, Methods, and Examples
- What Is ENUM in SQL? How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Calendar Week Formula: How to Use WEEKNUMThe core Excel formula for a calendar week number is =WEEKNUM(date), which returns the week number (1–53) for…
- Paste Values in Excel: Keep Results, Remove FormulasTo get paste values in Excel, copy the cells, select where the results should go, then choose Home…
- Excel DATEDIF Function: Calculate Years, Months, and DaysMicrosoft Excel’s DATEDIF function calculates the difference between two dates in complete years, months, or days, or in…
- VLOOKUP in Excel Data Validation Lists: How It WorksVLOOKUP can help create a dependent drop-down list in Excel, but it cannot return a list of cells…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
- Excel Power Query vs Formulas: Which Should You Use?Use Excel formulas when you want calculations to update as you edit worksheet cells; use Power Query when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.