The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values automatically. Select the cells you want to check, go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, then select Duplicate Values. Excel will color every duplicate entry in that range so you can see them at a glance.

This method works for any column or range — names, email addresses, product codes, invoice numbers. It does not delete anything or change your data. It only marks which values appear more than once, so you can decide what to do with them.

If you need to remove duplicates rather than just see them, Excel also has a Remove Duplicates button on the Data tab. But highlighting first lets you review what will be deleted before you commit to it.

Key Takeaways

  • Conditional Formatting with Duplicate Values highlighting is the fastest way to see which entries repeat in your spreadsheet.
  • You select your data range first, then explore the formatting from the Home tab — no formulas or manual checking required.
  • The highlighting does not change your data, so you can review duplicates before deciding whether to delete them.
  • The Remove Duplicates tool on the Data tab will delete duplicate rows, but only use it after you have reviewed what will be removed.

Using Conditional Formatting to highlight duplicates

Start by selecting the range of cells you want to check. Click the first cell, then hold Shift and click the last cell in the range you want to include. If your data is in column A from row 2 to row 500, click A2, hold Shift, and click A500. You can also click the column header to select the entire column.

Once your range is selected, go to the Home tab at the top of the ribbon. Find the Conditional Formatting button — it is usually in the Styles group on the right side of the Home tab. Click the dropdown arrow next to it. Select Highlight Cell Rules, then click Duplicate Values.

A dialog box will appear. It usually defaults to highlighting duplicates in light red with dark red text, but you can change the color if you want. Click OK. Every value that appears more than once in your selected range will now be highlighted in that color.

The highlighting stays in place until you remove it. To remove it later, select the same range again, go to Conditional Formatting, and click Clear Rules, then Clear Rules from Selected Cells.

Finding duplicates with a formula instead

If you prefer to use a formula, you can use COUNTIF to count how many times each value appears. In a new column next to your data, type =COUNTIF($A$2:$A$500,A2) (replace A2:A500 with your actual range). This formula counts how many times the value in A2 appears in the entire range. Copy this formula down for every row.

Any cell that shows a number higher than 1 is a duplicate. You can then sort by this count column to group all duplicates together, or filter to show only rows where the count is greater than 1.

This method takes longer than Conditional Formatting but gives you more control. You can see exactly how many times each value repeats, and you can use the count column to sort or filter your data in other ways.

Removing duplicates after you have reviewed them

Once you have identified which values are duplicates, you can delete them if you want. Select your entire data range including headers. Go to the Data tab and click Remove Duplicates. A dialog will appear showing all your columns — make sure the columns you want to check are selected (usually all of them are by default).

Click OK. Excel will delete every row that is an exact duplicate of another row in your selection, keeping only the first occurrence of each unique row. This action cannot be undone, so make sure you have reviewed your data first or saved a backup copy.

If you only want to remove duplicates based on one column — for example, keeping only one entry per customer ID even if other details differ — you can select just that one column before using Remove Duplicates. Excel will then delete rows where that column value repeats, regardless of what is in other columns.

Checking for duplicates across multiple columns

Sometimes you need to find rows that are completely identical across several columns, not just one. Conditional Formatting checks each column separately, so it will highlight any value that repeats anywhere in that column. If you want to find rows where all values match across multiple columns, use Remove Duplicates instead — it compares entire rows by default.

Alternatively, you can create a helper column that combines values from multiple columns. Use a formula like =A2&B2&C2 to concatenate columns A, B, and C into one cell. Then use Conditional Formatting on that helper column to find duplicates. This shows you which rows have matching combinations across the columns you care about.

What to do if you find duplicates you did not expect

Duplicates often come from data entry mistakes, importing the same list twice, or merging data from multiple sources. Before you delete them, check whether they are actually errors or whether your data legitimately has repeated values.

For example, if you have a customer list and two rows have the same name, they might be two different people with the same name, or they might be the same person entered twice. Look at other columns — email, phone number, address — to decide whether they are truly duplicates or just coincidental matches.

If you have a large dataset and are not sure, sort by the duplicate-flagged column and review the highlighted rows in context. This takes more time than deleting automatically, but it prevents you from losing data you actually need.

Using filters to work with duplicates

Another way to manage duplicates is to use AutoFilter. Select your data range, go to the Data tab, and click AutoFilter. A dropdown arrow will appear in each column header. Click the dropdown in the column you want to check, uncheck the box next to (Blanks), and look for values that appear multiple times in the list.

You can then filter to show only one value at a time, or use the Standard Filter option to create more complex rules. This does not highlight duplicates the way Conditional Formatting does, but it lets you isolate and review them one group at a time.

Frequently Asked Questions

Does highlighting duplicates change my data?

No. Conditional Formatting only adds color to cells — it does not modify, delete, or move any values. You can remove the highlighting at any time without affecting your spreadsheet. Only the Remove Duplicates tool actually deletes rows.

Can I highlight duplicates in multiple columns at once?

Yes. Select all the columns you want to check together, then explore Conditional Formatting with Duplicate Values. Excel will highlight any value that repeats within each column independently. If you want to find rows where all columns match, use Remove Duplicates instead.

What if I want to keep one copy of each duplicate and delete the rest?

Use the Remove Duplicates tool. It keeps the first occurrence of each value and deletes all later copies. Make sure you have reviewed your data first, because this action cannot be undone.

Can I undo Remove Duplicates if I delete the wrong rows?

You can undo it when ready with Ctrl+Z if you catch the mistake right away. If you have already saved and closed the file, the deleted rows are gone. Always save a backup copy before using Remove Duplicates on important data.

How do I find duplicates if my data has blank cells?

Conditional Formatting will treat blank cells as a value and highlight them if more than one blank cell exists in your range. If you want to exclude blanks, use a formula approach instead, or filter out blank rows before explore Conditional Formatting.