{"id":2721,"date":"2019-09-03T14:03:58","date_gmt":"2019-09-03T20:03:58","guid":{"rendered":"https:\/\/www.brendapenman.com\/blog\/?p=2721"},"modified":"2021-11-21T15:47:31","modified_gmt":"2021-11-21T22:47:31","slug":"remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.brendapenman.com\/blog\/remove-duplicates-in-excel\/","title":{"rendered":"Remove Duplicates in Excel to Clean Up Your Data"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”3.24″ custom_margin=”0px||0px” custom_padding=”0px||0px” global_colors_info=”{}”][et_pb_row _builder_version=”3.25″ collapsed=”off” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

If you compile data from multiple sources into a single worksheet, you may end up with duplicate rows. And in most cases, you will want to eliminate these duplicates.<\/p>\n

For example, if you have combined lists of names and addresses, you want to ensure that the names and addresses are unique<\/strong> or only listed once in your list.<\/p>\n

Or you have a list of invoices and you want to make sure there are no duplicates<\/strong>.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Instead of manually looking through your data and deleting duplicate rows, use the Remove Duplicates<\/b>\u00a0feature to get Excel to delete the duplicate rows for you.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

The Remove Duplicates feature was introduced in Excel 2007.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_image src=”https:\/\/www.brendapenman.com\/wp-content\/uploads\/2018\/02\/RemoveDuplicates5.png” _builder_version=”3.27.2″ global_colors_info=”{}”][\/et_pb_image][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|800|||||||” text_text_color=”#e02b20″ text_font_size=”26px” global_colors_info=”{}”]<\/p>\n

Caution:<\/strong><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Excel’s Remove Duplicates feature does delete the duplicate rows.<\/p>\n

Just to be safe, you may want to create a backup copy<\/strong> of your worksheet or workbook before you proceed to Remove Duplicates.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

To Remove Duplicates in Excel:<\/h2>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ module_alignment=”left” custom_margin=”|||” global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

1.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Create a Backup Copy of your worksheet or workbook before proceeding, just in case.<\/p>\n

Remember:<\/strong> Excel’s Remove Duplicate feature does delete the duplicate rows.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ module_alignment=”left” custom_margin=”12px|auto||auto||” global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

2.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Select any cell within the data range.<\/p>\n

e.g. On sheet 1, click on cell A2<\/span><\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” custom_padding=”||8px|||” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

3.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

On the Data\u00a0<\/b>tab, in the Data Tools\u00a0<\/b>group, click the\u00a0Remove Duplicates\u00a0<\/strong>icon.<\/p>\n

Excel expands the selection to include the entire data range.<\/p>\n

e.g. Excel expands to select cells in the data range,\u00a0
A1 to E400<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_image src=”https:\/\/www.brendapenman.com\/wp-content\/uploads\/2018\/02\/RemoveDuplicates3.png” align=”center” _builder_version=”3.27.2″ global_colors_info=”{}”][\/et_pb_image][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

4.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

In the Remove\u00a0Duplicates<\/strong>\u00a0dialog box, Excel determines if your data has headers.<\/p>\n

This dialog box displays all of the columns in your data range.<\/p>\n

 <\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_image src=”https:\/\/www.brendapenman.com\/wp-content\/uploads\/2018\/02\/RemoveDuplicates5.png” align=”center” _builder_version=”3.27.2″ global_colors_info=”{}”][\/et_pb_image][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

5.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Place a checkmark<\/strong> next to the columns that you want to include in the duplicate search.<\/p>\n

By default, Excel selects all<\/strong> of the columns.<\/p>\n

And most of the time, you will want to include all columns in your search.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

When all<\/strong> columns are selected, Excel deletes a row only if<\/strong> the content of every column is duplicated.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

\u00a0In other situations, you may not<\/strong> want to analyze all the columns for duplicates. In that case, uncheck those columns in the Remove Duplicates dialog box.<\/p>\n

[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row column_structure=”1_4,3_4″ _builder_version=”3.25″ global_colors_info=”{}”][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ text_font=”|700|||||||” text_text_color=”#00a4e4″ text_font_size=”31px” text_orientation=”center” text_text_shadow_style=”preset2″ global_colors_info=”{}”]<\/p>\n

6.<\/p>\n

[\/et_pb_text][\/et_pb_column][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.27.4″ global_colors_info=”{}”]<\/p>\n

Click OK<\/strong> to remove the duplicates.<\/p>\n

Excel displays:<\/p>\n