What´s a dynamic data validation drop down list in Excel, you say?

Well, as you add new data into your Excel Table, your drop down list automatically gets updated.

That is a cool feature and it means that you do not need to update your data validation source reference each time you update your data with a new entry, saving you heaps of time in the long run.

In a few steps you too can create this:

1. Convert your data to an Excel Table (Ctrl+T)

2. Select your table´s column by hovering over the Excel Table and left clicking when the arrow pointer shows

3. In the ribbon go to Formulas > Define Name > enter a custom name with no spaces (we will put this name in step 5) and press OK

4. Click in a cell and go to the ribbon and choose Data > Data Validation > List

5. In the Source box enter the name you created in Step 3 and press OK

6. If you want to add extra data into your Excel Table, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.

7. Enter a new entry and this will automatically be updated in your drop down list.


Dynamic Data Validation







If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation. You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...
Consolidate in Excel with the CONSOLIDATE Tool The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges into one new range. You would use this feature when you have a single text column on the left and the column has many duplicate values. First you need to select a blank cell o...
How to Calculate Percentage in Excel In calculating percentages in Excel, there are a lot of ways that you could do this: What is the percentage of a number? What is the percentage change of value i.e. The percentage increase or decrease What is the proportion against a total value? Let us go over ...
Show The Percent Of With Excel Pivot Tables   Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF calculation. It will display the value of one item (the Base Field) as the percentage of another item (the Base Item). This optio...