Dropdown Tools Excel 2016 Update Its Data

Share 0 Tweet 0 Related Tips Dependent Drop Down List in Excel: A dependent drop down is all about showing values in a drop down list according to the selection.

You can use a drop down list with your dashboards, charts or even with normal data.

While using it, one thing always gave me nuts that you need to update its data source whenever you add a new entry.

The more you add data he more frequently you need to update it.

For this, the best solution is to use a dynamic drop down list in which you dont need to update data source again and again.

But first of all, let me show you an example that why your drop down list should a be a dynamic one.

In below example, we have two different drop down lists in which we have used the same data source.

When you added a new entry into the source list, that entry is automatically updated in the second drop down list.

On the other hand, there is no change in the normal drop down list.

And, if you want to update your list, you need to update your data source range.

If you are using Excel 2007 or above then you can use an excel table and in case you are still with Excel 2003, you can use a named range.

Using Excel Table to Create a Dynamic Drop Down List The best method to create a dynamic drop down list is using an Excel table for source data.

Here are the simple steps to create a dynamic drop down list in Excel.

Select your list.

Go to Insert Table Tables Table.

Click OK. Now the next step is to refer to the table range data source and for this we need to use below formula.

Now you have a dynamic list which will update instantly when you update your source list.

Note: If you try to directly refer the table range as a source it will return an error like below.

Dynamic Range for a Drop Down List with OFFSET Function If you are still using Excel 2003 or a lower version, you can use offset function to create a dynamic range and then you can use that dynamic range for creating a drop down list.

Here are the easy steps to create a drop-down list using a dynamic range.

In name input box enter a name for named range (Here I am using monthList2003).

Now you have a dynamic range monthList2003 and you can use this range name to create a dynamic drop-down list.

How does this work The formula which we have used to create a dynamic range will count the values from the list and expand the source range accordingly.

Conclusion Using a dynamic drop down list is a big time saver.

And, the best part is, its a one-time setup, you dont need to apply it again and again.

I hope this tip will help you to get better in Excel but now you need to tell me one thing.

Have you ever used a dynamic drop down in Excel Please share your views with me in the comment section, Id love hear from you and make sure to share this tip with your friends.

Dropdown Tools Excel 2016 Update Its Data

Dropdown Tools Excel 2016 For Mac