How to Compare Records in Two Excel Sheets with Formulas

Formulas & functions · Updated November 21, 2026

To compare records in two Excel sheets, match each row by a shared, unique ID rather than by its position. First flag IDs that are missing from the other sheet, then compare the fields for each matching ID. In Microsoft Excel for Microsoft 365 on Windows, a few helper columns and formulas make differences easy to find without rearranging either source sheet.

What You’ll Need

  • An Excel workbook containing both sheets. Save a separate copy before making changes if the source data is important.
  • A shared key in both sheets, such as a customer number, order ID, or product code. Ideally, each key appears once per sheet.
  • Matching column headings and a clear idea of which fields should be compared. For example, compare address and status as well as the ID if you need to find changed details.
  • A rough idea of the data size. The formulas below check rows 2 through 5000; extend the ranges if your records go beyond that.

Step-by-Step

  1. Make a working copy and identify the sheets. Keep the original data intact while you compare it. For this example, the first sheet is named Current and the second is named Previous. Each has headers in row 1, IDs in column A, and details in columns B through D. Replace these names and column letters with the ones in your workbook. If a sheet name contains spaces, put single quotation marks around it in a formula, as in 'Previous Data'!A2:A5000.
  2. Check that the two sheets have comparable columns. Confirm that column A represents the same kind of ID in both sheets, and that each field you plan to compare has the same meaning and data type. A date in one sheet and text that looks like a date in the other may display alike but not compare as equal. Do not delete or move rows to try to line them up; records can be in different orders.
  3. Check whether the key is unique. A customer ID or order number should usually identify one record only. Sort or filter column A on each sheet and look for repeated IDs before comparing. If an ID occurs more than once, a match by ID alone cannot reliably tell which row belongs to which. Resolve duplicates, or build a key from multiple fields that together identify a record, such as order ID plus line number.
  4. Add a helper column to find records missing from the other sheet. On Current, enter a heading such as “Presence” in E1. With IDs in Current!A2:A5000 and Previous!A2:A5000, enter this in E2: =IF(SUMPRODUCT(--(Previous!$A$2:$A$5000=$A2))=0,"Missing from Previous","Found"). The formula returns “Missing from Previous” when the ID in A2 does not occur in the Previous sheet; otherwise it returns “Found.” The bounded ranges must cover all relevant rows and be the same size.
  5. Fill the presence formula down. Select E2 and use the fill handle at the cell’s lower-right corner to copy the formula to the last record. Alternatively, copy E2, select the destination cells, and paste. Review the results: filter column E for “Missing from Previous” to see current records with no counterpart. Repeat the check from the other direction by adding a helper column on Previous and changing the formula’s sheet references, so you also find IDs that appear only in Previous.
  6. Compare a field for records with matching IDs. For example, to compare the value in column B, add a heading such as “B comparison” in F1 on Current. With the same ID and data layout, enter this in F2: =IF(SUMPRODUCT(--(Previous!$A$2:$A$5000=$A2))=0,"Missing ID",IF(SUMPRODUCT((Previous!$A$2:$A$5000=$A2)*(Previous!$B$2:$B$5000=B2))>0,"Same","Different")). This returns “Missing ID” if A2 is absent from Previous, “Same” if the ID and B value occur together there, and “Different” if the ID exists but no row has both values. For instance, if ID 1042 has the same status in both sheets, the result is “Same”; if its status changed, it is “Different.”
  7. Copy the comparison across and down. After checking F2, fill it down for all current records. To check another field, copy the formula into a new helper column and change the Previous return column and the Current comparison cell together. For example, for column C, compare Previous!$C$2:$C$5000 with C2. Keep the ID ranges fixed with dollar signs so they do not shift as you fill. A separate result per field makes it clear whether a record’s name, amount, date, or status is the part that changed.
  8. Review the differences and make them easy to inspect. Filter the helper columns for “Different” or “Missing ID,” then check the source rows before deciding what to update. To highlight results, select the helper cells and choose Home > Conditional Formatting > Highlight Cells Rules. You can also add a filter using Data > Filter. These tools help you review the comparison; they do not change or reconcile the underlying records.

For a practical check, suppose Current has ID 1042 and Previous also has ID 1042. The presence result should say “Found.” If column B is “Open” in Current and “Closed” in Previous, the B comparison should say “Different.” If ID 1043 appears only in Current, its presence result should say “Missing from Previous.” These results answer different questions: whether a record exists on both sheets, and whether a particular field agrees.

Common Mistakes

  • Comparing rows by position: Row 10 on one sheet may be a different record from row 10 on the other. Use the shared ID to match records instead.
  • Using a non-unique ID: Repeated keys can produce ambiguous comparisons. Identify duplicates first or choose a key that uniquely identifies each row.
  • Checking only for missing IDs: A record can exist on both sheets while its details differ. Compare every field that matters, not just the key.
  • Comparing inconsistent data: Extra spaces, different date formats, and numbers stored as text can make values appear unequal. Clean or standardize the data before treating a result as a meaningful change.
  • Using ranges that stop too early: If new rows fall below row 5000, the formulas will miss them. Extend both ranges to cover the data, keeping their start and end rows aligned.

Tips

  • Keep the original sheets unchanged and put comparison formulas in helper columns or a separate review sheet. This makes it easier to audit your work and repeat the check later.
  • Use clear helper headings such as “Presence,” “Name comparison,” and “Status comparison.” A reviewer can then tell what each result means without inspecting every formula.
  • For a large workbook, avoid checking hundreds of thousands of blank rows unnecessarily. Use ranges that cover the records, or convert each data range into an Excel table so new rows are easier to include in later comparisons.
  • Excel’s ordinary equality comparisons are not case-sensitive. If “ABC” and “abc” must count as different, use a case-sensitive method or inspect those entries separately.
  • Document the comparison date and the fields checked. This is especially useful when a workbook is refreshed regularly and a later comparison needs to use the same rules.

Troubleshooting

The formula shows “Missing” even though the ID appears on both sheets. Check for leading or trailing spaces, different ID types, or hidden characters. A number stored as text may not match a numeric value. Compare the cells’ formats and contents, then standardize the source values before rerunning the check. If IDs were imported, re-import or clean them consistently rather than changing only one side.

Every field says “Different.” Confirm that the formula compares the correct columns and that the ID ranges refer to the intended sheet. Verify that the ranges include all rows and have matching dimensions. If values look identical, inspect the underlying values for spaces, text-versus-number differences, or date-time values that include a hidden time component.

The formula returns an error or does not calculate. Check that the sheet tab is actually named Previous and that the formula’s parentheses and range references are intact. Excel may use semicolons instead of commas as formula separators under some regional settings; if Excel rejects the comma-separated formula, use the separator expected by your installation. Also confirm that calculation is enabled under Formulas > Calculation Options > Automatic.

Results seem wrong when IDs repeat. The method assumes each key identifies one record. If duplicates are legitimate, compare a unique combination of fields or add a sequence/line identifier, then rerun the check with that composite key. Do not assume that the first matching duplicate is the correct one.

The workbook becomes slow. Large SUMPRODUCT ranges recalculate repeatedly. Keep ranges to the used rows, reduce unnecessary helper formulas, or split the comparison into manageable batches. If the files are very large or need repeated reconciliation, Power Query may be a better fit than worksheet formulas.

Frequently Asked Questions

Can I compare two sheets if the rows are in a different order?

Yes. The formulas match records by the value in the ID column, so row order does not matter. Make sure the ID is consistent and unique in both sheets.

How do I find records that appear in only one sheet?

Use the presence helper column in both directions. First flag IDs in Current that are absent from Previous, then run the corresponding check on Previous against Current. Filtering for the missing result reveals the unmatched records.

Can I compare an entire row at once?

You can compare each important field in its own helper column, which makes the exact difference visible. A single combined result is possible, but it is less informative and can be harder to troubleshoot, especially when fields contain blanks or inconsistent formats.

Does this work in Excel for Mac or Excel for the web?

The formulas use standard Excel functions available in Microsoft 365, including Excel for Mac and Excel for the web. Ribbon labels or their locations can differ slightly by platform, but the matching logic is the same.

What if there is no shared ID?

Choose a combination of columns that reliably identifies the same record in both sheets, such as last name plus date of birth or invoice number plus line number. If no combination can distinguish records reliably, resolve that ambiguity before relying on the comparison.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.