When working with large amounts of data in Microsoft's Excel spreadsheet program, you may encounter many duplicates. The Conditional Formatting function of the Excel program can show exactly where the duplicates are, and the Remove Duplicates function can remove all duplicates for you. Finding and removing duplicates gives you the most accurate data results possible.

step Method 1 Use the Conditional Formatting feature

1. Open the original file. The first thing you need to do is select all the data that you want to use to compare duplicates.

2.Click the cell in the upper left corner of the data group to start the data selection operation.

3. Hold down the Shift key and click on the last cell. Note that the last cell is located in the lower right corner of the data set. This will select all of your data.

You can also select cells in a different order (for example, click the bottom right cell first, and mark select other cells from there).


4. Click "Conditional Formatting". It's located under the "Home" tab of the toolbar (usually in the "Styles" section). [1] Delete key to delete them.

If each data represents a different meaning (such as: survey results), then you cannot delete these values.

When you delete duplicate values, the data paired with them will lose their highlighting.


5. Click "Conditional Formatting" again. Whether or not you remove duplicates, you should remove the highlighting before exiting the document.


6. Select "Clear Rules" and then "Clear Rules for Entire Worksheet" to clear the formatting. This will remove the highlighting of the remaining duplicates. [4] Shift key, click the last cell.

The last cell is located at the lower right corner of the data set. This will select all of your data.

You can also select cells in a different order (for example, click the bottom right cell first, and mark select other cells from there).


7.Click the "Data" tab at the top of the screen.

8.Find the "Data Tools" section in the toolbar. There are several tools in this section for manipulating the selected data, including the "Remove Duplicates" feature.

9.Click "Remove Duplicates." This will open the customization window.

10.Click Select All. This will confirm the selection of all data columns in the table.

11.Check the fields for the data you want to use the tool with. The default setting is to check all data columns.

12. If appropriate, click the "Data contains headers" option. This will cause the program to mark the first item in each column as a header, and then exclude them from delete operations.

13. Click OK to remove duplicates. If you are satisfied with the settings, click "OK". The program will automatically delete the duplicate values in the selected part. If the program tells you there aren't any duplicates - and you're sure there are duplicate values - try ticking individual columns of data in the "Remove Duplicates" window. Browse the exclusion results for each column to see where the problem lies.

14. Save the changes to the document. At this point, you have successfully found and deleted the duplicates in the worksheet!

hint

You can also install a third-party load utility tool to identify duplicate values. These utilities enhance Excel's conditional formatting capabilities, allowing you to use multiple colors to identify duplicate values.

When browsing attendance lists, address directories, or similar documents, use the method in this article to remove duplicates.