How to remove duplicates but keep rest of the row values in Excel?
In daily data processing, Excel’s “Remove Duplicates” feature often deletes entire rows, which can disrupt other columns’ data and the overall worksheet structure. This article introduces several methods to clear duplicate values only in a specified column—while preserving the rest of each row’s data—including formulas with filters, VBA code, and the Kutools add-in. Each solution is accompanied by typical use cases, advantages, and considerations to help you quickly choose the approach that best fits your needs.
 |  |
Remove duplicates but keep rest of row values with Filter (3 steps)
Remove duplicates but keep rest of row values with VBA (4 steps)
Remove duplicates but keep rest of row values with Kutools for Excel (2 steps)
Remove duplicates but keep rest of row values with Filter
A reliable approach for maintaining the structure of your data while clearing duplicate values is to combine a simple formula with Excel’s Filter feature. This method is particularly effective when you want to visually identify and selectively remove duplicates from a single column, ensuring that other information in each row remains unaffected. This technique is suitable for small to medium-sized datasets and provides full user control over the removal process.
1. Select a blank cell adjacent to your data range (for example, D2 if your data starts in A2), and enter the formula: =A3=A2. This formula compares each cell with the one above to flag duplicates. Drag the fill handle down to apply the formula to the rest of the rows as needed. Please see the screenshot:
2. Next, select the entire data range—including the newly added formula column—and click Data > Filter to activate the filter function. This allows you to filter out those rows flagged as duplicates.
3. Click the Filter icon in the formula column (for instance, Column D), and from the drop-down list select only TRUE. All duplicate entries (except the last occurrence) will be displayed.
4. After clicking OK, all identified duplicate values will be displayed. Select these duplicate cells and press the Delete key to clear them without affecting other cells in their rows. This preserves the row data structure while removing only the duplicate values from the specified column. Refer to the screenshot below:
Tip: Be cautious to select only the duplicate cells—not the entire row—so that only the targeted values are removed.
5. To return your view to normal, click Data > Filter again to disable the filter. Remove or clear the helper formula column if it’s no longer needed. Now, duplicates are gone from the specified column, and all other row values remain intact.
Advantages: No need for VBA or add-ins; steps are clear and manual errors can be easily corrected.
Disadvantages: Not fully automated—manual cleanup is involved; less efficient with very large datasets.
Remove duplicates but keep rest of row values with VBA
VBA provides a more automated method for removing duplicates from a column, while keeping all other data in the same rows untouched. This approach is perfect for handling repetitive cleaning tasks, especially when dealing with large volumes of data or when the removal procedure needs to be repeated often. Using VBA can minimize manual mistakes and save time, but it's most suitable for users who are comfortable with basic scripting in Excel.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor window.
2. In the VBA window, go to Insert > Module, then copy and paste the following code into the new module. This code removes duplicate values from your selected range but retains all other information in the rows:
VBA: Remove duplicates but keep rest of row values
Sub RemoveDuplicates()
'UpdatebyExtendoffice20160918
Dim xRow As Long
Dim xCol As Long
Dim xrg As Range
Dim xl As Long
On Error Resume Next
Set xrg = Application.InputBox("Select a range:", "Kutools for Excel", _
ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
xRow = xrg.Rows.Count + xrg.Row - 1
xCol = xrg.Column
'MsgBox xRow & ":" & xCol
Application.ScreenUpdating = False
For xl = xRow To 2 Step -1
If Cells(xl, xCol) = Cells(xl - 1, xCol) Then
Cells(xl, xCol) = ""
End If
Next xl
Application.ScreenUpdating = True
End Sub
3. To run the script, press F5 or click the Run button. A dialog window will prompt you to select the range where you want to remove duplicate values. Then click OK to confirm.
4. The macro will execute and remove duplicate values within the specified range, leaving blank cells in their place while all other row content is kept. This effectively preserves your worksheet structure.
Advantages: Highly efficient for larger or repetitive datasets; minimizes manual work.
Disadvantages: Requires enabling macros and a basic understanding of VBA; edits are often irreversible so working on a backup is recommended.
Remove duplicates but keep rest of row values with Kutools for Excel
If you have Kutools for Excel installed, you can simplify the process of removing duplicates from a range or column while preserving the rest of the row data. Kutools provides convenient features that handle duplicate data intuitively and efficiently, which is especially useful for users who frequently need to declutter data lists in Excel.
After installing Kutools for Excel, you can use either of the following two methods depending on your data and preferences:
Method1: Merge Same Cells (2 steps)
1. Highlight the duplicate values you wish to deduplicate. Then, navigate to Kutools > Merge & Split > Merge Same Cells. This will consolidate contiguous duplicate values into a single cell, making it easy to clear all except the first instance.
2. After merging, select the merged cells, go to Home > Merge & Center > Unmerge Cells to revert them to standard cells. The duplicates will now appear as blank cells, and only the unique value remains for each group.
The result will look similar to the example below, where duplicates have been removed and the rest of the row values are preserved:
Method2: Select Duplicate & Unique Cells (4 steps)
1. Select the entire list or range from which you want to remove duplicate values. Click Kutools > Select > Select Duplicate & Unique Cells.
2. In the Select Duplicate & Unique Cells dialog box, choose Duplicates (Except1st one) in the Rule section. This will highlight all instances of duplicates after the first occurrence.
3. Click OK. A prompt will notify you how many duplicate cells have been selected. Confirm with another OK.
4. Simply press the Delete key to remove these selected duplicate values, leaving the rest of your row data untouched.
Advantages: No formulas or coding required; very quick and user-friendly; supports bulk operations.
Disadvantages: Requires extra installation.
Summary and troubleshooting: Each solution above offers a practical approach to removing duplicate values while keeping the rest of your row data intact in Excel. The filter and formula methods are suitable for transparent, step-by-step manual processing; VBA is valuable for batch or recurring cleanup; and Kutools provides convenient, streamlined operations for regular users. Always double-check your selected ranges, make backup copies before batch operations, and choose the solution that matches your workflow and comfort level with formulas or code. If you encounter issues such as missed duplicates or accidental data loss, review your helper columns for accuracy, verify sorting, and confirm that your selections are correct before deleting.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in