Buzz
When working with a Microsoft Excel spreadsheet containing large amounts of data, you may encounter duplicate values. Excel's Conditional Formatting feature will highlight these duplicates, while the Remove Duplicates tool will allow you to delete them. Reviewing and removing duplicates helps ensure the accuracy and presentation of your data.
Steps to Follow
Using Conditional Formatting
Open the original file. The first thing you need to do is select the entire range of data that you want to check for duplicates.
Click on the cell in the top-left corner of the data group to begin the selection process.
Hold down the ⇧ Shift key and click on the cell at the bottom-right corner of the data group. This will select all of your data.
- You can perform this in any order (for example, click on the bottom-right cell first and then begin selecting).
Click on "Conditional Formatting." It is an option located in the "Home" tab/ribbon on the toolbar (often found under "Styles"). A dropdown menu will appear.
Select "Highlight Cells Rules" and then choose "Duplicate Values". Ensure your data is highlighted. A new window will open with additional options in the dropdown menu.
Select "Duplicate Values" from the dropdown menu.
- If you'd like to show only unique values, choose "Unique".
Choose a highlight color. The highlight color will indicate the duplicated values. By default, the text will appear in dark red and light red.
Click "OK" to view the results.
Select the duplicate cell and press the Delete key to remove. You should not delete these values if they represent essential data (such as survey numbers).
- After you remove the duplicate data, the remaining unique value will no longer be highlighted.
Click "Conditional Formatting" again. Regardless of whether you decide to delete the duplicate data or not, it’s important to turn off the formatting feature before closing the document.
Select "Clear Rules" (Clear rules), then choose "Clear Rules from Entire Sheet" (Remove rules from the whole sheet). All highlights of the duplicate data you did not delete will be removed.
- If the spreadsheet contains multiple formatted items, you can select a specific range and click "Clear Rules from Selected Cells" (Remove rules from selected cells) to remove the highlighting.
Save your changes to the document. If you're satisfied with the results, it means you've successfully found and removed duplicate data in Excel!
Use Excel's Remove Duplicates feature
Open the original file. The first thing you need to do is select all the data you want to check for duplicates.
Click the cell in the upper-left corner of the data group to begin the selection process.
Hold down the ⇧ Shift key and click the last cell in the lower-right corner of the data group. All your data will be selected.
- You can proceed in any order (for example, click the bottom-right cell first, then start selecting).
Click on the "Data" tab at the top of the screen.
Locate the "Data Tools" section in the toolbar. This section contains tools for managing the selected data, including the "Remove Duplicates" feature.
Click on "Remove Duplicates." A custom window will pop up.
Click on "Select All". This will select all columns in the document.
Check the columns you want to apply the tool to. By default, all columns will be selected.
Check the option 'My data has headers' if you think it is necessary. The program will label the first row in each column as headers and exclude them from the deletion process.
Click 'OK' to remove duplicate data. Once you're satisfied with your selections, press 'OK'. All duplicate values will be removed from your selected range.
- If the program responds by saying no matching cells are found, even though you know duplicates exist, try checking each column box in the 'Remove Duplicates' window. Reviewing one column at a time will solve the issue.
Save the changes to the document. If you're happy with the result, it means you've successfully located and removed duplicate data in Excel!
Tip
- You can also detect duplicates by installing third-party add-ins. Some of these enhance Excel's conditional formatting features and allow you to use various colors to highlight identical values.
- The process of removing duplicates is particularly useful when you need to check attendee lists, address books, or similar documents.
Warning
- Make sure to save your documents once you're done with your tasks!
