Often, while entering large datasets on spreadsheets, you might have a feeling of deja vu while entering duplicate data. But when you are working with a large file with hundreds of rows and columns, it will be very difficult to find and remove the duplicate data manually. Not only will the process be time-consuming and effort-intensive, but it may also be prone to errors. What will you do then? We are here to the rescue! In this blog, we will suggest some quick and easy methods to find duplicates in Excel and remove them to have an error-free Spreadsheet.
So are you ready to know all the quick and easy tips? Let’s dive in and learn how to find duplicates in Excel.
How to Find Duplicates in Excel
1. Conditional Formatting
The easiest way to find duplicates in Excel and highlight them is through Conditional Formatting. All you need to do is follow these easy steps:
- Step 1: Open the Excel sheet and select the cell range you want to review for duplicate data.
- Step 2: Select Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
- Step 3: A Dialogue Box appears. Choose a formatting style and color scheme to highlight the duplicates.
- Step 4: Click on Done.
The duplicate data will be highlighted on your Spreadsheet and you can remove and modify it as required.
2. Advanced Filter Feature
The Advanced Filter is another convenient way, not just to find and highlight, but to filter the data and get a unique set of results. Check out the following steps:
- Step 1: Select the cell you want to check duplicates for.
- Step 2: Select Data Tab> Advanced Filter from the Sort and Filter group.
- Step 3: From the Action Section, click on Copy to Another Location.
- Step 4: Check the List Range to ensure the correct cell range is selected w.r.t the original data. For example, we have selected the cell range from A1 to C12.
- Step 5: In Copy To Control, enter the copy range. For example, we have selected to print the results on H1 to J12.
- Step 6: Select Unique Records Only, Press OK.
Excel will provide a new table with a filtered list of unique records, and you can make the required changes in your original table.
How to Remove Duplicates in Excel
Once you find duplicates in Excel, you must know how to remove them as well. Manually removing and modifying data on Spreadsheets can be very time-consuming and effort-intensive. But we will tell you some ways to do this very easily. So let’s get started!
1. Using Remove Duplicates Feature
Follow these simple steps:
- Step 1: Select the cell with the data you want to check duplicates for.
- Step 2: Select Data tab > Remove Duplicates.
- Step 3: A Remove Duplicates Dialogue Box appears listing the columns that you have on the table. Choose the columns to check for duplicate values and remove them. Click OK.
- Another Dialogue Box will show you how many duplicate values were removed. Click OK.
The Duplicate values will be removed from your Spreadsheet.
Pro Tip: Always save a copy of the original spreadsheet on a separate file. If your spreadsheet has valuable information, it will stay as a record and you might need it later.
2. Using Formulae to Find and Remove Duplicates in Excel
A. IF() and COUNTIF()
The COUNTIF function is used to count cells in a range that meets the specific criteria It is especially helpful if you want to count cells with a specific value or a range of values, and that is why we can use it to find duplicates in Excel.
Remember: This is not a direct formula to remove duplicates, but it will help you find duplicates in Excel and then you can remove them from the table and save them separately.
- Step 1: Find the duplicate values from the table using this simple formula =IF(COUNTIF($A$2:$A$12,A2)>1,”DUPLICATE”,”UNIQUE”). The results will show the Duplicate values as “DUPLICATE”, and the unique values as “UNIQUE”.
- Step 2: You can follow this method for individually checking the value of each cell, or click on the corner of the cell to get the plus (+) symbol, and pull your mouse down to the end of the list. This will give you the value of all the cells quickly.
B. UNIQUE()
The UNIQUE function in Excel is used to return a list of unique values from a range or array. However, it does not replace your data, rather it provides a new list on the same spreadsheet sans the duplicate values. Follow these simple steps to use the UNIQUE function to find duplicates in Excel:
- Step 1: Click on an empty column on the Spreadsheet.
- Step 2: Enter the UNIQUE function including the cell range that you want to check for duplicates, and get unique values as a result. For example, we have entered =UNIQUE(A2:C12)
- Step 3: You will get a new table on the same spreadsheet with only the unique values. You can save it separately on another Excel file.
Final Thoughts
In this blog, we have discussed some simple as well as expert methods to find duplicates in Excel and also remove them, and have an error-free Spreadsheet. Manually removing this data and modifying them will be very time-consuming and might be prone to errors, especially for large datasets and high-value projects. That is why we have provided some quick and easy solutions to make the process easier and quicker for you.
Srijita Datta is a content writer with over 4+ years of experience in various niches. Her active interest in exploring new and emerging SaaS software and the evolving world of AI motivates her to write crisp, engaging, and informative articles to share her knowledge. She is working with How to Buy SaaS as a content writer and curating fresh and unique content every week with an added expertise in SaaS content.
