How to Find and Remove Duplicates in Excel

remove duplicates in MS 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 atnxReydYtvWL6VHGjXg2tcVINSAATweMSgLnWIzbK7KBdhpaDX7bVucMSVN4I1XCFgQhZD0W3behE1D1QBHTeihDY4nkd5tdslTeJvLwshMXlhrICFQwq0KG6t8rk2WODsinCbV 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
Highlight Cell Rules
  • From the drop-down box adjacent to values with, select your preferred formatting to apply to the duplicate values
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 –
Duplicate Values Found

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 nxReydYtvWL6VHGjXg2tcVINSAATweMSgLnWIzbK7KBdhpaDX7bVucMSVN4I1XCFgQhZD0W3behE1D1QBHTeihDY4nkd5tdslTeJvLwshMXlhrICFQwq0KG6t8rk2WODsinCbV 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.
Advanced Filter
  • 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.

Accounting Professionals & Specialized Experts

Want quick help from accounting software experts? Get in touch with our team members who can install, configure and configure your software for you. Proficient in fixing technical issues, they can help you quickly get back to work whenever you encounter an error in Sage software. Our team is available 24/7 365 days to assist you. To get in touch.


Do I Need to Take a Backup of the Sage 50 Database before Importing an Excel File into Sage 50?

Yes. Do make sure to have a proper backup of the sage 50 database before importing an excel file into your Sage 50. In case any mistake happens during the import process, you will be able to restore your Sage 50 data easily, from the backup.

What are the Advantages of Using the Excel Integration Module in Sage 50?

Some of the manifold advantages provided by this module are as follows-
You can facilitate other users to access the data without even installing Sage 50
This module includes a number of summaries and reports so that you can make good use of your important business data.
This module facilitates easy assimilation of your account data with other information.
While granting access to other users, Sage would ensure the security of your company data. 
The example sheets embedded in this integration module would provide you with a better understanding of how the module works. 

Will I be Able to Copy my Sage 50 Report Data to a Spreadsheet, in all Versions of Excel?

You must have Excel 2003 or any newer version for this feature of Sage 50 to work properly. Copying of reports to Excel may work with versions before 2003 as well. However, it is not specifically supported by Sage 50.

Related Posts

Further Reading