How to remove duplicates but keep rest of the row values in Excel

Duplicate values in Excel can be annoying, but fortunately, there are several methods for finding and removing them. We recommend making a backup copy of your Excel sheet before re

How to remove duplicates but keep rest of the row values in Excel
Excel Duplicate Values Find Remove

Duplicate values in Excel can be annoying, but fortunately, there are several methods for finding and removing them. We recommend making a backup copy of your Excel sheet before removing the duplicates. Lets look at how to find, count, and remove duplicate values in Excel.

Content

  • 1. Use the Remove Duplicates Button
  • 2. Find Duplicate Data Using Conditional Formatting
  • 3. Remove Duplicates Using Conditional Filter
  • 4. Find Duplicate Values Using a Formula
  • 5. Count Number of Duplicates Using a Formula
  • 7. Remove Duplicate Data Using Advanced Filters
  • 8. Remove Excel Duplicates Using Power Query

1. Use the Remove Duplicates Button

Excel comes with a native button to remove duplicate values.

  1. Select the column where you would like to remove the duplicates.
  2. Go to the Data tab and click the Remove Duplicates button.
Excel Duplicate Values Remove Button
  1. The Duplicates warning box will appear, letting you customize your selection.
  2. Select the Continue with the current selection option to remove duplicates from the currently selected data set.
  3. To include more columns in the list, select Expand the selection.
  4. Press the Remove duplicates button in both cases.
Excel Duplicate Values Remove Button Selection
  1. Keep the box checked next to the columns containing the duplicates and uncheck that you want to keep them.
  2. Make sure the box next to My data has headers is selected. This will keep the first sets of data for duplicate values while removing their subsequent entries.
Excel Duplicate Values Remove Button Headers
  1. Click the OK button. Excel will remove all duplicate values and provide a summary of how many duplicate and unique values were found.

2. Find Duplicate Data Using Conditional Formatting

This method of removing duplicates is based on conditional formatting and will help you find duplicate values without deleting them.

  1. In your Excel sheet, select the data that contains duplicates. It can be a column or the entire table.
  2. Go to the Home tab and click on Conditional Formatting. The duplicate values will be highlighted.
Excel Duplicate Values Home Tab
  1. Choose Highlight Cell Rules followed by Duplicate Values.
Excel Duplicate Values Duplicate Values
  1. The Duplicate Values pop-up will appear, allowing you to change the color of duplicate values.
  2. Click on the Duplicate drop-down box and select Unique if you want to see unique values instead.
  3. Click on OK.
Excel Duplicate Values Duplicate Values Color

3. Remove Duplicates Using Conditional Filter

With the duplicates highlighted, you can either use filters to remove them or use the method shown above.

  1. To activate the Filter operation, go to Home tab  Sort & Filter  Filter.
Excel Duplicate Values Sort & Filter
  1. A drop-down box will appear next to the column headers in your data. Click on the drop-down box on the column where you would like to filter duplicates.
  2. Select Filter by color from the menu. Choose the color of unique values to keep them in your sheet and remove the duplicates.
Excel Duplicate Values Filter By Color

Now your sheet will only show unique values. Be careful as this method removes all the duplicate values including the first one.

Excel Duplicate Values Filter By Color Result

To copy the unique values, select the data set and use the Alt + ; keyboard shortcut to select only the visible rows, then use the Ctrl + C shortcut to copy the visible rows and paste them wherever required.

4. Find Duplicate Values Using a Formula

Duplicates in a column can also be found using Excel formulas. The most basic formula for detecting duplicate entries is =COUNTIF(Range, Criteria) >1, where the range can be the entire column or a subset of rows.

Create a new column and enter one of the below commands in the new column.

  • Use the formula =COUNTIF(D:D, D2) >1 for a full column, where D is the column name and D2 is the topmost cell.
  • For selected rows, use the formula =COUNTIF($D$2:$D$10, $D2) >1, where the first set represents the selected rows, and D2 is the topmost cell. Please note that the range must be preceded by the dollar ($) sign, otherwise the cell reference will change when you drag the formula.

Drag the fill handle to use the command in the other rows. The formula will show True for duplicate values and False for unique values.

Excel Duplicate Values Formula Find Duplicate Values

If you want to display some other text than True or False, you must enclose the COUNTIF formula in the IF function. So the formula will become =IF(COUNTIF($D$2:$D$10, $D2) > 1, "Duplicate, Single).

If you want unique values to show a blank cell, use the formula =IF(COUNTIF($D$2:$D$10, $D2) > 1, Duplicate, ).

Excel Duplicate Values Formula Text

5. Count Number of Duplicates Using a Formula

You can use the above formula without the > 1 text to count the number of duplicates in a column. The two formulas will become =COUNTIF($D$2:$D$10, $D2) and =COUNTIF(D:D, D2). Enter in the new column for it to display how many times each item appears in the data.

Excel Duplicate Values Formula Count Duplicates

6. Remove Duplicate Values Using a Formula

Once you have found the duplicate values or the duplicate count, use the filter method to remove duplicates and retain unique values.

  1. Go to Home tab -> Sort & Filter -> Filter to enable the filter drop-down box on the column header.
  2. Click it and keep the box checked next to the value you want to keep. It should be Unique for the finding duplicates method and 1 for Counting duplicates. Doing so will hide the duplicate values and display unique values.
  3. Select the visible rows using the Alt + ; shortcut.
Excel Duplicate Values Formula Hide

7. Remove Duplicate Data Using Advanced Filters

  1. Go to the Data tab and click on Advanced.
Excel Duplicate Values Advanced Filter
  1. The Advanced Filter pop-up window will open.
  2. Select the Filter the list, in-place option if you want to hide the duplicates on the same data set. You can manually copy-paste the unique values later to a different place in the same sheet or to a different sheet.
  3. With this option selected, select the list of columns. They will automatically show up in the List range field. Leave Criteria range blank.
  4. Check the box next to Unique records only and hit OK.
Excel Duplicate Values Advanced Filter In Place
  1. That will show unique values in your data. Use the Alt + ; shortcut to select visible rows only if you want to perform any action on them.
  2. On the contrary, select Copy to another location if you want Excel to automatically copy the unique values to a different place in the same sheet.
  3. Select the List range first. You can keep the Criteria range blank.
  4. Click once on the Copy to field and select the rows on your sheet where you want to copy the unique data.
  5. Make sure the box next to Unique records only is checked.
Excel Duplicate Values Advanced Filter Copy To

8. Remove Excel Duplicates Using Power Query

Power Query also helps to remove duplicate values in Excel as shown below.

  1. Select the values where you want to remove duplicates.
  2. Go to the Data tab and click on From Table/Range.
Excel Duplicate Values Power Query
  1. The Power Query editor will open. Select the columns and right-click on the selected column header. Choose Remove duplicates from the menu.
Excel Duplicate Values Power Query Remove
  1. If you want to remove duplicates from the entire table, click on the Table button at the top-left corner and choose Remove Duplicates. Alternatively, select Keep duplicates to show only the duplicate entries and remove the rest.
Excel Duplicate Values Power Query Remove Table
  1. Click on Close and Load at the top to open the table in the same sheet.

9. Using Pivot Tables

You can use Pivot tables to display only the unique values in your data, thus removing the duplicate entries.

  1. Start by creating a Pivot table by selecting a cell inside your data.
  2. Go to Insert  Pivot table. Select From Table/Range.
Excel Duplicate Values Pivottable
  1. The PivotTable from the table or range pop-up will open.
  2. Select the table or range where you want to hide duplicate values, for it to automatically show up in the Table/Range field.
  3. Select whether the PivotTable should be placed in the same worksheet or in a new worksheet.
  4. Click OK.
Excel Duplicate Values Pivottable Select Range
  1. You will be greeted by the PivotTable Fields sidebar. Drag the columns where you to extract unique values to the Rows section.
Excel Duplicate Values Pivottable Fields

You will need to format the PivotTable to show it in a tabular form. For that, go to the Data tab and perform the following steps:

  1. Click on Report Layout  Show in tabular form.
  2. Go to Subtotals  Do not show subtotals.
  3. Click on Report Layout  Repeat all item labels.
  4. Under Grand Totals, select Off for rows and columns.
Excel Duplicate Values Pivottable Tabular Form

You will get a Pivot table with unique values in a tabular form.

Excel in Excel

Read on to learn how to merge cells, columns, and rows to create a new set of data. And in case your worksheet contains important data, find out how to password protect your Excel workbook.Is this article useful? Yes NoSubscribe to our newsletter!

Our latest tutorials delivered straight to your inbox    Sign up for all newsletters.  By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time.SubscribeLeave a comment

  • Facebook
  • Tweet

Video liên quan