Find and Delete Duplicates in Excel: A Detailed Overview

Learning to find and delete duplicates in Excel is a common task for people working with large datasets. Suppose you are working on a large dataset for your company. In that case, chances are that there are repeated entries, making your data redundant.

Have you faced the challenge of finding and deleting duplicates on Microsoft Excel? You are not alone. This guide reveals how you can find and delete duplicates in Excel. It also focuses on finding duplicates in Microsoft Excel without deleting them.

A 3D logo of Microsoft Excel.

Understanding the Need To Find and Delete Duplicates in Excel

In today’s world, Microsoft Excel is a robust data management and analysis tool. Yet, we commonly deal with duplicates in large datasets due to human error. This often causes inaccuracy and hinders practical data analysis. Moreover, it is quite challenging to find and delete duplicates in Excel.

One common way to fix this is by deleting rows and columns of repeated records in Excel. However, that process is cumbersome and can be tiring, especially when dealing with huge datasets. So, there is a need to utilize straightforward techniques in finding and deleting duplicates in Excel.

Thankfully, Microsoft Excel provides several built-in features and functions to find and delete duplicates in Excel quickly. Knowing these built-in tools is one thing; utilizing them appropriately is another. Keep reading to learn more about using these tools to navigate various complexities in Excel.

A lady using Microsoft Excel’s features for Data visualization.

3 Methods To Find Duplicates in Excel Without Deleting Them

Before we start to find and delete duplicates in Excel, our first goal should be to identify them within a dataset. Learning to find duplicates in Excel without deleting them is crucial for maintaining data accuracy and ensuring reliable analysis. Let’s discuss some methods that can help find duplicates in Excel.

A team of two collaborating on how to use Microsoft Excel in data analysis effectively.

Use Conditional Formatting To Find and Delete Duplicates in Excel

Conditional formatting is a handy feature in Excel that allows you to highlight duplicate values visually. This feature lets you find and delete duplicates in Excel effectively. The process of using this feature is straightforward; consider the steps below.

  • Navigate to the Home Tab on Microsoft Excel.
  • Click Conditional Formatting in the Styles group.
  • Choose Highlight Cells Rules and Duplicate Values.
  • A dialog box allows you to customize the formatting options in displaying the duplicates.
  • Finally, Excel will automatically highlight the duplicate values within the data sets according to your chosen criteria, making them easily visible.

Employ Excel’s built-in Functions COUNTIF and VLOOKUP

Microsoft Excel provides hundreds of built-in functions you can employ to handle specific tasks within your data sets. Despite the various functions set aside for identifying duplicates with a dataset in Excel, the two common ones are COUNTIF and VLOOKUP.

Let’s explore how these two functions work within a large dataset to find and delete duplicates in Excel.

COUNTIF Function: This built-in function allows you to count the occurrence of a specific value within a range. To find duplicates using COUNTIF, insert a new column next to your dataset and enter the formula “=COUNTIF (range, cell).

Replace “range” with the range of cells you want to check for duplicates. Also, replace “cell” with the cell reference of the value you want to count. Drag the formula down the column to apply it to all the cells. This function will indicate cells with a count more significant than one as a duplicate.

VLOOKUP Function: This built-in function helps you look for a specific value in a range by searching for it vertically across the worksheet in Excel. To use the VLOOKUP function to find duplicates, insert a new column next to your data and enter the formula “=VLOOKUP (cell, range, column, FALSE).

Replace “cell” with the cell reference of the value you want to search. Replace “range” with the range of cells you want to search within. Finally, replace “column” with the column where the duplicates are. When you are through with your input, Click “Enter.” 

These two built-in functions are helpful to find and delete duplicates in Excel. 

 A man checking out valuable features on Excel to improve his work productivity.

Use the Filter Function

Another helpful technique to find duplicates in Excel without deleting them is using Excel’s filter function. This function allows you to display only the duplicate values with a column. To use the filter function, follow these easy steps below.

  • Select the range of cells containing your data.
  • Navigate to the Data Tab and click Filter in the Sort & Filter group.
  • Open the dropdown menu for the column you want to check for duplicates.
  • Uncheck the “Select All” option and scroll down to select “Duplicates.
  • Excel will filter the column to display the duplicate values.

Any of these three techniques can help you quickly find duplicates easily within your dataset on Excel. What can you do if you don’t need the duplicates anymore? Let’s consider how to find and delete duplicates in Excel.  

How To Find and Delete Duplicates in Excel To Obtain Clean and Reliable Data

While we cannot deny Microsoft Excel’s versatility in data analysis, we need to work to ensure we provide clean and reliable data. To do this effectively, we must find and delete duplicates in Excel. Several methods of deleting these duplicates in Excel ensure you maintain your data structure and improve your workflow overall. Let’s consider some of these methods you can employ to find and delete duplicates in Excel.

H3: Employ the Duplicate Remover Feature To Find and Delete Duplicates in Excel

One of the effective ways you can find and delete duplicates in Excel is by utilizing its built-in feature – “Remove Duplicates.” This feature lets you quickly delete duplicate values within a range of cells. Here is how to use it:

  • First, select the range of cells that contain the data you want to work with.
  • Go to the data tab and click “Remove Duplicates” in the data tools group.
  • A dialog box opens, where you can find duplicates based on specific columns or the entire selected range.
  • Select the columns or cells you want to consider for duplicate detection and deletion. Afterward, click OK to remove the duplicate values, keeping unique entries.

Four members of an organization analyzing company data with Microsoft Excel.

Use the Advanced Filter Option To Find and Delete Duplicates in Excel

Another straightforward method to find and delete duplicates in Excel is the use of its advanced filter option. The advanced filter option offers more control over duplicate content by not deleting them entirely. Instead, this option hides duplicates within your dataset. 

Follow these steps to utilize the advanced filter option to find and delete duplicates in Excel effectively:

  • Select a cell within your dataset and navigate to the data tab.
  • On this tab, click “Advanced Filter” on the far right. It will open the advanced filter dialog box.
  • Choose the “Copy to location” option. This option will make a copy of the data where you can specify its location on your PC. Alternatively, you can choose “Filter the list, in-place,” which hides any row with duplicates in a given cell range.
  • Afterward, check the “Unique records only” box.
  • Click OK to apply the filter and copy only the unique values to the specified location.

Finding and deleting duplicates in Excel is highly efficient when accessing the original data with separate data of unique values.

Use the Excel Formula To Find and Delete Duplicates in Excel

You can use the Excel formula to find and delete duplicates in Excel. One powerful formula you can utilize is the “COUNTIF” duplicates. Here is how to use this Excel formula to delete these duplicates.

  • Insert a new column next to the column containing duplicates.
  • In the first cell of the new column, input the formula” =IF(COUNTIF (range, cell) > 1, cell).” Replace “range” with the range of cells you want to check for duplicates and “cell” with the cell reference of the value you want to evaluate.
  • Drag the formula from the reference cell to all cells in the column.
  • Copy and paste the new column as new values over the original column.
  • Finally, delete the new column.

While this Excel formula cannot hide duplicates, they effectively remove them in Excel. Overall, using the three methods above helps find and delete Excel duplicates. Thus, they can efficiently clean your datasets and ensure accurate analysis.

A delivery team of two men and a lady working round the clock and sorting important data for their organization.

How To Find and Delete Duplicates in Excel on Multiple Columns

Several powerful built-in features can help you find and delete duplicates in Excel. While dealing with large datasets, we could face a problem where multiple columns contain duplicates.

Deleting individual duplicate rows can be strenuous and time-consuming. As such, Excel comes to the rescue by offering an advanced feature – Power Query. This feature lets you select the columns with duplicates and removes the duplicate from them. Let’s discover how Power Query can effectively find and delete duplicates in Excel worksheets contained in multiple columns.

  • In Excel’s landing page, navigate to the data tab and click Get Data.
  • Select Excel Workbook as your data source.
  • Then, you browse your files to select the spreadsheet you want for the advanced function, Power Query. Afterward, click “Next.”
  • Tick the checkbox next to the spreadsheet containing your data and click “Load” in the bottom right corner.
  • Excel will automatically convert your dataset into a table. Then, select the columns containing duplicates within your data set. You can do this effectively by using the Ctrl + the columns.
  • Once you have selected it, navigate to the data tab. Then, click “Remove Duplicates.
  • A dialog box will pop up; select “OK.” Excel will begin its work in finding and deleting the duplicates in the datasets.
  • Finally, An Excel prompt will notify you of the duplicate values it found and removed and the number of unique values remaining.

Using the Power Query function is a copy of your original worksheet. So, you needn’t worry about removing the duplicates entirely. Alternatively, if you want to keep unique values, follow the steps outlined for the advanced filter option.

A team of five discussing how to use Microsoft Excel to deliver better services.

Final Thoughts: Get the Best Data Visualization and Analysis With Mapize

About The Author

Start creating map

Drag and drop your file (xls, xlsx or csv) Use our sample data, by clicking here. Or
AddressCityStateZipcodeNamePhone NumberGroupURLEmail
12032 SE Sunnyside rdClackamasOR97015Safeway Clackamas(503) 698-1121Group Bhttps://local.safeway.com/safeway/or/clackamas/12032-se-sunnyside-rd.html[email protected]
4715 SE Cesar Estrada Chavez BlvdPortlandOR97202Trader Joe's Portland SE(503) 777-1601Group Chttps://locations.traderjoes.com/or/portland/143/[email protected]
3380 Lancaster Dr NESalemOR 97305Safeway Salem(503) 399-1502Group Bhttps://local.safeway.com/safeway/or/salem/3380-lancaster-dr-ne.html[email protected]
12220 SW Scholls Ferry RdTigardOR97223Whole Foods Market Greenway(971) 371-7000Group Ahttps://www.wholefoodsmarket.com/stores/greenway[email protected]
8145 SW Barbur BlvdPortlandOR97219Safeway Barbur(503) 452-6068Group Bhttps://local.safeway.com/safeway/or/portland/8145-sw-barbur-blvd.html[email protected]
1550 NW 9th St Ste 100CorvallisOR97330Trader Joe's Corvallis(541) 753-0048Group Chttps://locations.traderjoes.com/or/corvallis/154/[email protected]
1210 NW Couch StPortlandOR97209Whole Foods Market Pearl(503) 525-4343Group Ahttps://www.wholefoodsmarket.com/stores/pearl[email protected]
353 E Broadway EugeneOR97401Whole Foods Market Eugene(541) 434-8820Group Ahttps://www.wholefoodsmarket.com/stores/eugene[email protected]
3527 SE 122nd AvePortlandOR97236Safeway 122nd(503) 760-5448Group Bhttps://local.safeway.com/safeway/or/portland/3527-se-122nd-ave.html[email protected]
9940 NE Cornell RdHillsboro OR97124Whole Foods Market Tanasbourne(503) 645-9200Group Ahttps://www.wholefoodsmarket.com/stores/tanasbourne[email protected]
15391 Bangy RdLake OswegoOR97035Trader Joe's Oswego(503) 639-3238Group Chttps://locations.traderjoes.com/or/lake-oswego/142/[email protected]
2490 OR-99WMcMinnivilleOR97128Safeway McMinniville(503) 435-3120Group Bhttps://local.safeway.com/safeway/or/mcminnville/2490-n-hwy-99-w.html[email protected]
4121 NE Halsey StPortlandOR97232Trader Joe's Portland Hollywood(503) 284-4232Group Chttps://locations.traderjoes.com/or/portland/144/[email protected]
Drag and drop your file here
- OR -
Copy + paste your data include column files
AddressCityStateZipcodeNamePhone NumberGroupURLEmail
12032 SE Sunnyside rdClackamasOR97015Safeway Clackamas(503) 698-1121Group Bhttps://local.safeway.com/safeway/or/clackamas/12032-se-sunnyside-rd.html[email protected]
4715 SE Cesar Estrada Chavez BlvdPortlandOR97202Trader Joe's Portland SE(503) 777-1601Group Chttps://locations.traderjoes.com/or/portland/143/[email protected]
3380 Lancaster Dr NESalemOR 97305Safeway Salem(503) 399-1502Group Bhttps://local.safeway.com/safeway/or/salem/3380-lancaster-dr-ne.html[email protected]
12220 SW Scholls Ferry RdTigardOR97223Whole Foods Market Greenway(971) 371-7000Group Ahttps://www.wholefoodsmarket.com/stores/greenway[email protected]
8145 SW Barbur BlvdPortlandOR97219Safeway Barbur(503) 452-6068Group Bhttps://local.safeway.com/safeway/or/portland/8145-sw-barbur-blvd.html[email protected]
1550 NW 9th St Ste 100CorvallisOR97330Trader Joe's Corvallis(541) 753-0048Group Chttps://locations.traderjoes.com/or/corvallis/154/[email protected]
1210 NW Couch StPortlandOR97209Whole Foods Market Pearl(503) 525-4343Group Ahttps://www.wholefoodsmarket.com/stores/pearl[email protected]
353 E Broadway EugeneOR97401Whole Foods Market Eugene(541) 434-8820Group Ahttps://www.wholefoodsmarket.com/stores/eugene[email protected]
3527 SE 122nd AvePortlandOR97236Safeway 122nd(503) 760-5448Group Bhttps://local.safeway.com/safeway/or/portland/3527-se-122nd-ave.html[email protected]
9940 NE Cornell RdHillsboro OR97124Whole Foods Market Tanasbourne(503) 645-9200Group Ahttps://www.wholefoodsmarket.com/stores/tanasbourne[email protected]
15391 Bangy RdLake OswegoOR97035Trader Joe's Oswego(503) 639-3238Group Chttps://locations.traderjoes.com/or/lake-oswego/142/[email protected]
2490 OR-99WMcMinnivilleOR97128Safeway McMinniville(503) 435-3120Group Bhttps://local.safeway.com/safeway/or/mcminnville/2490-n-hwy-99-w.html[email protected]
4121 NE Halsey StPortlandOR97232Trader Joe's Portland Hollywood(503) 284-4232Group Chttps://locations.traderjoes.com/or/portland/144/[email protected]
Copy + paste your data
Configuration

Map Fields

+ Add Custom Field
Show More

    Oh no!

    On the free tier you can only map up to 150 locations at one time. Please add a plan if you wish to map more than 150 locations.

    Related Articles

    • A company notices a reduction in operating costs after optimizing its delivery routes.
      Plan the Most Efficient Route: A Guide to Smarter Navigation

      Efficient route planning is essential for businesses and individuals alike. It can save time and ensure you make the most of available resources. But how do you plan the most efficient route? Where should you start, and what factors do you consider? Also, which mapping software should you use for this type of planning? The […]

    • A person enters geographic coordinates to find a specific location on Google Maps.
      Plot Coordinates on Map: Find Places With Pinpoint Accuracy

      One of the first things you must know when using mapping software is to plot coordinates on map. It forms the basis for visualizing geospatial datasets, making it easier to analyze large volumes of information. If you’re unfamiliar with how to do this on any mapping software, this article has you covered. Below, you’ll find […]

    • A person makes a postal code map using Google My Maps on their Macbook Pro.
      Zip Code Maps: Get Deep Insights From Numeric Identifiers

      Generally, zip codes improve the efficiency of mail delivery. However, you can do a lot more with these government-issued numbers. You can create zip code maps, which have a lot of use cases. For example, businesses rely on these charts to make various important decisions.  If you’re new to cartography or familiar with various mapping […]

    • How To Create a Custom Map in Google Maps

      Google Maps has become synonymous with navigation—you can find almost any place on this planet. Not only do you get turn-by-turn directions, but it also contains extensive information about the location. Usually, people use this tool to get from one point to another. But what if you want to do something more with this mapping […]