How to show duplicates in Excel

Excel users frequently want to show rows that have duplicate values in one or more columns.

This is easy to do in Easy Data Transform:

1. Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
2. Start Easy Data Transform.
3. Drag the Excel spreadsheet you wish to show duplicates for onto Easy Data Transform. If there is more than one worksheet you will be asked to choose which ones to input - choose the one with the duplicates. A pink input item will be added.

4. Ensuring the pink input item is selected, click the Count button in the Left pane. A blue transform item is added and selected.

   

5. In the Right pane select the column you want to show duplicates for.

   

6. Ensuring the blue Count transform item is selected, click the Filter button in the Left pane. Another blue transform item is added and selected.

   

7. Set the filter to show only rows where Count is greater than 1.

   

All the duplicates are now listed, along with the count of how many times they appear. No need to ‘run’ anything. If you want to look for duplicates across multiple columns (e.g. same first name And last name) you can use the Concat Cols transform to combine these columns before the Count.

Note that duplicate checking is sensitive to case (unless case sensitive is unchecked) and white space. You can use the Whitespace transform before the Count to modify whitespace. For more information on Dedupe options see the Dedupe documentation.

When you are satisfied with the results you can output the transformed data to a new file in a range of file formats. Ensuring that the blue transform item is selected, click the To File button at the bottom of the Left pane to create a new output file (you might have to scroll down to see the button).

If you need to show duplicates in lots of Excel sheets you can do it in a single operation using the batch processing feature.

Easy Data Transform allows you to combine 56 different transforms to quickly and easily manipulate your data. You can also use it to remove duplicate rows in Excel. Easy Data Transform can input and output in multiple formats, and process millions of rows.