Meraz Al Nahian
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.
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).
- Now click on the marked icon.
- After that, select cells B5 to B14 and click on the marked icon.
- Then, just click OK.
By 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 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<>"")
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.
- 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.
- 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 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.
- After that, select Blanks and then click OK.
- This operation will select the blank cells.
- 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.
- 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<>"")
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.
- 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.
Read More: Excel Data Validation Drop Down List with Filter (2 Examples)
Similar Readings:
- How to Use Custom VLOOKUP Formula in Excel Data Validation
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
- How to Make a Data Validation List from Table in Excel (3 Methods)
- Apply Multiple Data Validation in One Cell in Excel (3 Examples)
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
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.
- 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.
- 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.
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.
- 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.
- 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.
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
- Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
- How to Use Data Validation in Excel with Color (4 Ways)
- How to Use Data Validation List from Another Sheet (6 Methods)
- Excel VBA to Create Data Validation List from Array
- How to Use Named Range for Data Validation List with VBA in Excel
