19

How to Add New Rows to Drop-down Lists Automatically – Dynamic Data Validation Lists

Bottom Line: Learn to create dynamic data validation lists. These in-cell drop-down lists automatically expand to include new rows that are added to the source data range.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

You can download the file I’m using in the video to practice on your own.

Dynamic Data Validation Lists Examples.xlsx (22.1 KB)

Dynamic Lists with Excel Tables and Named Ranges

Data Validation lists are drop-down lists in a cell that make it easy for users to input data. If you’ve never worked with data validation lists before, I suggest you start with this tutorial for creating drop-down lists in cells before moving on.

In today's post, I want to show you how to make your drop-down list dynamic.  In other words, your list can automatically be updated with new options when you add or subtract entries to your source range.

This is done in three simple steps:

  1. Formatting the source range to be an Excel Table.
  2. Naming the range.
  3. Telling the Data Validation rules to pull the named range as your source.

I’ll explain in more detail below.

Step 1 – Format the Source Range as a Table

To begin, we will format our source range to be an Excel Table. On the Insert tab, you’ll chose the Table button.  The keyboard shortcut for inserting a Table is Ctrl+T.

Insert Table Keyboard Shortcut

The Create Table window will appear, showing the range of cells that will be in your Table.  Since our column begins with a header (“Products”), we want to make sure the checkbox that says “My table has headers” is checked. If we don't check that box, the column title will be included in our source range and will appear as one of the options in our drop-down list.

Create Table Window Header Checkbox

If you haven’t used Tables before, I recommend checking out my Excel Tables Tutorial Video.

Step 2 – Create the Named Range

The next step in our process is to name our range for the “Products” Table that we just created.

On the Formulas tab in the ribbon, you want to select the Name Manager (or you could use the the Ctrl+F3 keyboard shortcut instead).

Name Manager Keyboard Shortcut

The Name Manager window will appear, and you will want to click on the New button.

Name Manager Window Select the New Button..

This brings up a new window that allows you to name your range.  I like to prefix my ranges with “rng” to make them easier to find in formulas. However, the naming is completely up to you.

The “Refers to” field allows you to select the range that you want to include.  The up arrow icon to the right of that field takes you to the worksheet. There you can highlight the selection that you want to use for your range.

Name and Define Range..

Once you have defined your range, you can click OK, and then close the Name Manager window.

Step 3 – Reference the Named Range in the Data Validation Source

Now that we’ve named our range, we just need to tell that name to the Data Validation window so that it knows where to pull from for our drop-down list.

To do that, start with the cell that you want the drop-down list to be added to.  Then access the Data Validation window by selecting the Data tab on the ribbon and clicking on the Data Validation button.

Select the Data Validation Button on the Data Tab

In the Data Validation window, under the Settings tab, we can type the name of our range into the Source field.

Data Validation Window Source Range

A shortcut to typing the name of our range is F3, which brings up a list of any ranges we’ve named.  We can simply click on the range we want in order to select it.

Data Validation Window Source Range F3 Shortcut for Names

Once you have selected the named range you want, click OK.  But before leaving the Data Validation window, you want to check the checkbox that says “Apply these changes to other cells with the same settings.”  This ensures that your drop-down list will be applied to similar cells in your worksheet.

Data Validation Window Checkbox to Apply to Similar Cells..

After we click OK, our drop-down list is now pulling from the Table that we have defined and named.  So now any time you alter the Table, adding or deleting rows, the drop-down list will remain in sync with those changes.

Drop-down list contains new items and will automatically update as items are added or deleted from the source Table

Alternative to Named Ranges – INDIRECT

There are alternatives to using the named range in the data validation source.  We can also use the INDIRECT function in the Source box, and reference the Table and Column name.  The formula uses structured reference Table formulas, and looks like the following.

=INDIRECT("Table3[Products]")

You can type the formula directly in the source box in the Data Validation window.  Just make sure the Table name and column name are correct.

Dynamic Data Validation with INDIRECT Formula

I presented this technique in my article on dependent drop-down lists.

One drawback with the INDIRECT technique is that if you change the Table or Column names, the formula will NOT be updated.  The drop-down button will still appear next to the cell, but you won't be able to click it.

Using the named range technique does allow you to change the name of the Table or Column. The reference in the named range will automatically be updated. Since multiple cells could be using this reference, it makes maintenance easier if you forget to press the “Apply these changes to other cells…” checkbox.  Therefore, I recommend using the named range method described above.

Dynamic Data Validation Lists Save Time

By making your Data Validation lists dynamic, you don’t have to worry about updating your drop-down lists every time you make a change to your source data.  This not only saves you time but hopefully adds a little peace of mind to your day. 😊

If you are interested in creating drop-down lists that are dependent on other drop-down lists, you can find out how to do that here. For example, if you choose Fruit in your first list, the options to choose from in your second list might be Banana, Apple, and Orange.  But if you change your choice to Vegetable in your first list, the options in the second list change to Celery, Carrot, and Broccoli. This function is helpful if your lists drill down several levels or involve steps.

I've also created a free tool that helps you to search a data validation list, so you don't have to scroll through lots of entries to find the one you want. You can get that free search tool here.

I hope all of this information is helpful! If you have any questions about making your drop-down lists dynamic, please let me know in the comments below.

Free Productivity Workshop

Speaking of adding peace of mind to your day, next week I'm co-hosting a live online class on productivity.

My good friend and international expert on productivity, Colin Boyd, will actually be conducting the training.  I'll be there with you to take notes, ask questions, and learn a ton.

Here's a video where Colin and I explain more about the class.

Colin has coached thousands of people on productivity at companies such as HP, Coca Cola, and Fuji Xero to name a few.  His goal is to help you feel back in control of your life and clear on how to get ahead.  This applies mostly to work life, but also great for finding that balance with life at home.

If you are feeling:

  • overwhelmed with the number of tasks on your plate,
  • like there is never enough time in the day,
  • or you don't know how to you will ever clean your email inbox…

Then this training is for you (and me!).

The free live online workshop is Thursday August 23rd at 1pm Pacific Time.

Click here to learn more and get registered today

You will receive an email with all the details on how to attend after you register.  I look forward to seeing you there! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 19 comments
Ed - August 30, 2018

FYI – this is an excellent technique to also use in conditional formatting. Conditional formatting ranges do not understand tables, but if you name the range as you describe, the range properly expands as the table does.

Reply
Musah Ibrahim - August 15, 2018

Thanks for your excellent work. In fact you are blessed with knowledge in excel

Reply
Lisa - August 15, 2018

Hi, when I click on the link for the productivity workshop, I can’t get there because our IT walls prevent it – due to the fact that the site is not secure. Alternatives?

Thanks;

Reply
Elmarie Kaufman - August 14, 2018

I have built a dynamic dashboard file with 100000+ lines of data on electric meter usage. To be able to select ranges of data for graphing, I have created a small area of the data page that is an output range for unique record filtering of the months in the main data table. This unique month list then serves as the validation list for my drop-down lists to select a ranges of months on the graphing tab of the workbook. Is there any way to get the unique list to populate automatically, or is this something I will continue to have to perform the advanced filtering on after I add new data to the file?

Reply
    Jon Acampora - August 14, 2018

    Hi Elmarie,

    Great question! Yes, there are actually quite a few ways to automate this.

    You can use Power Query to Remove Duplicates, then output a Table with a list of unique values. That Table column would be the source of your data validation list.

    If you’re not on a version of Excel that has Power Query (see my article on the complete guide to installing Power Query), then you can also use a pivot table to create a list of unique values. You can then use a dynamic named range with the OFFSET function (explained in this article on dependent drop-down lists) as the source of the data validation list.

    We could also use VBA to get a list of unique values. There is actually a feature for this in my List Search Add-in. I also have an article on 3 ways to remove duplicates (#2 is a macro). However, you probably won’t need VBA for this unless it is part of a larger process that already uses macros.

    I hope that helps.

    Reply
Peter - August 14, 2018

Jon, great training as usual! This will definitely save me time updating my lists. One concern I have is if I delete an entry in the defined list (things change!), I know it will disappear from the drop-downs for new entries, but what about EXISTING entries? Will that value go away for those as well, and if so, is there a way to protect them? Just wondering. Thanks again!

Reply
    Jon Acampora - August 14, 2018

    Hey Peter,

    Happy to hear it will save you time.

    Great question! Existing entries will NOT be changed when you delete data from the source range of the data validation list. I briefly explained this in my previous video on creating data validation lists, but did not address this question directly.

    If you delete an item from the source list, the error check warning (green triangle in top left corner of the cell) will NOT appear. However, you can use the Invalid Data button on the Data Validation drop-down menu to highlight all cells that contain an error. Here is a screenshot.

    In that example I deleted Baseball from the Products Table. This will highlight cells that break the rules, and you can go fix them if needed.

    You can then press the Clear Validation Circles button to hide the circles.

    The main point is that changing the validation rules will NOT change existing values in cells. I hope that helps.

    Reply
      Peter - August 15, 2018

      Perfect Jon, thank you!! It’s great to know the data is safe as the lists are updated!

      Reply
Jon Peltier - August 14, 2018

It isn’t necessary to name the input range of the table. If you use the cell address of the range, adding items will expand the table and the address will change to include the new entries.

Using a name does make it more convenient.

Reply
    Jon Acampora - August 14, 2018

    Hi Jon,

    I don’t find that to work when typing data in a new cell directly below the Table. The Table does expand, but the data validation source range is not updated to include the new row. I show an example of this at the 9:30 mark in my previous post and video on creating drop-down lists.

    Maybe I’m not defining the range correctly, or not understanding what you are referring to?

    Thanks!

    Reply
      Jeff Weir - August 14, 2018

      Jons: If you just use a cell ref instead of a named range, then most times it works, but sometimes it doesn’t. Definitely a bug, that sometimes seems to go away when you restart, and possibly seems related to particular files. I’ve never been able to consistently recreate the issue. But using Names gets around the issue. Plus…I **like** calling Excel names 😉

      Reply
        Jon Acampora - August 14, 2018

        Haha thanks Jeff! I can’t get the cells refs to auto extend at all for data validation. It works fine for formulas that refer to a Table column using regular range refs, but not data validation. hmmm…

        Reply
        Jon Acampora - August 14, 2018

        Oh nevermind. It only works for me if the Table and the cell with data validation on the SAME sheet. If they are on different sheets, it doesn’t work.

        Reply
        Jon Acampora - August 14, 2018

        That’s probably another reason to use named ranges. If you create the data validation cell on the same sheet as the source using range refs, then copy to another sheet, the data validation does not work. It doesn’t update the range ref to include the sheet ref.

        Reply
NG - August 14, 2018

Hi Jon,

Great Video.

Is is possible to use the table named range but still have the facility to include a dynamic search as you type

Reply
    Jon Acampora - August 14, 2018

    Thanks NG! I’m not sure what you mean. There is no direct way to search data validation lists in cells within Excel. However, my List Search Add-in allows you to search these lists and filter down results as you type. The add-in does work with data validation lists that use a Table as the source range.

    I hope that helps.

    Reply
Miaousse - August 13, 2018

Hello,

I’ve found a simpler way with creating a named range.I still use a Table.

So instead of creating a named range for the validation list, in the field of the validation i write this simple formula =indirect(“myTable[theColumn]”)

Reply
    Jon Acampora - August 14, 2018

    Hi Miaousse,

    Thank you for the suggestion! I presented this technique in my article on dependent drop-down lists, and forgot to mention it hear. I’ll update the post to include it.

    One drawback with the INDIRECT technique is that changing the column name of the Table will NOT update the formula in the data validation source.

    Using the named range technique does allow you to change the name of the Table or Column. The reference in the named range will automatically be updated. Since multiple cells could be using this reference, it makes maintenance easier if you forget to press the “Apply these changes to other cells…” checkbox.

    I hope that helps.

    Reply
Hussain - August 13, 2018

Hello sir,
I was working on excel workbook with vba it worked well on my PC it works but on other PC it doesn’t work

The error I get it’s
Catastrophic error then out of memory error

System Error &H8000FFF (-2147418113)

Please help

Reply

Leave a Reply: