How to Find and Remove Duplicates in Excel

Out of several important features embedded in Sage 50, the capability to integrate with MS Excel is used extensively by businesses and professionals worldwide. With this useful feature, you can be saved from entering data manually from an excel spreadsheet and thus, can save a lot of time.

At times, while using Excel, the presence of duplicate data makes it harder for a user to understand the data. In this blog, we will look at how you can remove duplicates in MS Excel.

Before that, let’s turn our attention to how you can find and highlight duplicate data in Excel. Before removing them, you can also review them once. 

May Be Help You : Connecting Sage 300 CRE with Excel Using Office Connector

What Is the Procedure to Find Duplicates in MS Excel?

Please follow the steps below –

• Open MS Excel
• Select the cells you need to check for duplicate entries
• Click Home
• Go to the Conditional Formatting section
• Click on Highlight Cells Rules
• Now click on the Duplicate Values tab

• From the drop-down box adjacent to values with, select your preferred formatting to apply to the duplicate values

What is the Procedure to Remove Duplicates in MS Excel?

When you remove duplicate entries from Excel, the duplicate data will be deleted permanently. Therefore, we would suggest you copy the original data into another worksheet, before proceeding to delete the duplicates. This will save you from the accidental loss of any crucial information.

Please follow the steps below to remove duplicates in Excel –

• Open MS Excel
• Select the range of cells with the duplicate values you need to remove.

Please note: make sure to remove subtotals or outlines from your data before proceeding to remove duplicates

• Click Data
• Go to Remove Duplicates
• Click on the Columns section
• check or uncheck the columns from where you need to remove the duplicates
• Click Ok to finish
• Excel will delete the duplicate rows and will display a dialog box. The box will show a summary of how many duplicate values are removed, with the precise count of unique values. It will look like –

Remove Duplicates in Excel by using the Advanced Filter Option

The Advanced Filter option helps you filter duplicate values. You can then copy these unique values to a different location altogether. Here are the steps to follow in this regard –

• Click on a cell or range of data from which you need to remove duplicates. If you click on a particular cell, Excel would automatically determine the range, when you click Advanced Filter
• To locate the Advanced Filter option, go to Data
• Click on the Sort & Filter section
• Click Advanced 

• You will find a dialog box, containing a list of options for advanced filtering.
• Click on the Copy to another location option 
• Verify the range of records in the List Range field 
• Go to the Copy to: field
• Enter the range where the unique values must be copied.
• Checkmark the Unique records only box.

• Click OK to proceed
• The unique values will be copied to the location you specified

Also Read: Copy Sage 50 Report Data to a Microsoft Excel Spreadsheet

We hope the simple procedures discussed above would help you find and remove duplicates in MS Excel with ease. If you have any difficulties in performing these steps, or, if you have any queries, you may consult with some of the seasoned Sage experts 1800 964 3096.

FAQs