How to Compare Two Excel Sheets: Same Workbook, Three-Sheet Method

Why sheets are easier than files

The practical consequence: if your two sets of data are in separate files and you are doing anything more than a one-off visual check, copy them into one workbook first. You lose nothing and you gain the ability to use formatting and simple references.

Option 1: view them side by side

For a quick visual check on a small amount of data, Excel can show the same workbook in two windows with a different sheet in each.

1. 1Go to View → New Window. This opens a second window onto the same workbook.
2. 2Use View → Arrange All → Vertical to place the two windows side by side.
3. 3In each window, select the sheet you want to look at.
4. 4Turn on View → Side by Side → Synchronous Scrolling if you want both to scroll together.

Option 2: the three-sheet comparison — the method worth reusing

Leave your two source sheets untouched and build the comparison in a third sheet. Never compare the two source sheets directly to each other with ad-hoc formulas scattered across both — the logic disappears the moment you close the file.

Step 1: build the key list on the Compare sheet

The Compare sheet needs one row per key. Copy the key column from Sheet1, then add any keys that are on Sheet2 but not Sheet1. The simplest way is to copy both key columns into a single column, one below the other, and use Data → Remove Duplicates.

Before you do, normalise the key using the same formula on both source sheets — strip spaces, force upper case, remove separators:

=UPPER(TRIM(SUBSTITUTE(A2,"/","")))

This is the step people skip, and it is the reason most comparisons report a long list of false differences.

Step 2: test each key on both sides

That status column is the whole comparison, reduced to one phrase per row. Filter it and you have your three lists: records in both, records only in the register, records only in the export.

Step 3: compare the values where the key is present on both sides

Step 4: total both sides

At the bottom of the Compare sheet, total the value columns from each side and the difference column. The total of the difference column must equal the gap between the two source totals. If it does not, some row is not being captured by your key list — usually a key that exists on one side but was never copied in.

Option 3: conditional formatting for the visual pass

Once both sheets are in one workbook, formatting becomes available. Two rules do most of the work.

Use two clearly different fill colours — one for missing, one for different. A single colour for both conditions forces you to read every highlighted cell to know which problem you have.

Comparing more than two sheets

Comparing each pair of sheets in turn does not scale. Designate one sheet as the master key list and add a column group per source sheet.

One row per key, one column group per source. Adding a fourth file means adding a column pair, and the status column can be written once as a nested IF or a SWITCH.

The traps specific to sheet comparisons

• Hidden or filtered rows. A filtered sheet shows fewer rows than it contains, and a copy-paste of a filtered range copies only the visible rows. If you built the key list from a filtered view, your comparison is missing records and nothing warns you.
• Numbers stored as text. Common after a paste from a PDF, a web page or a bank export. Both key columns must be the same type — the quickest test is to check whether the values sit right-aligned (numbers) or left-aligned (text) in their cells.
• Non-breaking spaces. A copied value can carry a character that TRIM does not remove. If two values look identical and will not match, wrap the key in CLEAN() as well.
• Leading zeros lost. An employee code of 00417 becomes 417 when a sheet is formatted as a number or exported to CSV. The two sides then never match. Format both key columns as text before importing.
• Merged cells. A merged cell holds its value in the top-left cell only, so a formula reading the merged range sees blanks in every other row. Unmerge before comparing.
• Sheet names with spaces. Any reference to a sheet whose name contains a space needs single quotes — 'Sheet 1'!A1. Without them the formula errors, and people often “fix” it by retyping the reference and changing the meaning.
• 3-D references. A reference such as Sheet1:Sheet3!D10 reads through every sheet between the two named ones in tab order. It is a neat trick for totalling identical sheets and a serious hazard in a comparison, because dragging a sheet tab changes what the formula reads.

When the sheet comparison outgrows the sheet

The three-sheet pattern above is the right answer for most work, and it is entirely within reach of anyone comfortable with COUNTIF and XLOOKUP. Two things push people past it: volume, where formulas repeated down fifty thousand rows make the file slow to open and slow to recalculate, and repetition, where the same comparison has to be rebuilt every month because the sheet names or the column positions changed.

Power Query handles the repetition well if you are comfortable with it. If you would rather the comparison simply be a saved set of rules — this key column, this tolerance, this is what counts as a difference — then it belongs in a tool rather than a worksheet. The Piloteq Automate comparison page shows what that returns. If you are comparing two separate files rather than two sheets, start with compare two Excel files.