How to Create Dynamic Drop Down List without Blank in Excel

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel.

In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be used to reduce mistake and typing errors. And it is also be used to restrict the user for the type of data that can be entered in the range.

Assuming that you have a list of data in your current worksheet, and you can want to create a dynamic drop down list based on those data without blank cells, and if you use the usual method to create it, and it may be doesn’t work. And you can use an OFFSET formula and count the entries in the column, and calculate the number of rows in the range, and then create a source list without blanks, and use formulas to pull the numbered items into a new column. Just do the following steps:

Step1: select cell A2 next to the original data list B1:B8, and type the following formula in Cell A2, and copy it down to cell A8, and it will number the cells that are not blank.

=IF(B2=””,””,MAX(A$1:A1)+1)

create dynamic drop down list with blank1

Step2: then you can create a new source list column without blanks based on the source data list, just enter the following formula into the cell D2 in a new column, and then copy it down to the cell D8 to create a new data list with all the blanks at the end.

=IFERROR(INDEX($B$2:$B$8,MATCH(ROW()-ROW($D$1),$A$2:$A$8,0)),””)

create dynamic drop down list with blank2

Step3: you can create a dynamic drop down list based on the newly source range without blanks. Select a cell that you want to create dynamic drop down list , and go to Data tab, and click Data Validation command under Data Tools group. And the Data Validation dialog will appear.

create dynamic drop down list with blank3

Step4: select List value from the Allow drop down list in the Data Validation dialog box, and enter the following formula into the Source text box. And click Ok button.

=OFFSET($D$1,1,0,MAX($A:$A),1)

create dynamic drop down list with blank4

Step5: You would see that the dynamic drop down list should be created without blanks in your current worksheet.

create dynamic drop down list with blank5

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Related Posts

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum the Smallest N Values in Excel
How to Sum the Smallest N Values in Excel 15

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Highlight Every Other Row or Every Nth Row in Excel?
How to Highlight Every Other Row or Every Nth Row in Excel 11

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...

How to Count Duplicate Values Only Once in A Range in Excel?
How to Count Duplicate Values Only Once in A Range in Excel5

When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

How to Count Only Unique Values Excluding Duplicates in Excel
Count Only Unique Values Excluding Duplicates 6

We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the ...

How To Transpose Every N Rows of Data into Muliptle Columns in Excel
How to Transpose every N rows from one column to multiple2

This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel. Assuming that you have a list of ...

Sidebar