- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.