How to Show Duplicates in Excel (Complete Guide in 2026)

If you want to show duplicates in Excel, the fastest way is to use Conditional Formatting. Select your data, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and Excel will instantly color every duplicate in your range. Done in under 10 seconds.

But that is just the start. Depending on what you actually need, there are several methods to find, highlight, count, and remove duplicates. This guide covers all of them clearly, with real steps you can follow right now.

What Counts as a Duplicate in Excel?

Before jumping into methods, it helps to be clear. A duplicate is any value that appears more than once in a column or row. Excel treats “Apple” and “apple” as the same value (it is not case-sensitive by default). Numbers, dates, and text all qualify.

A full-row duplicate means every single cell in that row matches another row exactly. A partial duplicate means just one or two columns match. These require different approaches.

Method 1: Show Duplicates in Excel Using Conditional Formatting

This is the most visual and beginner-friendly method. It highlights duplicates directly in your spreadsheet so you can see them at a glance.

Step-by-Step

1. Select the range you want to check. For example, click A2 and drag to A100.
2. Go to the Home tab on the ribbon.
3. Click Conditional Formatting.
4. Hover over Highlight Cells Rules.
5. Click Duplicate Values.
6. In the dialog box, the first dropdown says “Duplicate.” Leave it as is.
7. Choose a highlight color from the second dropdown. Light Red Fill is the default and works well.
8. Click OK.

All duplicate values in your selected range will now be highlighted.

What You Can Change

Option	What It Does
Duplicate	Highlights values that appear more than once
Unique	Highlights values that appear only once
Fill color	Changes the background color of highlighted cells
Font color	Changes the text color instead

Important Notes

Conditional Formatting only highlights the cells. It does not delete anything or move data. If you add new data later, the formatting updates automatically.

Method 2: Use COUNTIF to Show Duplicates in Excel

If you want to see exactly how many times a value appears, COUNTIF is the right tool. It puts a number next to each value so you can sort or filter based on that count.

How It Works

Assume your data is in column A, starting at A2. In column B, type this formula in B2:

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

Then drag that formula down to fill the rest of column B.

Any row where column B shows a number greater than 1 is a duplicate. A value of 2 means it appears twice. A value of 3 means three times. And so on.

Turning That Into a Yes/No Flag

If you just want to know if something is a duplicate without caring about the count, wrap the formula like this:

=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique")

Now column B will say either “Duplicate” or “Unique” for each row. You can filter column B to show only the duplicates.

Method 3: Filter to See Only Duplicate Rows

Once you have flagged duplicates using COUNTIF or a helper column, you can filter your data to show only the duplicate rows.

Steps

1. Add a helper column with the COUNTIF formula above.
2. Click anywhere in your data.
3. Go to Data > Filter.
4. Click the dropdown arrow on your helper column header.
5. Uncheck “Unique” or any value equal to 1.
6. Click OK.

Now your spreadsheet shows only the rows where duplicates exist. You can review them, edit them, or copy them to another sheet.

Method 4: Use Remove Duplicates to Clean Your Data

Sometimes you do not just want to see duplicates. You want to delete them. Excel has a built-in tool for this.

Steps

1. Click anywhere inside your dataset.
2. Go to the Data tab.
3. Click Remove Duplicates.
4. A dialog box appears listing all your columns.
5. Check the columns Excel should use to determine a duplicate. If you check all columns, only rows where every column matches will be removed.
6. Click OK.

Excel tells you how many duplicates were removed and how many unique values remain.

Warning: This is permanent unless you undo it (Ctrl+Z). Always work on a copy of your data or save a backup before using this.

Method 5: Use Power Query to Find Duplicates (Advanced)

Power Query is more powerful and works well for large datasets or when you need to do this repeatedly.

How to Open Power Query

1. Click anywhere in your data.
2. Go to Data > Get & Transform Data > From Table/Range.
3. Excel loads your data into Power Query Editor.

Finding Duplicates in Power Query

To keep only duplicates:

1. Select the column you want to check.
2. Right-click the column header.
3. Click Keep Rows > Keep Duplicates.

To remove duplicates:

1. Select the column or columns.
2. Right-click.
3. Click Remove Duplicates.

When you are done, click Close & Load to bring the cleaned data back into Excel.

Power Query is especially useful when your source data changes regularly. You can refresh the query and it re-runs the deduplication automatically. Learn more about Power Query at Microsoft’s official Power Query documentation.

Method 6: Find Duplicate Rows (Entire Row Match)

If you need to find rows where all columns match, a single COUNTIF will not cut it. You need to combine multiple columns into one string to compare.

The Formula Approach

Suppose your data has three columns: A, B, and C. In a helper column D, type:

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2, $C$2:$C$100, C2)

This counts rows where all three columns match simultaneously. Any row with a count above 1 is a full-row duplicate.

Alternatively, you can concatenate the columns and count that:

=COUNTIF($A$2:$A$100&$B$2:$B$100&$C$2:$C$100, A2&B2&C2)

Note: Enter this as an array formula using Ctrl+Shift+Enter in older versions of Excel. In Excel 365 and Excel 2021, it works as a regular formula.

Comparing Two Columns for Duplicates

A common task is finding values that appear in both column A and column B. For example, you have a list of customers in A and a list of email subscribers in B, and you want to see who is in both.

Using MATCH

In column C, type:

=IFERROR(MATCH(A2, $B$2:$B$100, 0), "Not Found")

If a value in column A also exists in column B, the formula returns the row number where it was found. If not, it returns “Not Found.”

Using Conditional Formatting Across Two Columns

1. Select column A (A2:A100).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter: =COUNTIF($B$2:$B$100, A2)>0
5. Pick a fill color.
6. Click OK.

This highlights every value in column A that also appears in column B.

Which Method Should You Use?

Goal	Best Method
Visually highlight duplicates	Conditional Formatting
Count how many times a value appears	COUNTIF formula
Filter to see only duplicate rows	COUNTIF + Filter
Delete duplicates permanently	Remove Duplicates tool
Large datasets or repeated tasks	Power Query
Match values across two columns	MATCH or COUNTIF cross-column
Full-row duplicates	COUNTIFS on multiple columns

Common Mistakes to Avoid

Selecting the wrong range. If your range includes the header row, Excel may flag your header as a duplicate if the same word appears in the data. Always start your selection from row 2.

Not locking the range in COUNTIF. Using =COUNTIF(A2:A100, A2) without the dollar signs means when you drag the formula down, the range shifts and gives wrong results. Always use =COUNTIF($A$2:$A$100, A2).

Forgetting that Excel is not case-sensitive. “JOHN” and “john” will be treated as duplicates. If case matters to you, you need a more advanced formula using EXACT and SUMPRODUCT.

Using Remove Duplicates without a backup. This cannot be emphasized enough. Make a copy of your sheet first.

Case-Sensitive Duplicate Detection

If you need to treat “Apple” and “apple” as different values, use this array formula:

=SUMPRODUCT((EXACT($A$2:$A$100, A2))*1)

This counts exact matches including case. Any value above 1 is a case-sensitive duplicate.

For a deeper dive into Excel formulas for data validation, the Excel Easy website is a solid free reference.

Showing Duplicates in Excel Online and Excel 365

The Conditional Formatting and Remove Duplicates features work the same way in Excel Online and Microsoft 365 as they do in the desktop version. Power Query is available in Excel 365 and Excel 2016 and later on desktop. Excel Online has limited Power Query support as of 2026.

Summary

Showing duplicates in Excel is straightforward once you know which tool matches your goal.

Use Conditional Formatting when you want a fast visual overview. Use COUNTIF when you want to count occurrences and flag rows. Use Filter on top of a COUNTIF column to isolate only the duplicates. Use Remove Duplicates when you want to clean the data. Use Power Query for large, repeating workflows.

None of these methods are complicated once you follow the steps. The biggest mistake is not knowing which one to use for which situation. Now you do.

Frequently Asked Questions

How do I show duplicate values in Excel without deleting them?

Use Conditional Formatting. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This only highlights them visually and does not change or delete any data.

Can I highlight duplicates across multiple columns in Excel?

Yes. Select all the columns you want to check together, then apply Conditional Formatting with the Duplicate Values rule. Excel will highlight any cell value that appears more than once across all selected columns combined.

How do I find duplicate rows where every column matches?

Use COUNTIFS with all your columns: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2). Any row returning a count greater than 1 is a full-row duplicate.

Why is Conditional Formatting not showing my duplicates?

Check that you selected the correct range and that it does not include empty rows or mismatched data types. Also verify the rule is set to “Duplicate” and not “Unique.” Sometimes a space or invisible character makes two seemingly identical values look different to Excel.

Does Excel count the first occurrence as a duplicate?

Yes. Unlike some tools that mark only the second and later occurrences as duplicates, Excel’s Conditional Formatting highlights all occurrences including the first. If you want to mark only the second occurrence onward, use a COUNTIF formula like =COUNTIF($A$2:A2,A2)>1, which counts only previous rows.