How to create a drop-down list in Excel?

Drop-down list in Excel is a valuable feature that can greatly improve data entry and validation in your spreadsheets. By learning how to create a drop-down list in Excel, you can enhance data accuracy, simplify user interaction, and create user-friendly forms. Whether you’re managing inventory, conducting surveys, or organizing data, this skill allows you to take control of your Excel workbooks. Embrace the versatility of drop-down lists to excel in data management, making your spreadsheets more efficient and user-centric. It’s an essential tool for anyone looking to streamline data input and enhance data quality in Microsoft Excel.

Create a drop-down list in Excel from Data Validation

We will create the Month name drop-down in cell D1. First, keep the cursor on cell D1 to show the drop-down list there.

From Data Tab select Data Validation

In the first method we will create drop-down list through writing the month name directly in the ‘Source’ box, see the below:

From the Allow section select List from the down arrow and then write the month name separated by a comma in the Source box, then click OK.

Month names will appear in the drop-down arrow in cell D1, select the month from the list.

In the second method we will create drop down in Excel worksheet by referencing the source cells in the ‘Source’ box, see the below

We will create Month name dropdown list excel in cell D1. First keep the cursor on cell D1 to show the drop-down menu (list items) there.

From the Allow section select ‘List’ from the down arrow and then click on the Source box to refer the source data and then click OK, see below:

You will the see the drop-down arrow in cell D1, select the month from the list.

Add new item or delete item from the excel drop down list

To add or remove items to drop-downs, follow the below steps.

To Add new item in the drop-down list

If you follow to write directly in the ‘Source’ Box then write new item after the existing items. Here we added new month Jul after the existing month names. The new month name appeared in the drop down list accordingly.

To delete item from the drop down list in Excel

Just delete the item (here we delete Jan and Feb) from the Source box. You see the result in drop-down list in below image that Jan and Feb are no more in the drop-down list.

Add item through reference the source in the ‘Source’ Box

First add the item in the reference source, see the below that we added new month name Jul at the bottom of the table

In the Source box select the source range from A1:A8 to add new month i.e., Jul in the drop-down list

If you use cell reference as source to create drop-down, to delete the item you just remove the cell/cells from the reference source and drop-down will updated automatically.

Say, we want to remove Jan and Feb from the drop-down list

Select the Jan and Feb cells, then right click and select Delete. From the Delete pop up select ‘Shift cells up’. Jan and Feb month deleted from the table.

The drop down is update automatically

See the source reference in the Source Box, source reference adjusted to range A2:A6 before which was A2:A8

Dynamic drop-down in Excel – how to create drop down menu in Excel?

Select the cell (here D1) where you want to create the drop-down

In Data Validation window write following OFFSET formula in Source

=OFFSET ($A$1,0,0, COUNTA ($A: $A),1)

And click OK.

How OFFSET function works in Excel?

Syntax of OFFSET function: OFFSET (reference, rows, cols, [height], [width])

Total 5 arguments are used in OFFSET function. In our example arguments are:

Reference: A1 is the reference cell

Rows: number of rows up or down. We move 0 rows up or down

Cols: number of calls to left or right. We are using single col so 0 col

Height: we use COUNTA function in col A:A to count the values in col A that are not empty.  When you add one item in the list COUNT(A:A) considers it accordingly.

Width: here width is 1

Now if you add two more months in our monthly list say add Aug and Sep then drop-down will be adjusted dynamically.

 

Aug and Sep are added in the month list which are reflected in the drop-down list.

Now we delete Jun, Jul, Aug, and Sep from the month table, in the right-side drop-down list updated accordingly

How to use Excel Table to create dynamic drop down list?

To create Excel Table, click any cell of Month list and press CTRL+T then Create Table pop up appears and click OK

Here our Excel Table name is ‘Table1’ and column name is ‘Month’

Now use the Excel Table reference in Data Validation Source to make the dynamic drop-down. We will use INDIRECT function to create the reference.

Drop-down list looks like below

Now if you add July in the table, the drop-down will be adjusted dynamically.

If you delete any month form the table, you need to adjust the excel table accordingly.

Drag the bottom right corner icon to last month’s cell.

Use a drop-down list in Excel Workbook

1. Data Validation:
   • Ensure data accuracy by restricting input to predefined options in a drop-down list.
2. Survey Forms:
   • Create user-friendly survey forms where respondents can choose from a list of answers.
3. Inventory Management:
   • Manage inventory or product databases by allowing users to select items from a list.
4. Data Entry:
   • Simplify data entry by providing a list of predefined choices for users to select from.
5. Financial Modeling:
   • Improve financial models by using drop-down lists for scenario selection or category choices.
6. Dynamic Reporting:
   • Enhance interactive reports and dashboards with drop-down filters for customized views.

Creating a drop-down list in Excel not only ensures data consistency but also makes your spreadsheets more user-friendly and efficient for various tasks, from data entry to analysis.

For ready-to-use Dashboard Templates:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts