Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data. Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

  1. Select the cells you want to check for duplicates.

    Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.

  2. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

    Remove Duplicates 1

  3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then select OK.

    Remove Duplicates 2

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data is permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

  1. Select the range of cells that has duplicate values you want to remove.

    Tip:  Remove any outlines or subtotals from your data before trying to remove duplicates.

  2. Select Data > Remove Duplicates, and then under Columns, check or uncheck the columns where you want to remove the duplicates.

    Remove Duplicates 3

    For example, in this worksheet, the January column has price information I want to keep.

    Duplicate values highlighted

    So, I unchecked January in the Remove Duplicates box.

    Remove Duplicates 5

  3. Select OK.

Note: The counts of duplicate and unique values given after removal could include empty cells, spaces, etc. 

Need more help?

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!