Removing duplicates in Excel makes your data cleaner and more organized. This guide explains simple tools and formulas.

#1: Using Excel’s Built-In Tool to Remove Duplicates

1. Select your data. Highlight the range where you want to remove duplicate values.
2. Open the Remove Duplicates tool. Go to the Data tab and click Remove Duplicates in the ribbon.
3. Select if your data has headers: this action will exclude your header from the process to remove duplicates
4. Click OK to Delete all the values more than one time.
5. At the end of the process, a dialog box shows how many values have been removed and how many values remain.

• This tool works quickly but changes your data directly. Always save a backup before using this option.
• Also, if your source is updated, you must redo the technique.

#2: UNIQUE function to keep each value only one time

The UNIQUE function in Excel 365 helps extract unique values from a range.

• It creates a separate list with no duplicates.
• This function does not modify the original data 👍
• When your original data will be updated, the UNIQUE function will automatically refresh the result.

The UNIQUE function is flexible and works well for creating dynamic lists. Use it when you need a duplicate-free summary of your data.

#3: Highlighting Duplicates with Conditional Formatting

Conditional formatting makes duplicates stand out visually, helping you spot them easily without changing your data.

1. Select your data range. Highlight the cells where you want to find duplicates.
2. Open Conditional Formatting. Go to the Home tab and click Conditional Formatting in the ribbon.
3. Choose the Duplicate Values option. Select Highlight Cells Rules > Duplicate Values.
4. Pick a formatting style. Choose a color to highlight duplicates and press OK.

Which Method Should You Use?

• Use the Ribbon tool for quick, direct cleanup.
• Use the formula for a safer, and dynamic solution
• Conditional formatting is the perfect tool only to visualize duplicates