How to Show Overlapping Data in Excel

Chances are you have two lists of data in Excel, and you need a quick way to see who - or what - is on both. Whether you're comparing a list of webinar attendees with your newsletter subscribers or checking last month's customer list against this month's, finding the overlap is a common and often frustrating task. This guide will walk you through four different methods, ranging from a quick visual highlight to a powerful automated formula, to help you find and show overlapping data in Excel.

The Easiest Method: Highlighting Duplicates with Conditional Formatting

If you just need a fast, visual way to see the overlapping values without creating new columns or lists, Conditional Formatting is your best friend. In under a minute, you can make all the overlapping names, emails, or IDs stand out in a color of your choice.

This works best when your two lists are in columns side-by-side, but they can be on different sheets as well.

Step-by-Step Instructions:

1. Select both ranges of data you want to compare. You can do this by clicking and dragging over the first list, then holding down the Ctrl key (or Cmd on Mac) and selecting the second list.
2. On the Home tab in Excel's ribbon, find and click on Conditional Formatting.
3. From the dropdown menu, go to Highlight Cells Rules > Duplicate Values.
4. A small dialog box will pop up. The first dropdown should be set to "Duplicate." The second dropdown lets you choose the formatting style. "Light Red Fill with Dark Red Text" is the default, but you can choose any color or create a custom format.
5. Click OK.

Instantly, all the values that appear in both of your selected lists will be highlighted. This gives you an immediate visual confirmation of the overlap without changing any of your data.

Pro Tip: In that same dialog box, you can switch from "Duplicate" to "Unique" to highlight the values that are only present in one list, not both.

Creating a Helper Column with the COUNTIF Function

Sometimes, highlighting isn't enough. You might need to filter your list to show only the overlapping entries or perform other actions based on the results. For this, creating a "helper column" with a formula is the way to go. The COUNTIF function is perfect for this job.

The logic is simple: for each item in your first list, we’ll ask Excel to count how many times it appears in the second list. If the count is greater than zero, it's an overlap.

Step-by-Step Instructions:

Let's imagine you have a list of "Webinar Attendees" in Column A and a list of "Newsletter Subscribers" in Column B. You want to see which webinar attendees are also on your newsletter list.

1. In cell C2 (or the first empty cell next to your first list), type the following formula:
2. Press Enter. This formula will return a number. If it’s 1 or more, it means the name in cell A2 exists somewhere in Column B. If it’s 0, it doesn’t.
3. To make this more intuitive, we can wrap it in a simple logical test to return TRUE or FALSE:
4. Now, the formula returns TRUE if an overlap is found and FALSE if it's not.
5. Click on the cell with your new formula (C2). Move your cursor to the small green square at the bottom-right corner of the cell (the Fill Handle). Double-click it to automatically drag the formula down for your entire list.

You now have a new column with TRUE or FALSE values. You can easily filter this column to show only the TRUE results, giving you a clean list of everyone who overlaps.

How to Filter Your Results:

• Select the header of your new helper column (e.g., cell C1).
• Go to the Data tab and click the Filter button (it looks like a funnel).
• A small dropdown arrow will appear on your header. Click it.
• In the filter menu, uncheck "FALSE" (or "0") and make sure "TRUE" (or "1") is checked.
• Click OK. Your list will now hide all rows that aren't overlaps.

Using VLOOKUP to Check for Matches

VLOOKUP is another classic tool for this job. While it's often used to retrieve related information, it’s also excellent at simply confirming if a value exists in another list. When VLOOKUP can’t find a value, it returns an #N/A error, which is a clear signal that there's no match.

Let's use the same example: "Webinar Attendees" in Column A and "Newsletter Subscribers" in Column B.

Step-by-Step Instructions:

1. In cell C2, type the following VLOOKUP formula:
2. Let’s quickly break that down:
3. Drag this formula down. If a match is found, the formula will return the person's name (or email). If no match is found, you’ll see the #N/A error.

Cleaning Up the Output with IFERROR

The #N/A errors are functional but look messy. You can create a more user-friendly output by wrapping your VLOOKUP in an IFERROR function. This lets you specify what to show instead of the error.

=IFERROR(VLOOKUP(A2, B:B, 1, FALSE), "No Match")

Now, if a match is found, you’ll see the name. If not, you’ll see the more user-friendly text "No Match." You can then easily filter this column to exclude the "No Match" entries.

Modern and Dynamic: The FILTER and COUNTIF Combo (Microsoft 365)

If you're using a modern version of Excel (Microsoft 365), you have access to dynamic array functions that make this process incredibly clean and efficient. The FILTER function can produce a new, clean list of only the overlapping values without any helper columns or dragging formulas.

This approach combines the logic of COUNTIF with the power of FILTER.

Step-by-Step Instructions:

This time, we will create a brand-new list of just the overlapping names. No helper columns needed.

1. Click on any empty cell where you want your list of overlaps to begin (e.g., cell D2).
2. Type the following formula:
3. Make sure you adjust the ranges A2:A50 and B2:B100 to match the actual size of your lists.
4. Press Enter.

That's it! Excel will automatically "spill" a list of all names from your first list (Column A) that also appear in your second list (Column B) into the cells below your formula. If you add or remove names from your original lists, this new list will update automatically. There's no need to drag anything down or to filter, you get a perfect list of the overlapping data instantly.

How It Works

The magic is in the two parts of the formula:

• The COUNTIF(B2:B100, A2:A50) part runs a check for every item in Column A. It generates an array of numbers (1s and 0s).
• The FILTER function then takes your list from Column A and only shows the rows where the COUNTIF result was not zero, effectively filtering for only the matches.

Final Thoughts

Finding overlapping data in Excel is manageable once you know which tool to use for the job. From visually flagging duplicates with Conditional Formatting to generating a clean, dynamic list with the FILTER function, one of these methods will surely get the job done quickly and accurately, freeing you up to focus on what the data actually means.

While mastering these Excel functions is powerful, all of them require you to first manually export specific lists from your various tools into a spreadsheet. Frankly, this is exactly the kind of manual reporting work we built Graphed to eliminate. Instead of downloading CSVs from your CRM and email platform to combine in a spreadsheet, we allow you to connect those sources directly. You can then simply ask in plain English, "Show me customers from HubSpot who also unsubscribed in Klaviyo last month," and get a live, automated answer in seconds. You skip the spreadsheet wrangling entirely and get straight to the insight.