Drop-down is a Microsoft Excel feature in which you can select an item from a list rather than typing its own values.
There are many ways to create a drop-down in Excel, but in this article, we will learn the three best, simple, and fastest ways to create it.
ALSO READ: How to merge cells in excel
Here are the three ways to create a Drop-Down list in Excel:
Method 1: Create a Drop-Down List in Excel Using Data from Cells
Assume you have a column of cells, as shown below:
Step 1: Now Select the Cell
Select the cell (or cells) where you want the drop-down list to appear.
Step 2: Now Open Data Validation
Go to the Data Tab that displays on the ribbon, then click on Data Validation in the Data Tools group.
Step 3: Now Set Up Data Validation
Inside the setting tab of the Data Validation dialog box, set the following:
- Allow: Choose “List”.
- Source: Click in the Source box, then choose the range of cells that contains the items you prepared. If your list is on another sheet, you can switch to it and choose the range.
- Then Click OK to close the Data Validation dialog box.
Step 4: Finalize and Preview the Drop-Down List in Ms Excel:
RELATED: How to freeze a row in excel
Method 2: Create a Drop-Down in Excel by manually entering data:
Creating a drop-down list in Excel manually involves the following steps:
Step 1: Go to Data Tab
Click on the “Data” tab in the Ribbon.
Step 2: Now Open Data Validation
Go to the Data Tab that displays on the ribbon, then click on Data Validation in the Data Tools group.
Step 3: Data Validation Dialog Box:
- In the Data Validation dialog box, go to the “Settings” tab.
- In the “Allow” drop-down menu, select “List”.
Step 4: Enter List Items:
- In the “Source” box, type the items manually , separated by commas. For example: Item 1, Item 2, Item 3, and Item 4.
- Then Click “OK”.
After clicking “OK” your Drop-Down is successfully created, and you can select your item by clicking on the ▼ icon.
Method 3: Create a drop-down List in Excel using the “OFFSET” formula:
To create a dynamic drop-down list in Excel using the OFFSET formula, follow these steps:
Step 1: First of all Enter Your List Items:
For example, assume you have the list of “Popular Authors” index as displayed below:
Step 2: Now Select the cell
Select the cell where you want to create Drop Down list (cell C4 in this model).
Step 3: Now Go to the “Data Tab” and Select “Data Validation” in the “Data Tools” Selection
Step 4: Data Validation Dialog Box:
- In the Data Validation dialog box, go to the “Settings” tab.
- In the “Allow” drop-down menu, select “List”.
In the Source field, type the following formula: “=OFFSET($A$2:$A$6)”. Make sure the In-cell drop-down option is checked.
Click OK. This will create a drop-down list of all the organic product names (as shown below).
Your drop-down list has now been created in the selected cell. Any new items added to column A will be automatically updated in the list.
