{"id":1376,"date":"2022-12-23T15:42:56","date_gmt":"2022-12-23T15:42:56","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=1376"},"modified":"2024-02-28T20:01:34","modified_gmt":"2024-02-28T20:01:34","slug":"remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/remove-duplicates-in-excel\/","title":{"rendered":"How to Remove Duplicates in Excel?"},"content":{"rendered":"

Remove Duplicates in Excel to ensure your data is clean, accurate, and reliable. This essential feature is key to effective data management, enabling you to streamline databases, enhance analytical accuracy, and improve overall decision-making processes. By eliminating redundant information, you can focus on unique, valuable data points that drive insightful analyses and strategic outcomes. Embrace the power of Excel to transform cluttered datasets into organized, actionable information, paving the way for clearer insights and more efficient operations.<\/p>\n

This Tutorial Covers<\/strong>:<\/strong><\/p>\n

    \n
  1. Remove duplicates in Excel by built in feature<\/strong><\/a><\/li>\n
  2. Understand Filtering for Unique Values or Removing Duplicate Values<\/strong><\/a><\/li>\n
  3. Using Advanced Filter Option<\/strong><\/a><\/li>\n
  4. How to use Formulas to Remove Duplicates<\/strong><\/a><\/li>\n
  5. Conditional Formatting to identify and remove Duplicate Values<\/strong><\/a><\/li>\n<\/ol>\n

    1. Remove duplicates in Excel by built in feature<\/strong><\/h2>\n

    A built-in feature of Excel makes it simple to remove redundant data points from your collection.<\/p>\n

    Step 1:<\/strong> The first step is to click on any cell or a certain range in the dataset that contains duplicates that you wish to eliminate. Excel will choose the range for you automatically in the following step if you click just one cell.<\/p>\n

    \"Remove<\/p>\n

    Step 2:<\/strong> Under “Data” tab, select “Remove Duplicates”, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 3:<\/strong> As demonstrated below, a dialog window displays. The columns you want to compare and look for duplicate data in can be chosen. If your data contains column headers, check the box next to “My data has headers,” then click OK, outlined in Red below.<\/p>\n

    The first row will not be taken into account for removing duplicate values when the header option is checked.<\/p>\n

    \"Remove<\/p>\n

    Step 4:<\/strong> The duplicate rows will now be deleted by Excel, and a dialog box will appear. Along with the count of unique values, the dialog box displays a summary of how many duplicate values were discovered and eliminated.<\/p>\n

    \"Remove<\/p>\n

    Step 5:<\/strong> After clicking ok, observably, the duplicate records have been eliminated.<\/p>\n

    \"Remove<\/p>\n

    2. Understand Filtering for Unique Values or Removing Duplicate Values<\/strong><\/h2>\n

    In order to get a list of unique values, you can either delete duplicate values or filter for them. The objectives of all the tasks are the same. However, there is a crucial difference. Duplicate values are briefly hidden when you filter for unique values, but if you decide to remove them, they are gone forever.<\/p>\n

    The comparison of duplicate values must also be understood to be based on the contents of the cell rather than its underlying value. When two cells with the same date value are formatted, for instance, as “31\/10\/2022” and “Oct 10, 2022,” those two values are regarded as separate values. Make it a routine to check before deleting duplicates as a result. To obtain the desired outcomes, try filtering or conditionally formatting unique values.<\/p>\n

    3. Using Advanced Filter Option: Procedure of removing duplicates using advanced filter option in Excel<\/strong><\/h2>\n

    You can remove duplicate values using Excel’s Advanced Filter feature and copy the unique values to another location.<\/p>\n

    Step 1:<\/strong> To start, select the dataset’s cell or range from which duplicates should be eliminated. When you select Advanced Filter and click on a single cell, Excel instantly determines the range.<\/p>\n

    \"Remove<\/p>\n

    Step 2:<\/strong> Select the \u201cAdvanced\u201d option under the \u201cSort & Filter\u201d section on the \u201cData\u201d tab, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 3:<\/strong> A dialogue box will appear. It comprises a menu of advanced filtering choices. The unique values can be copied to another location by choosing the “Copy to another location” option, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 4:<\/strong> Verify that the records’ range in the “List Range” field corresponds to the range you specified.<\/p>\n

    \"Remove<\/p>\n

    \"Remove<\/p>\n

    Step 5:<\/strong> Enter the range where the resulting unique values must be copied in the “Copy to:” field.<\/p>\n

    Step 6:<\/strong> Select “Unique records only” in the checkbox. The most important step comes next. then click “ok”, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    \u00a0<\/strong>Step 7:<\/strong> Cell L1 will get copies of each unique value.<\/p>\n

    \"Remove<\/p>\n

    4. How to use Formulas to Remove Duplicates?<\/strong><\/h2>\n

    This method calculates the count by combining the columns using an Excel formula. The redundant values will then be removed by filtering.\u00a0<\/strong><\/p>\n

    Step 1:<\/strong> Let’s use the concatenation operator “&” to merge columns A, B, and C. The Excel formula would be as follows:<\/p>\n

    =A2&B2&C2<\/p>\n

    The cell D2 is used to insert the formula, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 2:<\/strong> After entering the formula, copy it down to all the rows. The result looks like below.<\/p>\n

    \"Remove<\/p>\n

    Step 3:<\/strong> In order to identify the duplicates in Column D, we now need a new column called “Count.” Therefore, on cell E2, we apply the COUNTIF function. It will be written as =COUNTIF($D$2:D2,D2)<\/p>\n

    The number of observations of each value in column D can be determined with the aid of this formula, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 4:<\/strong> After entering the formula, copy it down to all the rows. The result looks like below.<\/p>\n

    \"Remove<\/p>\n

    If Count has the value “1,” then the item has only ever existed once and is unique. Duplicate values are those that have a value of “2” or higher.<\/p>\n

    Step 5:<\/strong> Under the \u201cData\u201d tab, select \u201cFilter\u201d option in the \u201cSort & Filter\u201d section.<\/p>\n

    A filter will now be added to the Count column, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 6:<\/strong> Select the top-most filter in Column E. Select “1” to keep only the unique values and remove duplicates. Then click ok, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 7:<\/strong> After clicking ok, the result looks like below.<\/p>\n

    \"Remove<\/p>\n

    The table will be cleared of duplicate values. These resulting unique records can be copied and pasted elsewhere.<\/p>\n

    5. Conditional Formatting to identify and remove Duplicate Values<\/strong><\/h2>\n

    Finding and spotting duplicate text is made simpler by conditional formatting. However, Excel is unable to draw attention to duplicate entries in the PivotTable report’s Values section.<\/p>\n

    Step 1:<\/strong> Start by choosing the cells whose values you want to compare for duplication.<\/p>\n

    \"Remove<\/p>\n

    Step 2:<\/strong> Go to “Home” tab, then select “Conditional Formatting”, then go to “Highlight Cells Rules’ and then select “Duplicate Values”, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 3:<\/strong> The Duplicate Values option will prompt you with a popup box once you click it.<\/p>\n

    Select the formatting for the duplicate values from the drop-down menu in the box next to the “values with” option. then press OK, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 4: <\/strong>After clicking ok, the result looks like below.<\/p>\n

    \"Remove<\/p>\n

    Step 5:<\/strong> Now, click on any cell or a certain range in the dataset that contains duplicates that you wish to eliminate.<\/p>\n

    Remember that all duplicate data will be permanently erased before using the remove duplicates tool. It is therefore advised to duplicate the original data to a new worksheet in order to prevent the unintended loss of any information.<\/p>\n

    \"Remove<\/p>\n

    Step 6:<\/strong> Under “Data” under the Developer tab, select “Remove Duplicates”, outlined in Red below.<\/p>\n

    \"Remove<\/p>\n

    Step 7:<\/strong> As demonstrated below, a dialog window displays. The columns you want to compare and look for duplicate data in can be chosen. Check or uncheck the columns where you want to eliminate duplicates under Columns.<\/p>\n

    If your data contains column headers, check the box next to “My data has headers,” then click OK, outlined in Red below.<\/p>\n

    The first row will not be taken into account for removing duplicate values when the header option is checked.<\/p>\n

    \"Remove<\/p>\n

    Step 8:<\/strong> The duplicate rows will now be deleted by Excel, and a dialog box will appear. Along with the count of unique values, the dialog box displays a summary of how many duplicate values were discovered and eliminated.<\/p>\n

    \"Remove<\/p>\n

    Step 9:<\/strong> After clicking ok, observably, the duplicate records have been eliminated.<\/p>\n

    \"Remove<\/p>\n

     <\/p>\n

    Application of Remove Duplicates in Excel<\/strong><\/h2>\n

     <\/p>\n