Create drop-down List in Excel Using Formulas
Aside from choosing from cells and entering information physically, you can likewise involve a recipe in the source field to make an Excel drop-down list. Any recipe that profits a rundown of values can be utilized to make a drop-down list in Excel.
For instance, assume you have the informational index as displayed underneath:
Here are the moves toward making an Excel drop-down list utilizing the OFFSET formula:
Step 1: Select a Cell
Select a cell where you need to make the drop-down list (cell C3 in this model).
Step 2: Go to the Data Tab and Select Data Validation in the Data Tools Selection
Go to Data –> Data Tools –> Data Validation.
Step 3: Select List in Validation Criteria
In the Data Validation popup, inside the Settings tab, select List as the Validation Criteria. When you select List, the source field shows up.
- In the Source field, enter the accompanying equation: “=OFFSET($A$2,0,0,5)”. Ensure that the In-cell dropdown choice is checked.
- Click OK. This will make a drop-down list that runs down all the organic product names (as displayed underneath).
Note: If you need to make a drop-down list in different cells at one go, select every one of the cells where you need to make it and afterward follow the above advances. Ensure that the cell references are outright (like $A$2) and not a family member (like A2, or A$2, or $A2).
How does the OFFSET formula Work?
In the above case, we utilized an OFFSET capability to make the drop-down list. It returns a rundown of things from the reach A2:A6. Here is the sentence structure of the OFFSET capability: =OFFSET(reference, lines, cols, [height], [width])
It takes five contentions, where we determined the reference as A2 (the beginning stage of the rundown). Lines/Cols are determined as 0 as we would rather not offset the reference cell. The level is determined, however, 5, as there may be five components in the rundown.
How to Create a Drop-Down List in Excel
A drop-down list offers users a selection from predefined options, commonly used on sites/applications for easy navigation. Dynamic arrays in Excel 365 allow us to create drop-down lists quickly, without complex setups.