Smart Dependent Drop-Down Lists in Excel:
Expandable & Excluding Blank Cells

In situations where the contents of a drop-down list are frequently changing, it’s helpful to have a dynamic drop-down list that can automatically expand, as well as exclude any blank cells in the cell references.

This example uses the sample data of App names for three divisions: Productivity, Games, and Utility.

Excel drop down list dependent

The drop-down list should display App names based on the Division selected by the user.

Adding data validation for Division

Add data validation to a selected cell.

Go to Data > Data Validation symbol.

Under Validation Criteria, select List.

For the Source, do a cell reference to the Division names, which are the table headers (cell A4:C4).

If set up correctly, it should display as follows:

Method 1: Using OFFSET() to create a dynamic drop-down list

Setup formula for the data validation

Whenever a formula is to be used as a data validation criteria, it is usually easier to first set the formula up on the spreadsheet, then transfer it to the data validation window so that all the Excel helper tips show up as guides.

The OFFSET() formula has the syntax:

= OFFSET(reference, rows, cols, [height], [width])

  • reference = starting point, $A$4
  • rows = how many rows going down until the first value to be used. In this case, since we want to start one row below the header, we input 1
  • cols = how many columns to the right until the first value to be used. In this case, this is dependent on the selected Division by the user. A MATCH() formula comes in handy since it returns a number:

MATCH(lookup_value), lookup_array, [match_type])

    • lookup_value = source, $F$4
    • lookup_array = where the lookup will be done, $A$4:$C$4
    • match_type = 0 for exact match

It now becomes: MATCH($F$4,$A$4:$C$4,0)

However, this can’t be used as is since the return value of the MATCH() function will be 1, 2, or 3 depending on the selected division.

What this means is that when the Productivity division is selected, the MATCH() function returns a “1”, which tells the OFFSET() function to move one column to the right instead of staying in column A.

To fix this, a correction is made by deducting 1 from the MATCH() function.

The col parameter now becomes:

MATCH($F$4,$A$4:$C$4,0)-1

  • [height] = how many rows will be included. This should correspond to the number of apps listed for each division and be dynamic to expand and contract. The COUNTA() function comes in handy since it counts cells with numbers or text.

COUNTA(value1, [value2], …)

However, since the cell reference changes depending on the division selected by the user, another function should be introduced.

The OFFSET() function can also be used here.

OFFSET(reference, rows, cols, [height], [width])

    • reference = starting point, $A$4
    • rows = how many rows going down until the first value, 1
    • cols = how many rows to the right until the first value to be used. This will again depend on the division selected. The same MATCH() function will be used here:

MATCH($F$4,$A$4:$C$4,0)-1

    • [height] = how many rows will be displayed. Use a bigger height than what is expected to make sure additional cells are taken account of. An arbitrary number 20 is used for this example, which should be sufficient. This value can be changed later on.

    • [width] = how many columns will be included, 1

This now becomes:

COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1))

  • [width] = how many columns will be included. Since we only need one column, set this as 1.

The final formula now becomes:

=OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,)

A single value will be displayed in the cell.

However, the formula actually returns an array and is unable to display all the values on a single cell.

To view the entire array, click inside the formula bar and press F9.

Press CTRL + Z again to undo.

Adding data validation for App names

Copy the entire formula.

Add data validation criteria to cell F5 through Data > Data validation.

Under validation criteria, select List.

Paste the formula to the source text box.

Method 2: Using table referencing to create a dynamic drop-down list

Convert data table into Excel tables

Instead of converting the entire data table as one Excel table, convert it into 3 Excel tables.

To do this, highlight each data column (CTRL + SHIFT + DOWN when selecting the header).

Press CTRL + T.

Double check if the cell referencing is correct.

Tick the box to indicate that the table has headers.

Change the table name to the division name.

In this example, we use TableProd.

Do the same for the Games and Utility columns and rename them to TableGame and TableUtility.

Setup formula for data validation criteria

The INDEX() formula can be used to make sure the correct Table is used as reference.

There are two syntax options for INDEX().

The second option will be used.

INDEX(reference, row_num, [column_num], [area_num])

  • reference = instead of selecting an array of cells, the tables should be selected. To do this, hover over the header until a black arrow shows up.

Selecting it should now display the table name with the column name in square brackets: TableProd[Productivity].

Select the other 2 columns in the same manner and enclose all 3 in parenthesis.

This now becomes:

(TableProd[Productivity],TableGame[Games], TableUtility[Utility])

  • row_num = how many rows going down will be included. Since all rows should be included, skip this argument by using a comma.
  • [column_num] = how many columns will be included. Since there is only one column for each table, there is no need to specify the column number. Skip this argument by using a comma.
  • [area_num] = how many areas to move. This can be dictated by a MATCH() formula.

MATCH($F$4,$A$4:$C$4,0)

The final formula now becomes:

=INDEX((TableProd[Productivity],TableGame[Games],TableUtility[Utility]),,,MATCH($F$4,$A$4:$C$4,0))

Similar to method 1, a single value will be displayed on the cell.

However, the formula actually returns an array.

To view the entire array, click inside the formula bar and press F9.

Adding data validation for App names

Copy the entire formula.

Add data validation criteria to cell F5 through Data > Data validation.

Under validation criteria, select List.

Paste the formula to the source text box.

You might receive a message box indicating a problem with the formula.

This is because data validation is unable to use this type of table referencing.

To remedy this, paste this formula in Name Manager.

This is found under Formulas > Name Manager.

The Name Manager will pop up and display the three tables.

Click on New.

Give the formula a new name and paste the formula in the last text box.

It should now show up on the list.

Go back to Data Validation.

Under validation criteria, select List.

For the source, write the name of the formula.

If you have forgotten the name of the formula you have recently added to Name Manager, press F3.

If set up correctly, it should now display the App Names in the dropdown list.

Video and Workbook

Feel free to Download the Workbook HERE.

Free Excel Download

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

10 Comments

  1. Ramsés November 17, 2018 at 9:23 am - Reply

    Hi Leila
    I’m a fan of your tutorials, i learn a lot, and every time I need help i first come to this web or your channel in youtube, even though english is not my language, but i find very easy to follow you
    In order to do what you are doing in this tutorial, i’m looking for a way of transform the data form the way it appears on “Find Multiple Matches in Excel & Create a Dependent Drop Down List” tutorial, into the way it appears on this one
    if you already has explained it, could you please suggest where I can find it?

    • Leila Gharani November 20, 2018 at 12:37 pm - Reply

      Hi Ramses. Many thanks for your comment. I’m very happy to hear you find the tutorials easy to follow. Regarding your query – I think you might be looking for this video.

  2. Stefan Zawistowski March 9, 2019 at 9:46 pm - Reply

    The first method seems to me to be flawed, bc it requires an arbitrary number of rows to add (20 here). What happens if I need to add new data beyond the 20 empty cells that have been fille out? The second method is also less ‘convoluted’ (more elegant) and easier to absorb for me. Great tutorial, btw.

  3. Azizullah April 6, 2019 at 2:23 am - Reply

    Respected sister ! you are really one of the greatest teachers .

  4. Adrian April 19, 2019 at 2:44 am - Reply

    Hi! Thanks for the simple demonstration on how dynamic lists work!
    I was wondering on if there is a way to have formulas dependent of a dynamic array list of values; in particular also formula that is also a dynamic array.

    I have two tables to start with: a client list (with an unique id number column) and a document table (where a document ID number also exists as well as a reference to corresponding client unique ID). Both are structured tables and I’m using structured references. Then there’s a third table, with lots of records which are related both to a client ID and a document ID from the previous tables.

    I was trying to get a way of having a filtered validation list of Documents to select from, dependent on the client has been selected for that specific record.

    Would this be possible with dynamic arrays?

    Regards!

    • Bryon Smedley May 5, 2019 at 11:18 am - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      The XelPlus Team

  5. Deniz Aksen July 11, 2019 at 6:22 pm - Reply

    Hello Dear Excel Queen / Excel Melikesi Leila Gharani;

    This is yet another very eloquent and elaborate tutorial on the subject of “Conditional and Dynamic Data Validation”. I had thought of the same problem, though with static data ranges, some time ago, and had come up with a solution which is probably similar to your Method 2.

    As to Method 1 with OFFSET() function: Whenever I see an Excel magic with the function OFFSET(), I take my hat off!.. Method 1 solution is of course very esoteric.

    In my alternative version of Method 2, let’s denote is as Method 3–the NAME-BASED Method, I proceed as follows.
    Step 1: I first select each data column and convert it to a single-column table just as you proposed. This already resolves the dynamic data range issue, and allows me to keep validating the data after the insertion or deletion of one or more rows (cells).

    Step 2: Next, I simply select the entire range of data along with column titles either manually or by first opening the “Go To…” window (CTRL+G or F5 is the shortcut for that window), and subsequently selecting the columns of unequal lengths by clicking on the “Special…” button first and then choosing in the appearing “Go To Special” window the “Constants” radio button.

    Step 3: Once all columns of data of unequal lengths have been selected, I use the “Create from Selection” facility of Excel in the Format menu ribbon or simply press the shortcut key combination CTRL + SHIFT + F3 in order to assign each column title as a name to the respective data column in the selection. Pressing CTRL+SHIFT+F3 or opening the “Create from Selection” facility accomplishes this job automatically and spares me a huge burden in case my table has plentiful columns.

    Step 4: Now I open in the cell F4 (the primary validation cell) the Data Validation tool of Excel. Validating the entry of a column title in cell F4 is straight forward for the knowledgeable Excel users. So I skip that part.

    Step 5: For the “Conditional Data Validation” part of the task, I open the Data Validation tool again in the cell below, that’s cell F5. I select the “List” type of data validation as usual and in the “Source:” field I simply write the formula

    =INDIRECT(F4)

    And presto!… All done… It works like a charm thereafter.

    Because NAMES in Excel must be a single string without white spaces, an extra tweak is needed when column titles are comprised of multiple words separated by blanks. Excel would replace the blanks with the underscore character “_” in the NAMES. Therefore, blank spaces in the primary data validation cell F4 need to be replaced by underscore. So, the “Source:” field formula for the conditional data validation cell F5 should read:

    =INDIRECT(SUBSTITUTE(F4,” “,”_”))
    ____________________________________

    I hope you and your loyal readers find this Method 3 handy and viable.
    Best regards Dear Excel Sorceress Leila.
    – Excel Gazi Deniz Hoca (hodja)

    • Leila Gharani July 18, 2019 at 7:35 am - Reply

      Thank you for sharing Method 3 in detail Deniz! It’s a great alternative to Method 2.

  6. Linkpak Digital July 25, 2019 at 7:45 pm - Reply

    Thank you for this helpful yet efficient method. I think this is the only method I find on internet that completely address the concept of ‘Dependent Dropdown Lists in Excel”.

    I was just wondering if this method could work on large data i.e.50000 records?

    • Leila Gharani July 25, 2019 at 9:52 pm - Reply

      I’m glad you like it. I haven’t tested it yet on 50k lines of data…

Leave A Comment

Share This