How to Compare Two Excel Sheets to Find Missing Data

Trying to spot the differences between two massive Excel sheets can feel like an impossible game of find-the-difference. Whether you're comparing a new customer list against an old one, checking updated inventory records, or verifying sales data, manually sifting through rows is slow, tedious, and prone to error. This guide will walk you through several easy-to-follow methods to compare sheets and find missing data quickly, from simple formulas to more advanced, automated techniques.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Before You Begin: The Importance of a Unique Identifier

For any comparison method to work, your two sheets must have at least one column in common that contains unique data for each row. This is often called a unique identifier or a primary key. Without it, Excel has no reliable way to match a row from one sheet to a specific row on the other.

Common examples of unique identifiers include:

• Email Addresses
• Customer IDs
• Product SKUs
• Order Numbers
• Transaction IDs

Before you start, make sure this common column exists in both of your sheets and that the data is formatted consistently (e.g., both are formatted as text or numbers). An ID like "AB-123" is not the same as "AB123" to Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Use Conditional Formatting to Visually Highlight Differences

Conditional formatting is one of the quickest ways to visually identify data in one list that is missing from another. It colors the cells based on a rule you create, making the unique entries pop out instantly. This method is great for smaller datasets where you want a fast, visual check.

Let's say you have two worksheets named Sheet1 and Sheet2. You want to find out which customer IDs from Sheet1 are missing from Sheet2.

Step-by-Step Instructions:

1. Select Your Data: In Sheet1, click and drag to select the entire column of unique identifiers you want to check (e.g., select all values in Column A).
2. Open Conditional Formatting: On the Home tab of the ribbon, click Conditional Formatting > New Rule.
3. Choose a Rule Type: In the "New Formatting Rule" dialog box, select the last option: Use a formula to determine which cells to format.
4. Enter the Formula: In the formula bar, you'll use a COUNTIF formula. This formula counts how many times a value appears in a range. If the count is zero, it means the value is missing. Enter the following formula, adjusting the references for your specific worksheet name and cells:
5. Set the Format: Click the Format... button. Choose how you want the missing data to be highlighted. A bright fill color (like yellow or light red) usually works best. Click OK.
6. Apply the Rule: Click OK again to close the rule dialog box.

Now, any customer ID in Sheet1 that does not appear anywhere in Sheet2 will be highlighted with the color you chose. You can repeat the process in reverse on Sheet2 (changing the formula to reference Sheet1!$A:$A) to find IDs that are in Sheet2 but not in Sheet1.

Method 2: Find Missing Data with the VLOOKUP Formula

Creating a helper column with the VLOOKUP formula is a classic and versatile way to find missing data. VLOOKUP searches for a specific value in one column and returns a corresponding value from a different column in the same row.

When VLOOKUP can't find the value, it returns an #N/A error. We can use this to our advantage to flag missing entries.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions:

1. Add a Helper Column: In Sheet1, add a new column next to your data. Let's call it "Exists in Sheet2?".
2. Enter the VLOOKUP Formula: In the first cell of your new column (e.g., B2), type the following formula, assuming your IDs are in column A of both sheets:
3. Drag the Formula Down: Click the small square (the fill handle) at the bottom-right corner of the cell and drag it down to apply the formula to the rest of your rows.
4. Filter for Missing Data: The results will show the ID if it was found and #N/A if it wasn't. To see only the missing data, go to the Data tab, click Filter, and use the dropdown arrow on your helper column to filter for #N/A values.

Method 3: A More Flexible Approach with INDEX and MATCH

The combination of INDEX and MATCH is often considered more powerful and flexible than VLOOKUP. While it looks a bit more complicated, it’s faster on large datasets and doesn't break if you insert columns into your lookup sheet.

We'll combine these functions with IF and ISNA to create a clean, easy-to-read result.

Step-by-Step Instructions:

1. Create a Helper Column: Just like with VLOOKUP, add a new column in Sheet1. Give it a name like "Status Check".
2. Enter the Formula: In the first cell of the new column, enter this formula:
3. Apply and Filter: Drag the formula down the entire column. Now you can easily filter your "Status Check" column for cells that say "Missing" to find all the missing entries.

Method 4: Automate Comparisons with Power Query

For large datasets or recurring comparison tasks, Power Query (now called Get & Transform Data in recent Excel versions) is by far the most powerful method. It lets you "merge" two tables and filter for the differences, creating a brand new table that contains only the missing data. Best of all, once you set it up, you can refresh it with a single click as your data changes.

Step-by-Step Instructions:

1. Format Data as Tables: First, you need to turn both of your data sets into official Excel Tables. Click anywhere inside your first data list and press Ctrl + T. Make sure the "My table has headers" box is checked, and click OK. Name this table DataSheet1. Repeat this for the second list and name it DataSheet2.
2. Open Power Query Editor: Go to the Data tab on the Excel ribbon. Click Get Data > From Table/Range to load your first table into the Power Query Editor. Once loaded, click Close & Load To... and select "Only Create Connection". Repeat for the second table. Now both tables exist as connections Power Query can use.
3. Merge Queries: On the Data tab, go to Get Data > Combine Queries > Merge.
4. Configure the Merge:
5. Load the Result: A new query will show you the rows from your first sheet that don't exist in the second. Click Close & Load to load this into a new worksheet. It will give you a clean list of all the missing data.

The best part? If your original data changes, right-click your "missing data" table and select Refresh. Power Query will rerun the comparison and update the list automatically. No formulas needed!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Final Thoughts

Comparing Excel sheets is a common but crucial task, and knowing the right technique can save you hours of manual work. For a quick visual check on smaller lists, conditional formatting is great. For more definitive flagging, formulas like VLOOKUP or INDEX/MATCH are reliable workhorses. For large, recurring, or complex comparisons, investing a few minutes to learn Power Query will deliver massive efficiency gains over the long run.

These Excel headaches are often symptoms of scattered marketing and sales data living in separate files and platforms. Manually exporting CSVs from your different tools just to compare them is a time-consuming reactive process. Instead of asking "why is this data missing?", we help you get ahead of the question by connecting all your data sources - like Google Analytics, Shopify, Salesforce, and Facebook Ads - into a single, unified view. You can then use simple, natural language to build live dashboards that track performance in real-time without ever needing to compare two spreadsheets again. Give Graphed a try to see how easy it can be.