Finding duplicates in a single column

The fastest way to spot duplicate values in one column is to use Excel's built-in Conditional Formatting feature, which highlights cells that repeat. Select the column you want to check, go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, then select Duplicate Values. Excel will when ready color any cell that appears more than once in that column.

This method works best when you have a few hundred rows or fewer. The highlighting stays in place as you scroll, so you can see which entries repeat without writing any formula. If you want to remove the highlighting later, go back to Conditional Formatting and click Clear Rules.

If you need to count how many times each value repeats rather than just see which ones do, use the COUNTIF function instead. In a blank column next to your data, type =COUNTIF($A$2:$A$100,A2) (adjusting the range to match your actual data), then copy that formula down. Any cell showing a number higher than 1 is a duplicate.

Finding duplicates across two columns

When you need to know whether values in one column also appear somewhere in a different column, use a COUNTIF formula that looks at both ranges. In a blank column, type =COUNTIF($B$2:$B$100,A2) to check whether each value in column A exists anywhere in column B. A result of 1 or higher means that value appears in column B; a 0 means it does not.

This approach is useful when you are comparing a list of customer IDs against a list of people who made a purchase, or checking whether items on one inventory list appear on another. The formula tells you which rows have matches without changing your original data.

Removing duplicates entirely

If you want Excel to delete duplicate rows rather than just highlight them, select all the data you want to clean (including headers), go to the Data tab, and click Remove Duplicates. A dialog box will appear asking which columns to check — leave all of them selected unless you only want to compare specific columns.

Excel will delete every row that is an exact duplicate of another row, keeping only the first occurrence of each unique entry. This action cannot be undone, so copy your data to a new sheet first if you want to keep the original. After removal, Excel tells you how many duplicate rows it deleted.

Finding duplicates with the Advanced Filter

For more control over which duplicates you see, use the Advanced Filter. Select your data range, go to the Data tab, click Advanced, and choose Filter the list, in-place. Check the box that says No duplicates, and Excel will temporarily hide every duplicate row, showing only the first occurrence of each value.

This method does not delete anything — it only hides rows. You can remove the filter at any time to see all your data again. This is useful when you want to review unique values without permanently changing your spreadsheet, or when you need to see the full context of each duplicate before deciding whether to keep it.

Key Takeaways

  • Conditional Formatting with Duplicate Values highlighting is the quickest way to see which cells repeat in a single column.
  • The COUNTIF function lets you count how many times each value appears, which works across columns and gives you a number rather than just color.
  • The Remove Duplicates feature deletes entire rows that match, but this action cannot be undone, so save a copy first.
  • Advanced Filter with No duplicates hides duplicate rows without deleting them, letting you review the data before making permanent changes.

Choosing the right method for your situation

Use Conditional Formatting when you just need to see which values repeat and do not plan to change the data. It is fast, reversible, and requires no formulas. Use COUNTIF when you need a count of how many times each value appears or when you are comparing values across different columns. Use Remove Duplicates only when you are certain you want to delete duplicate rows permanently and have already backed up your file.

If you are unsure whether duplicates should be removed, use Advanced Filter first to hide them and review what you have. Once you are confident about what should stay, you can then use Remove Duplicates or delete rows manually. This two-step approach prevents accidental data loss.

Frequently Asked Questions

Will Conditional Formatting slow down my spreadsheet?

No. Conditional Formatting only changes how cells look — it does not add formulas or change file size significantly. Even with thousands of rows, highlighting duplicates has almost no impact on performance.

Can I find duplicates in multiple columns at once?

Yes. When you use Remove Duplicates, Excel checks all selected columns by default, so a row is only kept if the combination of values across all columns is unique. With Conditional Formatting, you must check each column separately.

What happens if I remove duplicates but then realize I made a mistake?

The Remove Duplicates action cannot be undone with Ctrl+Z if you close the file. Always save a copy of your data before using this feature, or use Advanced Filter to hide duplicates first so you can review them.

Does COUNTIF work if my data has spaces or different capitalization?

COUNTIF treats "Smith" and "smith" as the same value, but it treats "Smith" and "Smith " (with a trailing space) as different. If your data has inconsistent spacing, use Find & Replace to clean it up first.

Can I find duplicates based on part of a cell's content?

Conditional Formatting and Remove Duplicates only work on exact matches. To find cells that contain the same partial text, you would need to use a more complex formula or manually review the data.