How to find duplicates and uniques in Excel

Duplicate Remover for Excel is a 5-step way to quickly remove, highlight, select, or move repeated or unique cells in Excel lists and duplicate rows in tables. Here you will find the instructions on how to use the tool.

Before you start

We care about your books and always suggest creating backup copies of your files by selecting the corresponding option in the add-in.

How to use Duplicate Remover

Start the wizard

Once you open your Excel workbook with the table you need to check for duplicate or unique values, click the Duplicate Remover icon in the Dedupe group on the Ablebits Data tab to start the add-in:
Open Duplicate Remover for Excel.

Tip. You can pre-select the type of data to look for and jump right to step 3 by clicking on the down arrow below the add-in's icon:
Choose to find duplicates or uniques right from the start.

Step 1. Select a table

First, you choose the range to dedupe.
Select Excel list to dedupe.
Click any cell in your table and get the whole dataset highlighted automatically. You can change the range manually by editing it in the Select your table field, clicking the Select range icon, or simply by selecting the needed cells in Excel.
Pick the range to find duplicate cells.

Note. We strongly recommend checking the option to Create a backup copy of the worksheet as Excel doesn't let you cancel changes made by add-ins.

When the needed range is picked, click Next.

Step 2. Choose the type of data to look for

Unless you use the drop-down list on the toolbar, this is the step where you choose the type of data to find:
Find and remove duplicates or uniques.

Tip. If you select a wrong option, you can always return and choose another by clicking the Back button.

Press Next to continue.

Step 3. Pick columns to check for duplicates and uniques

This step displays a list of columns with their headers as they are in your worksheet:
Pick columns with duplicates to dedupe Excel list.
You can take advantage of the additional options:

  • Unless your range is formatted as a table in Excel, you can let the add-in know if the first row contains labels with the My table has headers option at the top. If you have more than one header rows, click the "1 header row" phrase and enter the number of header rows your table has.
  • If you have empty cells in your Excel table, tick the Skip empty cells option to exclude them from the search results.
  • If text case matters, tick the Case-sensitive match box. With the checked box, the same text written in different cases ("Text" and "text", for instance) will be considered as different text.
  • If your range has no labels, look at the 1st row content area to make sure you choose the right columns.
  • All columns are selected by default. If you have a lot of columns in your table, take advantage of the top checkbox next to Columns or use the Select All and Unselect All buttons to quickly clear the selection and specify only the records you want to check.
Note. If you select more than one column, the add-in will look for duplicated rows. In other words, only those records that have the same values in both columns will be considered duplicates. If your purpose is to compare column A with column B, please use the Compare Tables tool instead with column A as your first table and column B as your second table.
Tip. If you have a long list of columns, you can make the wizard window bigger by dragging the bottom-right corner down and to the right.

When the columns are selected, click Next.

Step 4. Choose what to do with the found entries

On the final step, you are to select the action that will be applied to the found Excel duplicates and uniques:
Identify duplicates and uniques in Excel, remove or select them.

  • You can have the rows with duplicate values deleted.
  • Highlight results with color to quickly see all the duplicated rows. You can pick the color from the drop-down list next to the option.
    This is how you can Highlight duplicate rows.
  • Select the found values to be able to work with them further.
    Here is how you can quickly Select duplicate and unique values in Excel.
  • Mark the found dupes or uniques in the Status column in your Excel list.
    What is the status column? See it in the Identify duplicate rows in a special column video.
  • Copy or move the results to another location (a new workbook, new or existing worksheet).

Click Finish.
Highlight duplicates in Excel.

Responses

george bester says:
July 8, 2018 at 3:55 pm

empty cells are not skipped even after clear ll

Reply

Hi George,
Thank you for using our product and for your message. Please contact our support team at support@ablebits.com and describe the problem in detail. It would be perfect if you could attach your book and indicate the version of Ultimate Suite you are using (to find it, go to the Ablebits Data tab, find the Help icon on the left and click the About… option in the drop-down menu).

Reply
Jennifer Phelps says:
January 24, 2019 at 9:31 pm

We upgraded to Office 365 yesterday and now I'm unable to compare 2 tables as before.
It also tells me i am on a "trial"

Please advise asap

Thank you,
Jennifer

Reply

Hi, Jennifer,
I am sorry you are facing such problems with our product. For us to be able to assist you better, please contact our support team at support@ablebits.com. In your message, please specify your current license key. If you get any error message, please attach its screenshot. We will help you asap.
Thank you.

Reply

Hi,

Is there a version of the duplicate remover for MAC?

Thanks!

Reply

Hi Tegan,

Thank you for your interest in our product.

Duplicate Remover is designed for Windows only and doesn't work on Mac.

However, you can have a look at Remove Duplicates for Google Sheets that has similar functionality and can be run in a browser on your Mac. The add-on is installed and purchased directly through Google Sheets. You can test it out for 30 days for free to see if it suits your needs.

Reply

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard