Pinterest Pixel

Remove Duplicates in an Excel Table

John Michaloudis
There is a quick and easy way to Excel Remove Duplicates from Table! The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them.
Not to worry, Excel Table Remove Duplicates to the rescue!

There is a quick and easy way to Excel Remove Duplicates from Table! The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry, Excel Table Remove Duplicates to the rescue!

Key Takeaways

  • Built-in Remove Duplicates Tool – Excel provides a built-in “Remove Duplicates” feature under the “Data” tab, allowing you to quickly eliminate duplicate rows based on selected columns.
  • Column Selection Matters – When removing duplicates, selecting specific columns ensures that Excel checks for uniqueness based only on the relevant data fields.
  • Data Formatting Doesn’t Affect Removal – The “Remove Duplicates” tool works based on values, meaning formatting differences (such as font or color) do not impact duplicate identification.
  • Original Data Can Be Preserved – Before removing duplicates, it’s good practice to create a backup of your data in case you need to revert changes.
  • Power Query for Advanced Deduplication – For more complex duplicate removal, Power Query allows you to filter and clean data dynamically without permanently altering the original dataset.

Remove Duplicates

STEP 1: Click inside your Excel Table and select Table Tools > Design > Remove Duplicates

Remove Duplicates in an Excel Table

STEP 2: This will bring up the Remove Duplicates dialogue box.  Select only the Column box that contains the duplicates that you want to remove and press OK

Remove Duplicates in an Excel Table

Your duplicates are now removed!

Remove Duplicates in an Excel Table

 

Highlight Duplicates using Conditional Formatting

Instead of simply removing duplicates from the list, you may sometimes require to highlight duplicate values from a range.

Let’s see how it can be done:

STEP 1: Select the column containing customer name.

Remove Duplicates in an Excel Table

STEP 2: Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.

Remove Duplicates in an Excel Table

STEP 3: In the dialog box, Click OK.

Remove Duplicates in an Excel Table

All the duplicate values in the customer column will be highlighted in red!

Remove Duplicates in an Excel Table

 

Copy Unique List to New Location

If you wish you remove duplicates and copy the list of unique values in a new location without making changes to the existing table, follow along.

STEP 1: Go to Data > Under Sort & Filter > Select Advanced.

Remove Duplicates in an Excel Table

STEP 2: In the Advanced Filter dialog box,

  • Under Action, select Copy to another location
  • Under List Range, select the customer column
  • Under Copy to, select cell where you want to paste the unique customer list
  • Check Unique records only
  • Click OK

Remove Duplicates in an Excel Table

The unique list of customers will be copied to cell I6.

Remove Duplicates in an Excel Table

Frequently Asked Questions

How do I remove duplicates from an Excel table?

Select your table, go to the “Data” tab, click “Remove Duplicates,” choose the columns to check for duplicates, and press OK. Excel will delete duplicate rows while keeping the first occurrence.

Can I undo the removal of duplicates if I make a mistake?

Yes, you can press Ctrl + Z immediately after removing duplicates to restore your original data. If you’ve saved your file, having a backup before performing the action is recommended.

Does removing duplicates delete all instances of the duplicate data?

No, Excel keeps the first occurrence of the data and removes all subsequent duplicates. If you need all duplicates removed, consider using advanced filtering or Power Query.

Can I remove duplicates based on multiple columns?

Yes, the “Remove Duplicates” tool allows you to select multiple columns, and Excel will only remove rows where all selected columns have identical values.

How can I highlight duplicates before deleting them?

Use Conditional Formatting by selecting your data, going to “Home” > “Conditional Formatting” > “Highlight Cell Rules” > “Duplicate Values” to visually identify duplicates before removing them.

Conclusion

You can easily use either of the three options to remove duplicates from table Excel.

You can remove duplicates from the existing table or highlight duplicates or copy a list of unique values to a new location!

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Free Microsoft Excel Online Course - 20+ Hours Beginner to Advanced Course

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...