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.

If you need a list of just the duplicates rather than highlighting them in place, use the COUNTIF function to mark which rows contain repeated values. In a new column, type =COUNTIF($A$2:$A$100,A2)>1 (adjusting the range to match your data), then copy the formula down. Any cell that shows TRUE contains a value that appears more than once in your range.

For larger datasets or when you need to remove duplicates entirely, Excel's Remove Duplicates feature deletes every copy except the first occurrence. Select your data, go to the Data tab, click Remove Duplicates, choose which columns to check, and click OK. Excel will tell you how many rows it removed.

Key Takeaways

  • Conditional Formatting with Duplicate Values highlighting is the fastest visual way to spot duplicates without changing your data.
  • The COUNTIF function lets you mark duplicates in a helper column so you can review them before taking action.
  • Remove Duplicates permanently deletes repeated rows, keeping only the first instance of each value.
  • Always work on a copy of your data or save your file first, because Remove Duplicates cannot be undone.
  • When checking multiple columns at once, make sure your data has headers so Excel knows which rows belong together.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the easiest method when you want to see duplicates without changing your spreadsheet. Select the range of cells you want to check — click the first cell, hold Shift, and click the last cell in your range. If your data has headers in row 1, start your selection in row 2.

Open the Home tab at the top of the ribbon. Click the Conditional Formatting button (it looks like a paint bucket), hover over Highlight Cell Rules, and click Duplicate Values. A dialog box will appear. Leave the default setting as is and click OK. Excel will now color every cell that contains a value appearing more than once in your selected range.

The highlighting stays in place until you remove it. To clear it, select the same range again, go to Conditional Formatting, click Clear Rules, and choose Clear Rules from Selected Cells. This removes only the formatting, not the data itself.

Finding duplicates with the COUNTIF formula

The COUNTIF function counts how many times each value appears in a range. Use it to create a helper column that marks which entries are duplicated. This method lets you review duplicates before deciding what to do with them.

In a blank column next to your data, type a header like "Count" in the first row. In the cell below, type =COUNTIF($A$2:$A$100,A2)>1, replacing A2:A100 with your actual data range. The dollar signs lock the range so it does not change when you copy the formula down. Press Enter.

Now copy this formula down to every row with data. Click the cell with your formula, copy it (Ctrl+C), select the range below it, and paste (Ctrl+V). Any cell showing TRUE contains a duplicate value. You can then sort or filter by this column to group all duplicates together, or manually review them one by one.

Removing duplicates permanently

Excel's Remove Duplicates feature deletes every repeated entry except the first one. This is permanent and cannot be undone, so always save a backup copy of your file first or work on a copy of your data.

Select all your data including headers. Go to the Data tab and click Remove Duplicates. A dialog will show all your columns. Make sure only the columns you want to check are selected — if you want to find duplicates based on one column only, uncheck the others. Click OK.

Excel will show a message telling you how many duplicate rows it removed. The remaining data will shift up to fill the gaps. If you made a mistake, press Ctrl+Z when ready to undo the action.

Checking for duplicates across multiple columns

Sometimes you need to find rows that repeat across several columns, not just one. For example, you might want to find customers with the same first name AND last name, not just the same last name alone.

With Conditional Formatting, select your entire data range including all columns you want to check. Go to Home, Conditional Formatting, Highlight Cell Rules, Duplicate Values. Excel will highlight any row where the exact combination of values in all selected columns repeats.

With Remove Duplicates, select your data, go to Data, Remove Duplicates, and make sure all the columns you want to check are selected. Excel will only remove a row if it matches another row in every selected column. If you uncheck a column, Excel ignores it when deciding whether rows are duplicates.

Handling duplicates in columns with dates or numbers

Duplicates in date or number columns work the same way as text, but watch out for formatting differences. A date stored as "1/15/2024" and one stored as "01/15/2024" look the same but might not match if the formatting is different underneath.

If Conditional Formatting or COUNTIF is not catching duplicates you know are there, check the cell format. Right-click the cell, click Format Cells, and look at the Number tab. Make sure all cells in your range use the same format. If they do not, convert them all to the same format first — select the range, right-click, Format Cells, and choose a consistent format like Date or Number.

For numbers, watch for leading or trailing spaces. A cell containing " 100" (with a space before) is technically different from "100". Use the TRIM function to remove extra spaces: in a helper column, type =TRIM(A2) and copy down, then check for duplicates in the trimmed column.

Finding duplicates when you have blank cells

Blank cells can cause confusion because Excel treats each blank as a separate value. If you have multiple empty cells in a column, Conditional Formatting will highlight all of them as duplicates of each other.

If you want to ignore blanks, use COUNTIF instead of Conditional Formatting. In your helper column, type =IF(A2="","",COUNTIF($A$2:$A$100,A2)>1). This formula leaves blank cells empty instead of marking them as duplicates. Copy it down your entire range.

Before using Remove Duplicates, decide whether you want to keep or delete rows with blank values. If you delete them, you might lose important data. Consider filtering out blanks first, removing duplicates from the non-blank rows, then handling the blanks separately.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close the file, the undo history is gone and you cannot recover the deleted rows. Always save a backup copy before using Remove Duplicates, or use Conditional Formatting to review duplicates first before deciding to delete them.

Why is Conditional Formatting not highlighting duplicates I know are there?

The most common reason is extra spaces or formatting differences. A cell containing "Smith " (with a trailing space) is different from "Smith". Use the TRIM function to clean up spaces, or check that all cells use the same number format. Also make sure you selected the correct range — if you start your selection in row 1 with headers, Excel might treat the header as a duplicate.

How do I find duplicates between two different columns?

Use COUNTIF to check if values in one column appear anywhere in another column. In a helper column, type =COUNTIF($B$2:$B$100,A2)>0 to see which values from column A exist in column B. This marks matches rather than duplicates within a single column.

Can I highlight duplicates with a different color?

Yes. After explore Conditional Formatting with Duplicate Values, select the range again, go to Conditional Formatting, Manage Rules, find your Duplicate Values rule, click Edit Rule, and choose a different fill color or font color. Click OK to explore the new formatting.

What happens to formulas when I use Remove Duplicates?

Remove Duplicates deletes entire rows, including any formulas in those rows. If you have formulas in other columns that reference the deleted rows, those formulas will show an error. Copy your formulas to a safe location first, or use Conditional Formatting to review duplicates before removing them.