How To Create Drop Down List Ignore Blank Cells In Excel?

Drop-down lists are a great way to create more efficient and accurate data entry in your Excel spreadsheet. However, when your data includes blank cells, it can be tricky to create a drop-down list that only includes the non-blank cells. Fortunately, there is a simple solution to this problem. In this tutorial, we will guide you step-by-step on how to create a drop-down list while ignoring blank cells in Excel. By the end of this tutorial, you will be able to create a drop-down list that only includes the relevant data, making your data entry experience more efficient and streamlined. So, let's get started!

Create Drop Down List Ignore Blank Cells

Here we will first list the values without the blank cells and then create a data validation list. So let us see a simple process to know how you can create drop-down lists in Excel and ignore blank cells.

Step 1

Consider an Excel sheet where you have a list of names with some blank cells similar to the below image.

First, click on an empty cell in our case cell C1 and enter the formula as =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$12<>"",ROW($A$1:$A$12)),ROWS($C$1:C2))))) and click CTRL + SHIFT + ENTER to get the first value.

Empty cell > Formula > CTRL + SHIFT + ENTER.

Step 2

Then drag down from the first value using the auto-fill handle to get all the results.

Step 3

Now click on an empty cell, in this case cell E2, then click on data and select data validation.

Then in the pop-up window, set allow to list and source as the range of cells, and click OK to get the data validation list.

Empty cell > Data > Data Validation > Allow > Range.

Then the final output will be similar to the below image.


In this tutorial, we have used a simple example to demonstrate how you can create a drop-down list and ignore blank cells in Excel to highlight a particular set of data.

Updated on: 13-Jul-2023


Kickstart Your Career

Get certified by completing the course

Get Started