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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel data validation list remove blanks


Download Practice Workbook


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 Allow bar (Shown in the following figure).

excel data validation list remove blanks

  • Now click on the marked icon.

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

excel data validation list remove blanks

  • Then, just click OK.

By this way, we just created our drop down list.

excel data validation list remove blanks

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 which 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.
=FILTER(B5:B14,B5:B14<>"")

excel data validation list remove blanks

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 Formula Tab and click on New.

excel data validation list remove blanks

  • Give your range a name. I’m going to use NameNonBlanks as the name of the range.
  • And then write the following formula in Refers to
=offset(offset!$C$5,0,0,counta(offset!$C$4:$C$16)-1,1)

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.

excel data validation list remove blanks

  • 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.

excel data validation list remove blanks

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

You can see the new names in your drop down list. You can’t see any new entries under the 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 Create Excel Drop Down List for Data Validation (8 Ways)


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

We created our drop down list (section 1), 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.

excel data validation list remove blanks

  • After that, select Blanks and then click OK.

  • This operation will select the blank cells.

excel data validation list remove blanks

  • Now select any of these blank cells, do right click on it and select Delete to Delete the Blanks.

  • You will see a dialog box. Select Shift Cells Up and click OK.

excel data validation list remove blanks

  • 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.

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


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.
=FILTER(B5:B14,B5:B14<>"")

excel data validation list remove blanks

Here the FILTER function will take the range B5:B14 and checks 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.

excel data validation list remove blanks

  • 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.

excel data validation list remove blanks

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

Read More: Excel Data Validation Drop Down List with Filter (2 Examples)


Similar Readings:


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. And to see how to create a drop down list, go to Section 1.

Steps:

  • First, type the following formula in cell C5.
=IF(COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"",INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))))

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.

excel data validation list remove blanks

  • 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.

excel data validation list remove blanks

  • And 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.

excel data validation list remove blanks

Thus you can make a data validation list without blanks.

Read More: Default Value in Data Validation List with Excel VBA (Macro and UserForm)


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.
=IF(ROW()-ROW($B$5:$B$14)+1>ROWS($B$5:$B$14)-COUNTBLANK($B$5:$B$14),"", INDIRECT(ADDRESS(SMALL((IF($B$5:$B$14<>"",ROW($B$5:$B$14),ROW()+ROWS($B$5:$B$14))),ROW()-ROW($C$5:$C$14)+1),COLUMN($B$5:$B$14),4)))

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.

excel data validation list remove blanks

  • 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.

excel data validation list remove blanks

  • And 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.

excel data validation list remove blanks

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

Read More: How to Use IF Statement in Data Validation Formula in Excel (6 Ways)


Practice Section

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


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

Meraz Al Nahian
Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo