How to Find Same Data in Excel

Wrangling spreadsheet data often feels like trying to solve a puzzle, especially when you need to find where the same data lives across different rows, columns, or even entire sheets. Whether you're cleaning up a CRM export, comparing monthly sales lists, or just trying to remove duplicates, knowing how to identify identical data in Excel is an essential skill. This guide will walk you through several effective methods, from simple visual tricks to more powerful formulas, to help you find matching data quickly.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Conditional Formatting: The Easiest Way to Find Duplicates Visually

For a quick and easy way to spot duplicates within a single column or range, Conditional Formatting is your best friend. It doesn’t change your data, it just highlights cells based on rules you set, making it perfect for visual analysis.

How to Use Conditional Formatting to Highlight Duplicates

Let's say you have a list of email addresses in column B and you want to see if any have been accidentally entered more than once.

1. Select your data: Click and drag to highlight the entire range of cells you want to check for duplicates (e.g., B2:B150).
2. Go to the Home tab: In the Excel ribbon at the top, find the "Styles" group and click on Conditional Formatting.
3. Choose the rule: A dropdown menu will appear. Hover over Highlight Cells Rules and then click on Duplicate Values....
4. Format your duplicates: A small dialog box will pop up. Make sure "Duplicate" is selected in the first dropdown menu. You can then choose how you want the duplicates to be formatted. The default "Light Red Fill with Dark Red Text" is usually a great place to start. Click OK.

Instantly, all the duplicate email addresses in your selected range will be highlighted in red. This method is incredibly fast for finding repeats within a single, continuous list.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Using Formulas to Cross-Reference Two Different Columns

What if your data isn't in one list? A common task is comparing two separate columns to see which entries appear in both. For example, maybe you want to find out which customers who bought a product in January (List A) also bought another product in February (List B). Formulas are the most effective way to handle this.

Method 1: The COUNTIF Formula

The COUNTIF function is perfect for this. It counts how many times a specific value appears in a range of cells. If the count is greater than zero, it means the value exists in the other list.

Imagine your January customers are in column A (A2:A100) and your February customers are in column B (B2:B80). We want to check which names from column B also appear in column A.

1. Click into cell C2 (or any adjacent empty column). This will be our "helper column."
2. Type the following formula and press Enter:

=COUNTIF($A$2:$A$100, B2)

1. Click back on cell C2, grab the small square (the "fill handle") at the bottom-right corner of the cell, and drag it down to the last row of your data in column B.

Breaking Down the Formula:

• $A$2:$A$100: This is the range we're checking against (the master list from January). The dollar signs ($) are important, they "lock" the range so it doesn’t shift as you drag the formula down.
• B2: This is the individual cell we are currently checking to see if it exists in the January list. When you drag the formula down, this will change to B3, B4, and so on.

After dragging the formula, column C will show a number. Any row with a number greater than 0 contains a name that exists in both lists. Any row with a 0 means the name from column B does not appear in column A. You can then filter column C to show only values greater than 0 to get your final list of matching customers.

Method 2: The Mighty VLOOKUP

VLOOKUP is another classic function for comparing lists, though it's typically used to pull related information. For simply finding if a value exists, it works great too.

Using the same customer list scenario, we want to look up each name from the February list (column B) and see if we can find it in the January list (column A).

1. Click into cell C2.
2. Type this formula:

=VLOOKUP(B2, $A$2:$A$100, 1, FALSE)

1. Drag the formula down the column, just like with COUNTIF.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Breaking Down the Formula:

• B2: The value you want to look for.
• $A$2:$A$100: The range where you are looking for the value.
• 1: The column number in the range from which to return a value. Since our range is only one column, we put 1.
• FALSE: This specifies an exact match. It's crucial for making sure you don't get false positives.

When you drag this formula down, one of two things will happen. If a name from column B is found in column A, the formula will return that name. If the name is not found, the formula will return an #N/A error. This error is actually helpful - it tells you there's no match! You can then filter column C to exclude the #N/A results to see all the repeats.

Finding Entire Duplicate Rows

Sometimes finding duplicate values in a single column isn't enough. You might need to find rows where all the data is identical - like a customer who was accidentally entered twice with the same name, email, and sign-up date.

The Helper Column and Concatenate Trick

The easiest way to do this is to create a new "helper" column that combines the data from all other columns in that row into a single string of text. If two rows are identical, their combined strings will also be identical. Then, you can just use Conditional Formatting on that one helper column.

Let's say your data is in columns A, B, and C.

1. In cell D2, type the following formula:

=A2&B2&C2

The ampersand (&) operator joins the text from each cell together.

1. Drag this formula down to apply it to all your rows. Now, column D contains a unique identifier for each row's data.
2. Finally, use the first method we discussed: apply Conditional Formatting > Highlight Cells Rules > Duplicate Values to your new helper column (column D).

Excel will instantly highlight the rows that are exact duplicates across all the columns you combined.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Deal with Duplicates After You Find Them

Once you’ve identified all the duplicate data, the next logical step is often to remove it.

Heads up: Always make a copy of your worksheet before removing data. It's a simple step that can save you a big headache if you make a mistake.

Using Excel's Built-In Remove Duplicates Tool

1. Select the entire data set you want to clean up. A quick way to do this is to click any cell within your data and press Ctrl + A.
2. Go to the Data tab in the ribbon.
3. In the "Data Tools" group, click Remove Duplicates.
4. A dialog box will appear showing all the columns in your selected data. Check the boxes for the columns you want to consider when identifying duplicates. If you want to delete rows only if all the columns are identical, make sure every column is checked.
5. Click OK. Excel will report how many duplicate rows were found and removed.

Final Thoughts

Whether you're using simple highlighters or more flexible formulas like COUNTIF and VLOOKUP, Excel provides a complete toolkit for finding identical data. The method you choose simply depends on how your data is structured and what your end goal is. Mastering these techniques will help you clean datasets, compare lists, and pull actionable insights with more confidence.

Of course, this process often starts with exporting CSV files from multiple platforms and wrangling them in spreadsheets - the kind of manual busywork that eats up hours every week. We built Graphed to cut out that step entirely. Instead of struggling with spreadsheets, you can connect your data sources directly (like Shopify, Google Analytics, Salesforce, etc.) and simply ask a question like, "Show me customers who exist in both my Shopify and HubSpot lists." Graphed generates the analysis instantly, so you can skip the manual work and get right to the insights.