[Fixed!] Drop Down List Ignore Blank Not Working in Excel

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.

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.

Excel Drop Down List Ignore Blank Not Working: 3 Solutions

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.

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.

• Later, hit the Enter or Tab key to see the result.

• Next, navigate to 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.

• 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 Remove Used Items from Drop Down List in Excel

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.

Solution 3: Run Excel VBA Code to Extract Empty Cells from Drop Down List

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: Hide or Unhide Columns Based on Drop Down List Selection in Excel

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.

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