Create Excel drop-down list

Who I am
Aina Prat Blasi
Author and references

Create Excel drop-down list it makes life easier for the user in many contexts, for example when it is necessary to recursively insert certain values ​​or strings into cells. How to create drop down list in Microsoft spreadsheet? This is what we will see in this tutorial.

The drop-down list in Excel is a useful feature among the many possibilities offered by the most popular spreadsheet. If you work with the application regularly, you have probably had to enter the same words several times in multiple cells.

If so, there's nothing like a drop-down list that lets you delete these words in two clicks rather than having to re-enter them. This is also true in cases where you need to produce a document that needs to be completed by other people and you want to limit certain cells to specific choices.

Creating Excel drop-down list is actually a very simple task. To do this, just follow the two-step procedure below.

Create Excel drop-down list

First step: create a range of values

As a first step, you need to enter the range of words or values ​​you want to have in the drop-down list. This range is required because it is the database serving as the list whose values ​​are to be retrieved from a source. You can create the range on the current sheet, but we recommend that you do it on another sheet. It is necessarily more practical:

  • Open Excel
  • In the bar at the bottom of the screen, click the button with a + sign to create a new worksheet
  • Then enter the values ​​to be included in the drop-down list
  • We recommend that you sort them alphabetically for easy selection. To do this, select the set of values ​​and click on the option Sort and filter at the top right of the toolbar (section Modification ) and click Pull from A to Z
  • Rename the list now. To do this, select the range of values ​​again and click in the field above column A (where A1 is marked on the capture). Enter the name you want and confirm 

Step 2: Create Excel drop down list

Once the data list has been created, all that remains is to access the actual drop-down list.

  • Return to the main worksheet by selecting it at the bottom
  •  Go to the cell where you want to add the drop-down list
  • At the top of the main menu, click Data
  • In the tab Data tools, click on Data validation e then on the option with the same name
  • In the Validation Criteria section of the dialog box that appears, click the drop-down menu below the field Allow and select List
  • In the Source field, enter the name assigned to the previously created range and validate it 

You've just created your Excel drop-down list, a task that's less easy than it sounds after doing it once or twice.

Further Reading:

  • How to track changes in Microsoft Excel
  • How to use absolute references in Excel
  • How to enable or disable AutoCorrect in Excel
  • What is a CSV file and how to open a .csv file?
  • What is Microsoft Power Automate Desktop and why you need it

add a comment of Create Excel drop-down list
Comment sent successfully! We will review it in the next few hours.