How to Create a Dynamic Drop-down List that Automatically Expands

Bottom Line: Learn how to create a drop-down list in a cell using the new UNIQUE function to make the list dynamic.

Skill Level: Intermediate

Watch on YouTube & Subscribe to our Channel

Download the Excel File

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I have also posted a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Building an Attendance Dashboard

This post is part 5 of a six-part series explaining how to build an interactive dashboard for attendance.  This attendance report was an entry for the Excel Hash competition.

Here are the other posts in the series:

Data Validation Lists

In this post, we're looking at creating a drop-down list that is dynamic.  In other words, it can expand or contract depending on changes to the source data.  Drop-downs are also called data validation lists, and I've written a post going into more detail about these handy tools that you can check out here: How to Create Drop-Down Lists in Cells.

The drop-down list we're looking at today is part of our attendance dashboard.  It's a list that we can select from in order to filter attendance data by department.

Dynamic drop-down list

The source of this dynamic data validation list has been created using the UNIQUE function.  UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range.  In other words, it removes duplicates from a data set.

For the list below, this function is scanning the many entries listed in the Department column of our data table and returning only one unique entry for each department.

UNIQUE function for department list

Setting Up the Data Validation List

To create the data validation list on the Dashboard sheet, start by going to the Data tab on the Ribbon and clicking on the Data Validation icon, which looks like this:

Data Validation Button

This will bring up the Data Validation window. (If you are working with a merged cell, you may get a pop-up box asking if you want to extend the Data Validation to those other cells. If so, just select Yes.)

In the Source field, you want to select that list of unique departments that I referred to above. Instead of identifying a range of cells (such as $J$2:$J$8), it's better to reference the spill range ($J$2#). Why? Because then, as departments are added or deleted from the source table, our data validation will automatically update as well.

Data validation window showing spill range

Phantom Drop-Down Icon

Just a quick side note. One little trick I used in building this dashboard is the inclusion of a drop-down icon that's always visible. Normally this icon only appears when you select the cell that contains the drop-down. But I've found that sometimes it is helpful for users to see that icon no matter where their cursor is on the sheet, so that they know there is a list available that they can select from.

So what I've done is to insert an image of the icon next to the cell in question, and hyperlink it to that cell. That way, when they click on the image, their cursor will move to the cell, and the real drop-down button will appear, covering the ghost icon.

Phantom drop-down icon

This just makes the worksheet a little more intuitive to someone who hasn't used it before, showing them how they can interact with it.

Here's a blog post explaining how to set that up: Drop-down List Arrow Always Visible for Data Validation.

Populating the Department List

The only thing that remains is to reference the filtered list from the Calc sheet in order for it to show on the Dashboard.

This goes for both the Employee and Hours columns.

Filtered employee list

Now, as you select the different departments from the drop-down list, the employees associated with that department will populate in the list.

Related Posts

Here are some other posts I've written that may be of interest to you because they touch on some of the same subjects:

  1. For those who do not have Office 365, you can learn how to make dynamic Data Validation Lists without the spill ranges here: How to Add New Rows to Drop-down Lists Automatically.
  2. This post will show you how to make secondary drop-down lists that are dependent on the selection made from a primary list. These are called cascading drop-down lists: How to Create Dependent Cascading Drop-down Lists.
  3. And finally, this post looks at several other Dynamic Array Formulas like the UNIQUE function that was mentioned above: Dynamic Array Formulas & Spill Ranges.


In our next and final video of the series, we will look at how to create the thundercloud and fireworks icons. These have conditional formatting that causes them to appear grayed out when department hours are above or below a set goal.

I hope this post was informative. If you have questions about how to create a dynamic drop-down list, please ask them in the comments below.


Your email address will not be published. Required fields are marked *

  • Jon (or anyone), let’s say my first three columns are ‘date’, ‘vendor’ & ‘amount’ and I want column four to be ‘expense’, with a dropdown list (of any number of items): A, B C & D, etc. and a fifth ‘detail’ column with a dropdown list (of any number of items): 1, 2, 3, & 4, etc. My goal is if the user choses item ‘A’ in the ‘expense’ column (four), then only a predetermined item, say item ‘1’, will be shown in the column five dropdown list and if the user choses item ‘B’ in the ‘expense’ column (four), then only a predetermined item, say item ‘2’ will be shown in the column five dropdown list. Is that possible?

  • What formula would you include to skip/exclude blank cells in your spill list? The UNIQUE function eliminates all but one of the blank cells. I have tried a few options and have not been successful. I should clarify that I am not using the spill list for a dropdown. Thanks!

  • When you put the OFFSET function into a cell it will “spill” into the cells below, just like this UNIQUE function does. Why does data validation support using the OFFSET function directly within the data validation dialog box, but it does not work when you use the UNIQUE function? For some reason you are forced to put the UNIQUE function into a cell & let it spill over and then data validate off of the spill over cell address $J$5#… but why? is there way to use the UNIQUE function directly within the data validation dialog box?

  • Good afternoon!
    Thank you for this great material. I just have a question. how can remove the headers from the datavalidation box? in your example, how can remove the “All depts” from the depts drop-down list?

  • I’m going to preface this with the fact that this is a complicated question, and I’m not necessarily married to the method I have attempted so far. I have a survey that is sent out quarterly each year asking people to provide a rating score (the same people can respond each quarter, but new people will be added along the way). We would like to calculate the percentage of people who improved between any 2 year/quarter time periods selected (e.g. between Year 1 Quarter 1 to Year 1 Quarter 4; Year 1 Q1 to Year 2 Q1; etc.). I for the life of me couldn’t figure out how to structure a formula that can look up and compare the ratings for the same person based on the 2 different periods of time selected, so I decided to make a helper table that shows each unique individual’s ratings for the 2 year/quarter periods selected (using Index match formulas and a list of unique participants based off of a 1-column pivot table). Unfortunately, the excel table with the index/match functions (and the field calculating if progress was made) doesn’t automatically expand based on the pivot table or spill range list of names. Is there a way to make the table expand automatically based on a separate list of names? Or is there another way I can create a table that will help to calculate this percentage (keep in mind we will be creating a table which compares these percentages across different positions/roles responding)? Thanks in advance!

    • I’m not super savvy with VBA, so if it can avoid using VBA that would be the most preferred. Also, if you need visuals of the data source table, and the helper table I have created, I can send those to you to provide more context to the problem at hand.

  • Good example of dynamic array usage.
    However, I use dynamic arrays namely to compute other values from the subset that I am getting. These new columns are referring to the dynamic arrays results or other columns that I added beside them.
    The problem that I have is that the formulas in these added columns do not adjust to the size of the dynamic array. I constantly have to manually expand or reduce their content to fit the size of the dynamic array.
    Is there a way to solve this situation?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter