This post will guide you how to remove duplicates and replace with blank cells in Excel 2013/2016. How do I replace the duplicates with blank cells using a formula in Excel.

1. Replace Duplicates with Blank Cells Using Formula

Assuming that you have a list of data in range B1:C4, and you want to remove duplicate values in this range of cells and replace it with blank cells. How to achieve it.  And if you just use the Remove Duplicates command, it will remove all duplicate rows only. So you can use a formula to remove duplicates and replace it with blank cell based on the IF function, and the COUNTIF function.

Just like this:

=IF(B1="","",IF(COUNTIF($B1:B4,B1)=1,B1,""))

Type this formula into a blank cell, such as: D1 and press Enter key in your keyboard, and then drag the AutoFill Handle down to Cell F4 to apply this formula.

2. Replace Duplicates with Blank Cells using Remove Duplicates

You can replace duplicates with blank cells in Excel by using Remove Duplicates feature. Just do the following steps:

Step1: Select the range of cells containing duplicates that you want to replace with blank cells.

Step2: Click on the “Data” tab in the Excel ribbon. Click on the “Remove Duplicates” button in the “Data Tools” group.

Step3: In the “Remove Duplicates” dialog box, make sure that all columns are selected.

Step4: Click “OK” to remove the duplicates.

3. Replace Duplicates with Blank Cells with VBA Code

You can also use the VBA code to replace duplicate with blank cells in Excel. it can use the Application.InputBox function to select a range and a destination cell. Just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, select Insert > Module to create a new module.

Step3: Copy and paste the VBA code provided into the new module. Save the workbook as a macro-enabled workbook with a .xlsm extension.

Sub ReplaceDuplicatesWithOne_excelhow()
    Dim sourceRange As Range
    Dim destinationCell As Range
    Dim cell As Range
    Dim values As Variant
    Dim i As Long, j As Long
    Dim dict As Object
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    'Select the range to search for duplicates
    Set sourceRange = Application.InputBox(prompt:="Select range to search for duplicates", Type:=8)
    
    'Select the destination cell to place the result
    Set destinationCell = Application.InputBox(prompt:="Select destination cell for the result", Type:=8)
    
    'Copy the values of the selected range to an array
    values = sourceRange.Value
    
    'Loop through the array and add unique values to a dictionary, replacing duplicates with blank cells
    For i = 1 To UBound(values, 1)
        For j = 1 To UBound(values, 2)
            If dict.Exists(values(i, j)) Then
                values(i, j) = ""
            Else
                dict.Add values(i, j), ""
            End If
        Next j
    Next i
    
    'Paste the result to the destination cell
    destinationCell.Resize(UBound(values, 1), UBound(values, 2)).Value = values
    
End Sub

Step4: Press Alt + F8 to open the Macro dialog box. Select the macro you want to run from the list of macros and click the Run button.

Step5: select the range to search for duplicates.

Step6: select one destination cell to place the result.

Step7: The VBA code will then execute and replace duplicates with blank cells or keep one copy of each duplicate value. The result will be placed in the selected destination cell.

4. Show Only Duplicate Values

If you want to replace all unique values with blank cells and only show duplicates values in Excel, you can use another formula based on the IF function and COUNTIF function. Like this:

=IF(B1="","",IF(COUNTIF($B1:B4,B1)>1,B1,""))

Type this formula into a blank cell and then drag the AutoFill Handle down to other cells to apply this formula.

Let’s see the result:

5. Show Only Unique Values

If you want only to show the unique values and remove all duplicate values and replace with blank cells in Excel, you can use the following formula based on the IF and COUNTIF function.

=IF(B1="", "", IF(COUNTIF(B$1:B$4,B1)>1,"",B1))

6. Video: Replace Duplicates with Blank Cells

This video will guide you through the steps on how to replace duplicates with blank cells in Excel using built-in Excel functions or VBA code.

7. Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…
• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…