- With Excel's built-in tools, you don't have to spend any time hunting for duplicates in your spreadsheets: start by selecting the row or column you're interested in (selection tips).
- To highlight duplicate cells, make sure that the Home tab is selected in the ribbon: click on Conditional Formatting, and go to "Highlight Cells Rules > Duplicate Values".
- Excel opens the Duplicate Values dialog: to highlight duplicates, keep "Duplicate" in the dropdown; select "Unique" instead to highlight non-duplicate values. "Light Red Fill with Dark Red Text" is the second dropdown's default, but you can customize your highlight rule to use different background and text colors: choose "Custom Format" to assign custom fonts, colors, etc.
- Click OK to highlight these cells.
- No need to highlight them to just delete duplicate cell values: click on the ribbon's Data tab.
Tip: ensure that the range of cells is selected, to avoid a cryptic error message in the next step.
- Click on the "Remove Duplicates" button, under Data Tools:
- In the Remove Duplicates dialog that opened, confirm that the right columns are selected.
- Check the "My data has headers" checkbox if the first selected row(s) contain a label (vs. a value), to force Excel to ignore those values in the process. Click OK.
- A confirmation message will tell you something like "X duplicate values found and removed; Y unique values remain". To easily see what cells were deleted, quickly press Ctrl+Z to undo and Ctrl+Y to redo several times: this gives you a movie loop of the "before and after"!
Email AOL Mail Gmail Hotmail Yahoo Mail MS Office Excel 2010 OneNote 2010 Outlook 2010 Word 2010 OS & Devices Kindle Fire Windows 7 Web Dev C# and .NET JavaScript jQuery