To compare two Excel sheets for missing data, match records using a shared key—such as an ID or email address—and check whether each key appears in the other sheet. For missing cells within records that already match, compare corresponding fields separately; the right method depends on whether you mean a missing record, a blank value, or both.
What “missing data” means in a comparison
Two sheets can disagree in several ways. A record may appear in one sheet but not the other, such as a customer ID present in the latest export but absent from last month’s file. Or both sheets may contain the same customer, while a field such as phone number is filled in one and blank in the other. These are different checks and should not be confused.
There is also a difference between a truly empty cell and a value that looks empty. A cell might contain a formula returning an empty string, spaces, or a placeholder such as “N/A.” A basic blank check can treat a formula returning an empty string as blank, but it will not treat spaces or the text “N/A” as blank unless you explicitly account for them.
Before comparing, decide what counts as a match. A unique ID is usually safer than a person’s name, which may be misspelled, duplicated, or formatted differently. If no reliable shared key exists, a comparison may need several fields together or manual review; matching rows by their position is only sound when both sheets have the same row order and structure.
Prepare the sheets before comparing
Save a copy of the workbook before making substantial changes. Then check that the two sheets use the same kind of identifier and that the relevant columns contain consistent values. For example, the number 123 and the text value “123” may not behave alike in every comparison, and an ID with a leading zero can be changed if Excel treats it as a number.
- Give each sheet a clear name, such as Current and Archive. If a sheet name contains spaces, Excel references it with single quotes, as in
'Prior Export'!A:A. - Identify the key column and confirm whether it should be unique. Duplicate keys can make a simple “found or missing” test look successful even when the wrong record is matched.
- Check for extra spaces, inconsistent capitalization, different date formats, and blank key cells. Clean or document these differences before interpreting results.
- Keep a copy of the original data. Put comparison results in a helper column or a separate sheet so they can be filtered and reviewed without overwriting source values.
The examples below use Microsoft Excel for Microsoft 365 on Windows. Formulas can also be entered in Excel for Mac. Menu locations and Power Query availability vary by platform, and Excel for the web does not provide the same full Power Query authoring experience as desktop Excel.
Find records that are absent from the other sheet
For a straightforward list comparison, use a helper formula beside the first sheet’s IDs. This tests whether each ID appears anywhere in the second sheet, rather than assuming that the records are in the same row order.
Assume IDs are in Current!A2:A100 and Archive!A2:A100, with the first ID in Current!A2 and the result going in Current!B2. Enter this formula in B2 and fill it down:
=IF(A2="","",IF(ISNUMBER(XMATCH(A2,Archive!$A:$A,0)),"Found","Missing from Archive"))
For example, if Current!A2 contains C104 and that ID does not occur in Archive column A, the result is “Missing from Archive.” If it does occur, the result is “Found.” The outer check leaves the result blank when the current row has no ID, avoiding a misleading missing-record label for an empty row. XMATCH with a match mode of 0 looks for an exact match; XMATCH is available in Microsoft 365 and Excel 2021 or later.
To find records that exist in Archive but not Current, place the same check on the Archive sheet and reverse the lookup. Assume the ID to check is in Archive!A2 and the comparison column is Current!A:A; enter this in Archive!B2 and fill down:
=IF(A2="","",IF(ISNUMBER(XMATCH(A2,Current!$A:$A,0)),"Found","Missing from Current"))
A two-way check matters because a one-way comparison only answers whether one list contains IDs missing from the other. Once both helper columns are filled, filter each for “Missing.” You can then review the unmatched records without changing the original lists.
Compare blank fields in records that match
When the sheets have the same records in the same row order, corresponding cells can be checked directly. This is a limited shortcut: it does not establish that the rows represent the same record. If order can differ, first align records by a key or use a lookup to bring the corresponding value onto the same row.
Assume Current!A2 contains a phone number and Archive!B2 contains the corresponding phone number for the same person. Put the result in Current!C2:
=IF(AND(A2="",B2<>""),"Missing in Current",IF(AND(A2<>"",B2=""),"Missing in Archive",""))
If Current!A2 is blank and Archive!B2 contains 555-0104, the result is “Missing in Current.” If both cells are blank, or both contain something, the result is blank. This formula flags a one-sided blank; it does not report when both values are filled but different. That is a separate comparison, and it should not be labelled missing data.
For workbooks with many fields, decide which columns are meaningful to compare and repeat the check for those fields, or build a structured comparison table. Avoid comparing every cell indiscriminately if the sheets include notes, formulas, timestamps, or fields that are expected to change independently.
Highlight missing cells with conditional formatting
Conditional formatting is useful when two columns are already aligned and you want to see gaps without adding a status column. For example, select Current!A2:A100, where Current values are in column A and their corresponding Archive values are in column B. In Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula below, choose a format, and confirm:
=AND(A2="",B2<>"")
The relative row references adjust as Excel applies the rule down the selected range. It highlights a blank in Current column A only when the corresponding Archive cell in column B is not blank. Create a second rule on B2:B100 using =AND(B2="",A2<>"") to highlight values missing from Archive. Conditional formatting changes the display, not the underlying cell contents, so it is not a substitute for a saved comparison result when you need an auditable list.
On Mac, Conditional Formatting is also available from the Home tab, though the dialog layout can differ. Excel for the web has conditional formatting tools, but options and rule-editing details may differ from desktop Excel.
Use Power Query for larger or repeat comparisons
Power Query can be more convenient when files contain many rows or the comparison must be repeated after new exports arrive. It imports and transforms data, and a merge can identify rows that have no matching key in the other query. Unlike a formula copied into a worksheet, a query can be refreshed after the source data changes.
In Excel for Microsoft 365 on Windows, start with Data > Get Data > From File > From Workbook to import each workbook, or use the relevant import option if both sheets are in the current file. In the Power Query Editor, check that the key columns have compatible data types. Then use Home > Merge Queries, select the key column in each table, and choose a Left Anti join to return rows from the first table that have no match in the second. Reverse the table order and repeat for records missing in the opposite direction.
Power Query is especially helpful for repeatable workflows, but it still depends on a sound key. Review duplicate IDs and data types before trusting the output. A merge based on a non-unique key can produce confusing results, and a key stored as text in one table but as a number in the other may fail to match as expected. Power Query features and import options can vary across Excel for Mac releases; for a one-off check on Mac, formulas may be simpler. Excel for the web may let you view or refresh some queries, but desktop Windows is the clearest choice for the full set of authoring steps described here.
Choose a method that fits the job
The best approach depends on the size, shape, and repeatability of the comparison. A formula is transparent and easy to inspect for modest lists. Conditional formatting is quick for aligned ranges, but it depends on row-by-row correspondence. Power Query is a stronger fit for larger, recurring imports, especially when the goal is a refreshable list of unmatched records.
- Use XMATCH in a helper column when you need to check whether each key appears in the other list and want a filterable result.
- Use a direct blank-check formula or conditional formatting when the records are already aligned and you are checking particular fields.
- Use Power Query anti-joins when you compare large tables, repeat the process, or need unmatched rows as a separate query result.
- Use manual review when there is no trustworthy key or when a suspected match needs human judgment, such as names with spelling variations.
For a one-time list comparison, there is usually no need to build a complex workbook. For a monthly process, documenting the key, cleaning rules, and direction of each comparison can prevent a recurring source of errors.
Common problems and how to avoid them
Comparing row numbers instead of records: Sorting one sheet changes which records sit beside each other. A direct A2-versus-B2 check can then report false differences. Use a shared key to align records first.
Duplicate or blank keys: A duplicate key means a “found” result only proves that at least one occurrence exists, not that each duplicate has a unique partner. Blank keys should generally be reviewed or excluded rather than treated as ordinary identifiers.
Spaces and inconsistent values: “C104” and “C104 ” can look alike but differ. Text and numeric IDs can also differ, and leading zeros may be significant. Standardize values carefully; do not remove zeros or alter identifiers unless the data owner confirms they are not meaningful.
Misreading a blank check: A formula returning an empty string may appear blank and satisfy a check such as A2="". A cell containing spaces does not. If spaces should count as blank, clean them or use an explicitly designed test, and verify the result on representative rows before applying it to the entire dataset.
Assuming “found” means “equal”: A key lookup only confirms that the key appears. It does not prove that the other columns match. Check required fields separately, and distinguish missing values from changed values in your labels and reports.
Frequently Asked Questions
How do I find values in one Excel sheet but not another?
Put a lookup formula beside the first list to test whether each key appears in the second, then filter for missing results. Repeat with the lists reversed if you need to find missing values in both directions.
Can I compare two sheets when the rows are in a different order?
Yes. Compare using a shared key, such as an ID, rather than comparing cells at the same row number. A lookup formula or a Power Query merge can find matching and unmatched records even when the order differs.
How do I highlight blanks in one sheet when the other sheet has a value?
If the rows are already aligned, use a conditional formatting formula such as =AND(A2="",B2<>"") on the first range. For lists in a different order, align matching records by key before comparing cells.
Does XMATCH work in older Excel versions?
XMATCH is available in Microsoft 365 and Excel 2021 or later. In an older version, you can use a compatible alternative such as MATCH with exact-match mode, or use Power Query if that version supports the needed import and merge features.
What if the same ID appears more than once?
A basic existence check does not count or pair duplicate occurrences; it only tests whether a match exists. Review duplicates first, or compare a genuinely unique key or a combination of fields that identifies each record.
Related
- Can You Hide a Column in Excel From View?
- Location Heat Map in Excel: Create One Step by Step
- Compare Two Excel Sheets for Matches and Differences
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- XLOOKUP Name Error: Causes, Checks, and Fixes in ExcelIf XLOOKUP returns #NAME?, Excel usually does not recognize something in the formula: the function name may be…
- Excel Date Subtraction #VALUE! Error: Causes and FixesA #VALUE! error when subtracting dates in Excel usually means that at least one cell is text Excel…
- Outlook 365 Not Working Today? Check Status and FixesOutlook 365 may be affected by a Microsoft service incident, but I can’t verify live outage conditions from…
- Excel Filter Not Working? Common Causes and Fixes GuideIf a filter in Excel is not working, first check that it covers the entire data range, that…
- Unhide Rows in Excel Not Working? Fix the Hidden RowsIf Unhide does nothing in Excel, select the row headers on both sides of the missing rows, right-click…
- Excel Print Area Not Working? Common FixesIf Excel is printing the wrong cells or ignoring a print area, select the intended range and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.