Excel Formulas for Removing Duplicate and Blank Values in a Column

Sometimes, you may have a list of data in Excel that contains duplicate values and blank cells. You may want to remove the duplicates and leave the blank cells as they are, or you may want to remove both the duplicates and the blank cells. In this article, we will show you how to do both tasks using Excel formulas.

To remove duplicates with blank cells in a column using Excel formulas, we need to use two functions: IF and COUNTIF. The IF function returns one value if a condition is true, and another value if the condition is false. The COUNTIF function counts the number of cells in a range that meet a certain criterion.

We can use the IF function to check if a cell is blank or not, and then use the COUNTIF function to check if a cell is a duplicate or not. Depending on the results, we can return the original value, a blank, or a unique value.

Procedures

To remove duplicates with blank cells in a column using Excel formulas, follow these steps:

1. Assume that you have a list of data in column A, starting from cell A2. You can use any column and any starting cell as long as you adjust the formulas accordingly.
2. In an adjacent column, such as column B, enter the following formula in the first cell, such as B2:

   =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,""))

3. This formula checks if the cell A2 is blank or not. If it is blank, it returns a blank. If it is not blank, it counts how many times the value in A2 appears in the range $A$2:A2. If it appears only once, it returns the value in A2. If it appears more than once, it returns a blank. This way, only the first occurrence of a value in column A is returned in column B, and all the duplicates are replaced with blanks.
4. Drag the formula down to fill the rest of the column B. You will see that column B has the same values as column A, but without the duplicates. The blank cells in column A are also preserved in column B.
5. If you want to remove both the duplicates and the blank cells in column A, you can use a slightly different formula in column B. Enter the following formula in the first cell, such as B2:

   =IF(A2="","",IF(COUNTIF(A$2:A$15,A2)>1,"",A2))

6. This formula checks if the cell A2 is blank or not. If it is blank, it returns a blank. If it is not blank, it counts how many times the value in A2 appears in the range A$2:A$15. If it appears more than once, it returns a blank. If it appears only once, it returns the value in A2. This way, only the unique values in column A are returned in column B, and all the duplicates and blanks are replaced with blanks.
7. Drag the formula down to fill the rest of the column B. You will see that column B has only the unique values from column A, and no duplicates or blanks.

Example

To illustrate the procedures, let’s use an example. Suppose you have the following list of names in column A, starting from cell A2:

You want to remove the duplicates and leave the blank cells as they are. To do that, you can use the first formula in column B, starting from cell B2:

You can see that column B has the same values as column A, but without the duplicates. The blank cells in column A are also preserved in column B.

If you want to remove both the duplicates and the blank cells in column A, you can use the second formula in column B, starting from cell B2:

You can see that column B has only the unique values from column A, and no duplicates or blanks.

Other Approaches

Besides using Excel formulas, there are other ways to remove duplicates with blank cells in a column. For example, you can use the Remove Duplicates feature in the Data tab of the Ribbon, or you can use a VBA macro. However, these methods may not preserve the blank cells in the original column, and they may modify the original data. Therefore, you should always make a backup copy of your data before using these methods.