- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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.
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.
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."
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.
In this tutorial, we used a simple example to demonstrate how we can auto-update a dropdown list in Excel.
Kickstart Your Career
Get certified by completing the courseGet Started