How to Auto-Update a Dropdown List in Excel?

A drop-down list is used to choose a single item from the list of data. When we add an item to the source data, we can see that the list will not be updated. A drop-down list is used to select an item from many items, but if we try to add an item to the list, we need to create a new list again. However, there is a way in Excel to auto-update drop-down lists when new data is added to the sheet. This tutorial will help you understand how we can auto-update drop-down lists in Excel.

Auto-Update a Dropdown List in Excel

Here we will use the offset formula when creating the drop-down list. Let us see an uncomplicated process to understand how we can auto-update drop-down lists in Excel. When we create a drop-down list, we do not use any formula, but we can complete this process by using the formula in the general method.

Step 1

Let us consider an Excel sheet from which we can create a drop-down list, and the data will be like the below image.

To create a drop-down list in the general method, click on the list you want to add and click on data validation under Data, then select list, then click on OK to create a drop-down list.

Step 2

Now to create a drop-down list that auto-updates Click on the cell and click on data validation, then in the pop-up list, select "Allow" and enter the formula as =OFFSET($A$2,0,0,COUNTA(A:A)-1) in the source text box and click "OK."

Step 3

Now, every time we enter new data in column A, it will be directly added to the drop-down list, as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can auto-update a dropdown list in Excel.

Pradeep Kumar

Updated on: 12-Jan-2023

7K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started