How to Remove Blanks from Data Validation List in Excel (5 Methods)

This article will briefly describe some valuable methods on how to remove blanks from a data validation list in Excel. When we have blanks in a list of an Excel chart, it also remains in the data validation list which is unwanted.

So I will be working on the following dataset to show you how you can remove blanks from a data validation list in Excel.

Problem While Creating Data Validation List with Blank Cells

Let me show what happens if we create a drop-down list including blank cells. First, we need to create the drop-down list.

Steps:

• Select the cell C5.
• And then select Data >> Data Tools >> Data Validation

• A dialog box will show up. Select List from the Allow bar (Shown in the following figure).

• Now click on the marked icon.

• After that, select cells B5 to B14 and click on the marked icon.

• Then, just click OK.

In this way, we just created our drop-down list.

Here, you can notice that the drop-down list is created along with blank cells. Now I will describe how you can exclude these blank cells from the drop-down list.

5 Ways to Remove Blanks from Data Validation List in Excel

1. Removing Blanks from Data Validation List Using OFFSET Function

This is a way you can create more space for your drop-down list without having any blanks in that column. First, you need to filter out the blanks from your data. Let’s discuss the process.

We need some modifications to our dataset.

• Let’s add a new column ahead of the column that is being used for the drop-down list. We named the new column and the column we are using for the drop-down list as Name List with Blanks and List without Blanks, respectively. (To see how to create a drop-down list, please go to Section 1).

Steps:

• First, type the following formula in cell C5.

Here, the FILTER function will take the range B5:B14 and check any blanks between the range. Then it filters out empty or blank cells from the list.

• Now press the ENTER. You will see the list of names without any blanks.

• After that, select Name Manager from the Formula Tab and click on New.

• Give your range a name. I’m going to use NameNonBlanks as the name of the range.
• Then write the following formula in Refers to:

In this scenario, we want to use some more cells where we can enter some new names, but we don’t want blanks in our drop-down list for those spaces. Here we are creating new entries for the data validation list from C12 to C16 by putting that formula. Keep that in mind ‘offset!’ refers to the sheet name that we are using.

• Now click OK. You will see a Window. Just Close it.

• And then select cell D5 and select Data >> Data Validation List.
• Change the Source Name to =NameNonBlanks.
• Click OK.

• Select the drop-down list bar in cell D5. You will see the list of names we are using.

• Now write some new names throughout cells C12 to C16.
• Then select the data validation list in cell D5.

You can see the new names in your drop-down list. You can’t see any new entries under cell C16 because they are not in your range.

By following this approach, you can create some blank spaces for new entries in your data validation list without making any blanks in it.

Read More: How to Use Custom VLOOKUP Formula in Excel Data Validation

2. Using Go to Special Command to Remove Blanks from the List

We created our drop-down list (section 1) and you see that there are blanks remaining in it. To remove them, we can simply go through the following steps.

Steps:

• Select the cells B5 to B14 and then select Home >> Find & Select >> Go To Special.

• After that, select Blanks and then click OK.

• This operation will select the blank cells.

• Now select any of these blank cells, right-click on it, and select Delete to delete the blanks.

• You will see a dialog box. Select Shift cells up and click OK.

• This operation will remove the blanks from the original list as well as from the drop-down list.

By following this approach, you can easily remove blanks or empty cells from a drop-down list.

3. Using Excel Filter Function to Remove Blanks from the Data Validation List

We can apply the FILTER function in this aspect. We will be using dataset from the Section 2. To see how to create a drop-down list, please go to Section 1.

Steps:

• Type the following formula in cell C5.

Here the FILTER function will take the range B5:B14 and check any blanks between the range. Then it filters out empty or blank cells from the list.

• Press the ENTER key and you will see the list of names without any blanks.

• But if you go to the Drop Down List, you will still see that it contains blanks from column C.

• So to remove these blanks, go to Data Validation from Data Tab.
• Change the final cell of the range to C11 as your filtered list has the range C5 to C11 in the Source.

• Now click OK. You will now have no blank cells in your drop-down list.

This is a pretty efficient way to remove blanks from a drop-down list.

4. Combining IF, COUNTIF, ROW, INDEX and Small Functions to Remove Blanks from Data Validation List

We can also use the combination of IF, COUNTIF, ROW, INDEX and SMALL functions to remove empty cells from the data validation list. It’s going to be a little bit complex. We will be using the dataset from Section 2. To see how to create a drop-down list, go to Section 1.

Steps:

• First, type the following formula in cell C5.

The formula has two main parts. The first part is COUNTIF($B$5:$B$14,”?*”)<ROW(B5)-4,”” and the second one is INDEX(B:B,SMALL(IF(B$5:B$14<>””,ROW(B$5:B$14)),ROWS(B$5:B5))).

• The COUNTIF function counts non-blank text here and that’s why we get the 7 names in column C.
• The ROW function returns the row number of a cell and our empty cell is at position 5 from cell B5. We are subtracting 4 because we want it to be less than that.
• Now hit ENTER.

• Use the Fill Handle to AutoFill the lower cells.

• Now we have the Name list without any blanks. But if we click on the data validation list, we still see blanks in the drop-down list.

• To remove these blanks, go to Data Validation from the Data Tab.
• Change the final cell of the range to C11 as your filtered list has the range C5 to C11 in the Source.

• Now click OK. You will now have no blank cells in your drop-down list.

Thus you can make a data validation list without blanks.

Read More: How to Use IF Statement in Data Validation Formula in Excel

5. Utilizing Combined Functions to Remove Blank Cells from Data Validation List

We can also use nested formulas combined with ADDRESS, INDIRECT, COUNTBLANK, IF and SMALL functions. Let’s discuss the procedure. We will be using the dataset from Section 2. You can also go to Section 1 to see how to create a drop-down list/data validation list.

Steps:

• First, type the following formula in cell C5.

Here, I’ll be explaining in a very simple way how this formula works. It goes through the range B5:B14 and checks the blank cells with the help of the COUNTBLANK function. Then it also checks which cells are not blank throughout B5:B14 and thus it returns non-empty cells.

• Press CTRL + SHIFT + ENTER (because it’s an array formula) and you will see the output in cell C5 as down below.

• Now use the Fill Handle to AutoFill the lower cells.

• But if you go to the Drop Down List, you will still see that it contains blanks from column C.

• To remove these blanks, go to Data Validation from the Data Tab.
• Change the final cell of the range to C11 as your filtered list has the range C5 to C11 in the Source.

• Now click OK. You will now have no blank cells in your drop-down list.

This is another way you can make a drop-down list free from blanks.

Read More: How to Remove Data Validation Restrictions in Excel

Practice Section

In this section, I’m giving you the dataset so that you can practice these methods on your own.

Download Practice Workbook

Conclusion

In a nutshell, I tried to explain some methods of removing blanks from a data validation list in Excel. These methods are a little bit complicated but I tried to brief them in a very simple and understandable way. I request you leave some feedback on this article in the comment section, and also if you have your own ideas or questions, leave them in the comment box too.

Related Articles

• Apply Custom Data Validation for Multiple Criteria in Excel
• How to Apply Multiple Data Validation in One Cell in Excel
• Data Validation Based on Another Cell in Excel
• How to Copy Data Validation in Excel

<< Go Back to Data Validation in Excel | Learn Excel