Conditional Formatting to Compare Two Sheets in Excel

Yes. In Excel for Microsoft 365, you can highlight differences between two sheets with a conditional-formatting formula that uses INDIRECT. If Sheet1 and Sheet2 store corresponding values in the same cells, select the comparison range on Sheet1 and use this rule; for cells A2:A100, the formula is =AND(A2<>"",A2<>INDIRECT("'Sheet2'!A"&ROW())). The rule colors nonblank cells that differ from the value in the same row of Sheet2.

What “compare two sheets” means

Conditional formatting changes a cell’s appearance when a condition is true. For a sheet comparison, the condition is usually that a value on one worksheet differs from its counterpart on another. This is useful for reviewing revised price lists, checking two versions of a report, or spotting entries that changed between monthly exports.

The important question is what counts as a counterpart. If both sheets have the same records in the same order, comparing A2 with A2, B2 with B2, and so on is straightforward. If one sheet has been sorted differently, or rows have been added or removed, matching by row number can flag the wrong differences. In that situation, match records using a unique identifier such as an employee ID or product code instead.

Conditional formatting only marks cells; it does not change, reconcile, or copy values. It is a visual review aid. For a formal audit, keep the original sheets and consider adding a separate comparison or status column so the reason for each highlight is explicit.

Set up a same-position comparison

For a simple comparison, first confirm that both worksheets use the same layout and that row 2 represents the same record on each sheet. On Windows in Excel for Microsoft 365, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose a fill or font style with Format, and confirm the rule.

Open Sheet1 and select A2:A100, or the range you want to check.
Open the New Rule dialog and choose the formula-based rule.
Enter the formula shown in the opening paragraph, replacing the sheet name or column if your workbook differs.
Choose a noticeable format, such as a pale red fill, and select OK.
Review the rule’s Applies to range to make sure it covers the intended cells.

The formula assumes values are in column A on both sheets and that row numbers align. The reference to A2 is relative, so Excel adjusts it for each cell in the selected range. ROW() supplies the current row number, and INDIRECT turns the constructed sheet-and-cell address into a reference. The AND condition skips empty cells on Sheet1, avoiding highlights for unused rows. Remove that condition if blank-versus-nonblank differences should also be marked.

On Excel for Mac, the same general rule is available from the Home tab’s Conditional Formatting controls, though dialog layouts can differ by release. Excel for the web has conditional-formatting controls too, but the interface and available rule-editing options may vary; use desktop Excel if you cannot create or adjust the formula rule there.

Compare a whole matching table

If corresponding tables occupy the same cell positions, a single rule can be applied across the table. For example, assume both sheets contain matching records in A2:D100. Select Sheet1!A2:D100 and create a formula rule using this formula:

With the same table layout on Sheet1 and Sheet2, and the selected range beginning at A2, use =AND(A2<>"",A2<>INDIRECT("'Sheet2'!"&ADDRESS(ROW(),COLUMN(),1))).

Here, ADDRESS builds the address for the current row and column, so the cell in Sheet1 is compared with the same-position cell in Sheet2. Because the formula begins with A2, Excel adjusts its references across the applied range. This highlights individual differing cells, rather than coloring an entire row because one field changed.

To highlight all cells that differ, including cases where Sheet1 is blank and Sheet2 is not, omit the blank test. Assuming the same A2:D100 layout, use =A2<>INDIRECT("'Sheet2'!"&ADDRESS(ROW(),COLUMN(),1)). Test the result on a small sample first: whether blanks, formulas returning empty text, and values displayed with different number formats should count as differences depends on the task.

Use a helper column for a more maintainable rule

INDIRECT is convenient in a conditional-formatting formula, but it can be harder to inspect and is volatile, meaning Excel recalculates it more often than many ordinary functions. A helper column is often easier to troubleshoot. Unlike a conditional-formatting rule, a normal worksheet formula can directly refer to another sheet.

Assume Sheet1 and Sheet2 have corresponding values in A2:A100. In Sheet1, enter this formula in B2, then fill it down through B100:

With the values being compared in A2 on each sheet, enter =A2<>Sheet2!A2 in Sheet1!B2. The result is TRUE when the two cells differ and FALSE when they match.

Next, select Sheet1!A2:A100 and create a formula-based conditional-formatting rule. With the TRUE/FALSE comparison flags in B2:B100, use =$B2=TRUE. Apply the desired format. The dollar sign fixes the helper column while the row reference changes for each item. You can hide column B after checking the results, or leave it visible as an audit trail. If formatting every field in a table, create helper comparisons for the relevant columns or use the whole-table formula method above.

Match records by an ID, not by row number

When rows can move, a same-row comparison is not reliable. A product may be on row 12 in one sheet and row 18 in another; comparing those positions would report a mismatch even if the product’s data is unchanged. Use a stable unique key, such as a product ID, to identify the same record before comparing its other fields.

One practical approach is to add helper columns that retrieve the matching value by ID, then compare the retrieved value with the current sheet’s value. This example uses XLOOKUP, available in Microsoft 365 and Excel 2021 or later. Assume Sheet1 has IDs in A2:A100 and prices in B2:B100; Sheet2 has IDs in A2:A100 and corresponding prices in B2:B100. Enter the lookup formula in Sheet1!C2 and fill down:

With the ID in Sheet1!A2 and the lookup table on Sheet2, use =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found"). It returns the Sheet2 price for that ID, or the text Not found if the ID is absent.

Then compare B2 with C2 in another helper column and format the rows where the values differ. Check missing IDs separately: a missing record is not the same as a record with a changed price. Also confirm that the key is genuinely unique. If an ID appears more than once, a lookup may return one matching record without revealing duplicates, so add a duplicate check or clean the data before drawing conclusions.

What Excel considers a difference

A basic comparison checks the underlying cell values, not necessarily what a person sees on screen. Two dates can display differently because of number formats; conversely, different underlying values can appear identical when rounded or formatted with limited decimal places. If the displayed result is what matters, compare appropriately rounded values or create a helper formula that represents the intended comparison.

Text capitalization: Standard equality comparisons in Excel are generally not case-sensitive. If capitalization matters, use a case-sensitive check in a worksheet helper formula, such as EXACT.
Spaces: Leading or trailing spaces can make text values differ even when they look similar. A helper formula using TRIM can remove ordinary extra spaces before comparison, but it does not remove every kind of invisible character.
Numbers stored as text: A value imported as text may behave differently from a numeric value. Check the cell type and normalize the data if the comparison should treat them alike.
Formula results: Two cells may contain different formulas but return the same result. A value comparison checks the result, not the formula text.
Errors: If either compared cell contains an error, the comparison may itself produce an error rather than a useful TRUE/FALSE result. Handle errors in a helper formula if they are expected in the data.

For example, a price displayed as $10.00 might actually contain 10.004 on one sheet and 10.00 on the other. A direct comparison sees unequal values even though both display as $10.00. Decide whether the comparison should be exact or should use a tolerance or rounding rule, and document that choice so reviewers understand what a highlight means.

Common problems and how to fix them

Excel rejects a direct cross-sheet reference: Conditional-formatting formulas generally cannot use a direct reference to another worksheet in the way an ordinary cell formula can. Use the INDIRECT approach for the rule, or calculate a comparison in a helper cell and format from that result.
Every cell highlights, or none do: Check that the formula is written relative to the top-left cell of the Applies to range. If the selected range begins at A2, the formula should normally begin with A2, not A1 or a reference fixed to the wrong row.
The wrong records are flagged: Confirm that rows correspond. If sheets have been sorted or edited independently, match by a unique ID rather than position.
Sheet names with spaces: In a text-built reference, keep the sheet name inside single quotes, as in 'Sales 2026'!A2. If a sheet name contains an apostrophe, it needs special escaping inside the text string; a helper-column formula may be simpler to maintain.
The rule breaks after renaming a sheet: A sheet name embedded as text inside INDIRECT may not update like an ordinary cell reference. Edit the rule after renaming the sheet, or use a helper formula that refers to the sheet directly.
The workbook feels slower: Large ranges with many INDIRECT rules can add recalculation work. Narrow the Applies to range, reduce repeated rules, or use helper formulas.

To inspect a rule in Excel for Windows, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. Check the rule formula, its range, and its order if multiple rules apply. A rule with a different format or a stop-if-true setting can affect what you see.

When to use another comparison method

Conditional formatting is a good choice when the goal is to scan a sheet and spot exceptions quickly. A helper-column comparison is better when reviewers need a clear TRUE/FALSE result, when records are matched by key, or when a complex rule needs to be explained and maintained. For a large recurring reconciliation, Power Query can combine tables by a key and expose missing or changed records in a refreshable workflow; it is more setup than a visual rule, but avoids relying on row positions.

For a one-time comparison, formulas such as XLOOKUP or COUNTIF in helper columns can make unmatched records visible and easy to filter. If you only need to find a handful of differences between two static versions, copying values to a separate comparison workbook or using Excel’s review tools may be enough. Choose the method based on whether you need a quick visual cue, a repeatable process, or a detailed record of every discrepancy.

Frequently Asked Questions

Can conditional formatting directly compare cells on two Excel sheets?
Usually not with an ordinary direct cross-sheet reference in the conditional-formatting formula. Use INDIRECT to construct a reference, or compare the cells in a helper column and format based on that result.

Can I compare two sheets when their rows are in a different order?
Yes, but do not compare the same row numbers. Match records using a unique ID, retrieve the corresponding value with a lookup such as XLOOKUP, and compare the matched values.

How do I highlight values that are missing from the other sheet?
Use a key-based check, not a same-position comparison. A helper formula using COUNTIF can test whether each ID from one sheet appears on the other; format IDs whose count is zero, while handling blank IDs separately.

Does the rule compare displayed values or the underlying values?
A standard comparison checks cell values or formula results, not their visual formatting. Number formats can make unequal values look the same, so use rounding or another helper calculation if displayed precision is the intended standard.

Will the conditional formatting update when I edit a value?
Yes. Excel recalculates the rule as relevant cells change. If the formula contains a sheet name as text inside INDIRECT, however, review the rule if you rename that sheet.

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.