How to remove duplicate rows based on one column

Answer

To remove duplicate rows based on one column in Excel, use the built-in Remove Duplicates feature, Power Query, the UNIQUE function, VBA macros, or a helper column method. Each approach retains the first occurrence of each value by default.

How to use the built-in remove duplicates feature

Excel's built-in Remove Duplicates feature permanently deletes duplicate rows based on selected columns, retaining the first occurrence.

Preparation steps

• Copy your dataset to a new sheet or workbook first to avoid data loss, as the process is irreversible without Undo.
• Ensure data is in table format by selecting the range and clicking Insert > Table for easier handling, or use a structured range with headers.

Select data

• Click any cell within the data range.
• To select the entire range manually, click the top-left cell, hold Shift, and click the bottom-right cell.
• Excel auto-expands to adjacent data when using the tool.

Launch remove duplicates

• Go to the Data tab in the ribbon.
• In the Data Tools group, click Remove Duplicates.
• A dialog box opens listing column headers.

Configure columns

• Check "My data has headers" when the first row contains headers.
• Uncheck all columns except the single target column (e.g., Column A for ID values).
• Click OK to proceed.

Completion

• Excel displays a summary dialog showing duplicates removed and unique values remaining.
• Verify results by sorting or filtering the column.
• Save the file afterward.

How to use power query to remove duplicates based on one column

Excel's Power Query provides a refreshable method to remove duplicate rows based on one column, preserving the first occurrence by default. The standard Remove Duplicates command operates on all selected columns simultaneously, so removing duplicates based on a single column requires selecting only that column first.

Load data into power query

• Select your data range including headers and convert to a table via Insert > Table when not already formatted.
• Go to Data tab > From Table/Range to open Power Query Editor.
• Excel loads the data preview automatically.

Select and remove duplicates

• Click the header of the target column (e.g., Column A) to select only that column.
• Right-click the selected column header and choose Remove Duplicates, or go to Home tab > Remove Rows > Remove Duplicates.
• Power Query removes rows where the selected column contains duplicate values, keeping the first occurrence and retaining all other columns in the output.
• The updated row count appears in the status bar.

Output results

• Click Close & Load (top-left) to send cleaned data to a new worksheet as a refreshable table.
• Right-click the output table later and select Refresh to reapply steps on updated source data.

How to use the unique formula to remove duplicates in excel

Excel 365 and Excel 2021's UNIQUE function dynamically extracts unique values from a column without altering the source data.

Basic unique formula

• Select an empty cell (e.g., D1) next to or below your data in column A (A1:A100, assuming headers excluded).
• Enter =UNIQUE(A2:A100) and press Enter.
• Results spill automatically into adjacent cells.
• Copy the spilled range and use Paste Special > Values to a new location for static output when needed.

Unique values appearing once

Use =UNIQUE(A2:A100,,TRUE) to return only values that occur exactly once, excluding any duplicates. The third argument (exactly_once) set to TRUE filters stricter than the default distinct list.

With criteria or filter

Combine for conditions: =UNIQUE(FILTER(A2:A100,B2:B100="Criteria")) extracts uniques matching column B criteria. For multi-column filtering: =UNIQUE(FILTER(A2:C100,(B2:B100="Value1")+(C2:C100="Value2"))) handles OR logic across columns.

How to use VBA macros to remove duplicates based on one column

Excel's VBA RemoveDuplicates method deletes duplicate rows based on a specified column, retaining the first occurrence.

Access VBA editor

• Enable Developer tab via File > Options > Customize Ribbon > check Developer.
• Press Alt+F11 to open VBA Editor.
• In Project Explorer (left pane), right-click your workbook > Insert > Module to create a new module.

Write the macro code

Paste this code into the module window, adjusting range (e.g., "A1:Z100") and column number (1 for first column):

Sub RemoveDuplicatesColumn1() ActiveSheet.Range("A1:Z100").RemoveDuplicates Columns:=1, Header:=xlYes End Sub

Header:=xlYes assumes row 1 has headers; use xlNo otherwise. Save with Ctrl+S.

Execute the macro

• Close VBA Editor.
• Go to Developer > Macros, select RemoveDuplicatesColumn1, and click Run.
• Alternatively, press Alt+F8, choose the macro, and click Run.
• Verify by checking row count; use Undo (Ctrl+Z) when needed.

How to use the helper column method to remove duplicates

Excel's helper column method uses a formula to flag duplicates in one column, enabling filtered deletion of those rows while keeping the first occurrence.

Create helper column

• Insert a new column (e.g., column E) next to your data, labeling the header "Helper".
• In E2 (assuming data starts in A2 with headers in row 1), enter =COUNTIF($A$2:A2,$A2) and press Enter.
• Drag the fill handle down to the last data row.
• Values show 1 for first occurrences and higher numbers for duplicates.

Apply filter

• Select the entire data range including the helper column (A1:E100).
• Press Ctrl+Shift+L or go to Data > Filter to add dropdown arrows to headers.
• Click the Helper column filter arrow, uncheck 1 (or desired uniques), and check numbers greater than 1 to show only duplicates.

Delete duplicate rows

• With duplicates visible, select all visible rows by clicking the first row number, holding Shift, and clicking the last.
• Right-click a selected row number > Delete > Entire Row.
• Turn off filters using Data > Filter or Ctrl+Shift+L.
• Delete the helper column and verify results.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report