• About
  • Contact
  • Privacy Policy
  • Submit For Review
  • Template Gallery
  • Excel 2016 Udemy Course
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Mobile Apps
  • Software and Web Apps
  • Buying Guides
  • Shop
  • Show Search
Hide Search
Home » How To » Remove Duplicate Rows in Excel 2010 in 4 Super Quick Ways

Remove Duplicate Rows in Excel 2010 in 4 Super Quick Ways

November 4, 2014

When you are pulling together data from various sources onto one Excel sheet, you tend to create duplicate rows of data. If you only have a small set of data to work with, you can delete them manually, but when the data is voluminous, this won’t go be a pleasant task. Imagine the hours of lost productivity, just trying to fix your datasheet. Thankfully, Excel offers you a number of ways in which you can remove duplicate rows and in this tutorial. I’ll be discussing 4 of them. You can make use of this data sheet in case you want to test it out.

Find and Remove Duplicate Rows Using Remove Duplicates Function

Excel has a straightforward command that lets you find and remove duplicate cells in a worksheet at the tap of a button. To do this:

1. Open up the worksheet with the duplicated data.

2. Select the data by pressing Ctrl+A or by clicking and dragging across the worksheet. In my example, I want to identify and remove the years that have been duplicated.

3. On the Data tab, in the Data tools group, click Remove Duplicates.

remove duplicates command in excel 2010

4. Since there is data in the adjacent column, you will receive a warning message to expand the selection or continue with the current selection. Select Continue with the current selection and click Remove Duplicates.

remove duplicates warning

5. In the next screen, you will notice options to choose the columns where you want to detect and clear duplicate values. Since the focus here is on removing the duplicate years, I’ll uncheck Item and click Ok.

remove duplicates

6. You will receive a message that n number of duplicate values were found and removed. You will also be notified on the number of unique values left in the column. Click Ok to close the dialog.

success message

Method 2: Remove Duplicates Using Advanced Filter

1. Open up your worksheet with duplicated data and select all the data by pressing Ctrl+A.

2. Now on the Data Tab, in the Sort & Filter group, click Advanced.

sort & filter advanced filter

3. In the Advanced Filter dialog box, check the Unique records only check box and click OK.

advanced filter

4. You won’t get a message confirmation, but your duplicates should be removed now. In this method, Excel assumes that your first row is a header, so you will have to check and remove the duplication in the first (header) row.

ALSO READ:  How to Filter Alternate Rows in Excel

Method 3: Using Find and Replace Functionality

There’s yet another rather easy method to remove duplicates and that’s using the Find and Replace functionality. This one’s ideal if you are having a smaller worksheet to work on.

1. Open up your worksheet containing the duplicated data.

2. Select the cell containing the data that is duplicated and copy its contents. (Ctrl+C)

3. Press Ctrl+H on your keyboard and paste the content in the Find what text field.

4. Now press the Options button.

5. Check the Match entire cell contents checkbox to ensure that you are not accidentally removing the content that is present elsewhere n the worksheet.

find and replace

6. In the Replace text box, press the Spacebar to replace the cell contents with a blank space. That will indicate to you that a duplicate from that cell has been removed. Press Replace All or Replace until you’ve removed the values in the order in which you want.

7. After replacing all the duplicate values, close the Find and Replace dialog box and in your worksheet filter out the blank cells and remove them by right-clicking and pressing Delete.

Method 4: Using Conditional Formatting

This is another quick way to identify duplicate rows in Excel and is handy when you are working with large data.

1. Open up your worksheet and select the data.

2. On the Home tab, in the Styles group, click Conditional Formatting, click Highlight Cells Rules and then select Duplicate Values.

Remove Duplicate Rows using conditional formatting

4. In the Duplicate Values dialog, select a format option to highlight the duplicate data and click Ok.

You can instantly see the data that is duplicated and can remove them as you see fit.

In this tutorial, we showed you four different ways to remove duplicate rows in Excel so you can pick one that works best for you.

If you enjoyed this tutorial then you will also find the tutorial focused on removing spaces in excel cells.

Tweet
Share4
WhatsApp
Pin3
Buffer
7 Shares

Filed Under: How To Tagged With: excel

You May Also Like

How to Delete Blank Rows in Excel 2016
How to Delete Blank Rows in Excel 2016
excel 2010 tutorials
How to Create Summary Worksheets in Excel 2010
How To Make Important Business Decisions Using Excel

About Adeline Gear

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Hottest New Gadgets!

Fast External SSD Gadgets for your Mac - TATFI

4 Fast External SSD Gadgets for your Mac (2021)

Anker Powerhouse 100 - TATFI

Charge Your iPhone, Galaxy, iPad, or MacBook with the Portable Power Bank Anker Powerhouse 100

Logitech Circle View - TATFI

Logitech Circle View – A Great Wired Outdoor Camera for HomeKit Users

Spigen Galaxy S21 Cases - TATFI

Spigen Galaxy S21 Cases is Perfect for New Samsung Smartphone

Samsung Galaxy SmartTag Bluetooth Tracker - TATFI

Find Lost Items with the Samsung Galaxy SmartTag Bluetooth Tracker

Popular Tags

android android apps android games android how to apple chrome extensions excel excel how to facebook gadgets gaming gmail google instagram ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft music apps new releases outlook how to photo apps powerpoint powerpoint how to productivity Samsung security smartphone smartphones software tablet tablets technology twitter web browsers windows 10 word how to xbox youtube

Disclosure

TheAppTimes is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.

TheAppTimes © 2021 // Runs on StudioPress