Microsoft Excel is an intuitive tool that more than lives up to its illustrious reputation. We can perform many actions on a dataset thanks to Excel‘s tools and functions. We often need to create a drop-down list in Excel. However, the drop-down list looks ugly, with empty cells in the menu. We sometimes try to use the Ignore Blank to remove the blank cells. Interestingly, it never works in preventing the empty cells in the drop-down menu. Because the Ignore Blank has nothing to do with eliminating the empty cells in the drop-down list. This article will come up with three solutions for how we can remove blank cells in a drop-down list. Therefore, use one of these three suitable solutions to remove the empty cells in the Excel Drop Down List while Ignore Blank is Not Working.
Download Practice Workbook
You are kindly invited to get a free copy of the example workbook used during the presentation, and we thank you in advance for accepting this invitation.
What Is Drop Down List?
A Drop Down List allows the user to choose text or numbers from a list rather than typing them into a cell. After that, select the cells where the drop-down lists are needed. Go to the Data tab. Later, we need to click the Data Validation option in the Data Tools. Lastly, we need to allow the List option in the Validation criteria in the Data Validation window.
Reason with 3 Solutions to Fix Issues When Drop Down List Ignore Blank Not Working in Excel
For the sake of providing an example, let’s investigate a sample dataset. For instance, the following dataset has two columns titled Product and Area. Whenever we try to create a drop-down list in such a range that contains blank cells, it will also display an empty option in the drop-down menu. Though some users try to fix the issues with the help of the Ignore Blank option, it will not work to prevent blank cells. Thus, they may think that the Ignore Blank is Not Working. We will use the three solutions to remove the empty cells from the drop-down menu.
I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.
Reason: Source Range of Excel Drop Down List Contains Blank Cells
After creating a Drop Down List with the help of the Data Validation feature, we find some items remain blank while displaying the menu. This issue happens when the drop-down list contains empty cells in the Source Range.
Solution 1: Utilize FILTER and OFFSET Functions to Eliminate Blank Cells in Excel Drop Down List
This section will utilize the FILTER and OFFSET functions to remove the empty cells from a drop-down menu. Please adhere to the steps described below to complete the work effectively.
STEPS:
- First, create a column titled Select Area like the below one.
- Second, choose the D5 cell.
- After that, input the following equation in the Formula bar.
=FILTER(C5:C10,C5:C10<>"")
- Later, hit the Enter or Tab key to see the result.
- Next, navigate the Formulas tab and click on the Name Manager icon.
- Subsequently, the Name Manager window will appear, then tap New.
- Afterwards, the New Name window will open.
- Later, type the name, in this case, DropDownWithoutBlank.
- At this point, input the following formula in the Refers to box.
=OFFSET(OFFSETFunction!$D$5,0,0,COUNTA(OFFSETFunction!$D$4:$D$10)-1,1)
- Afterwards, hit OK.
- Now, choose Close from the Name Manager window.
- Presently, select the B13 cell.
- Latterly, go to the Data tab and click on the Data Validation symbol.
- Consequently, the Data Validation window will pop up.
- From the Settings tab, later choose List in the Allow box.
- In the Source box, input an Equal sign and type the Range name created earlier.
- After that, hit OK.
- As a result, after expanding, the desired drop-down list will display like the below one.
Read More: How to Add Blank Option to Drop Down List in Excel (2 Methods)
Similar Readings
Solution 2: Delete Blank Cells Using Find & Select Feature
The context will cover how we can eliminate blank cells from a drop-down menu with the help of Find & Select. Please follow the steps listed below to finish the work well.
STEPS:
- To begin, make another column named Select Area.
- Second, select the E5 cell.
- After that, navigate to the Data tab and click on the Data Validation icon.
- Subsequently, the Data Validation dialog box will appear.
- Presently, from the Settings tab, choose List in the Allow section.
- Latterly, input an Equal sign followed by the $C$5:C$10$ range and hit OK.
- After expanding, the drop-down will display a drop-down with some empty cells.
- To eliminate those, select the C5:C10 range.
- Later, go to the Home tab and click on Editing.
- From the Editing group, choose Find & Select.
- After that, pick the Go To Special option.
- From the Go To Special window, check Blanks and hit OK.
- At this point, the outcome will look like the below.
- Now, right-click on any selected empty cell and from the Context menu, choose Delete.
- Next, check the Shift cells up option and tap OK.
- As a result, the desired outcome will be produced like the below one.
Read More: Excel Drop Down List Not Working (8 Issues and Solutions)
Solution 3: Run Excel VBA Code to Extract Empty Cells from Drop Down List
The acronym VBA stands for Visual Basic for Applications. Microsoft has created VBA, and we can utilize Excel‘s incompatible features using VBA code. This lesson will teach you how to resolve the problem, as the Ignore Blank option does not erase blank cells from a drop-down list. Please complete the assignment according to these instructions.
STEPS:
- First, like before, generate another column titled Select Area.
- Secondly, go to the Developer tab and choose the Visual Basic symbol.
- After that, click on,
Insert → Module
- Later, input the code below in the Module box.
Sub DropDownWithNoBlank()
Range("D5").Select
ActiveCell.Formula2R1C1 = "=FILTER(RC[-1]:R[5]C[-1],RC[-1]:R[5]C[-1]<>"""")"
ActiveWorkbook.Names.Add Name:="DropDownWithoutBlankUsingExcelVBA", _
RefersToR1C1:= _
"=OFFSET(OFFSETFunction!R5C4,0,0,COUNTA(OFFSETFunction!R4C4:R10C4)-1,1)"
Range("B13:D13").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=DropDownWithoutBlankUsingExcelVBA"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
- At this time, press F5 or click on the Run button.
- Consequently, the drop-down will display in the desired manner after expanding.
Read More: Create Excel Drop Down List from Table (5 Examples)
Conclusion
By following the steps we have just gone through, you will now be able to solve when the Excel Drop Down List Ignore Blank is Not Working in the prevention of the empty cells in the menu. Further articles on the ExcelDemy Website are like this one. Continue to use them, and let us know if you come up with any new ideas or techniques for finishing the assignment. You are welcome to submit any questions, comments, or suggestions in the below-mentioned area.
Related Articles
- How to Make a Drop-Down List Based on Formula in Excel (4 Ways)
- Conditional Drop Down List in Excel (Create, Sort and Use)
- How to use IF Statement to Create a Drop-Down List in Excel
- Link a Cell Value with a Drop Down List in Excel (5 Ways)
- How to Remove Drop-Down List in Excel
- Create Drop Down List in Excel with Multiple Selections
- How to Make Multiple Selection from Drop Down List in Excel