Microsoft Excel, a tool for creating dynamic spreadsheets, has many features intended to make the user’s task easier. The drop-down list is one of them. It simplifies tedious data entry by offering predefined choices in a drop-down menu.
> Download: Excel Chart Templates” class=”hs-cta-img” id=”hs-cta-img-cb37302f-03e2-4d41-9a74-76b66031abbf” src=”https://no-cache.hubspot.com/cta/default/53/cb37302f-03e2-4d41-9a74-76b66031abbf.png” style=”border-width: 0px; margin-left: auto; margin-right: auto; display: block;”/>
What is the drop-down list used for in Excel?
The drop-down list in Excel is primarily used to improve the speed of data entry by saving the user from having to write long descriptions, especially when they are repeated many times in the spreadsheet.
It also makes it possible to limit the possible entries in a cell, to standardize the labels and to avoid typing errors, essential characteristics so that the sort, filter, grid or even pivot tables commands work correctly. Using a drop-down list ensures that the values entered are valid.
Finally, the drop-down list is also used in the drafting of questionnaires or forms in order to offer a selection of predefined answers to the respondent.
How to create a drop down list in Excel?
There are different steps involved in creating a drop down list in Excel.
The first of these is to enter the list of entries that will appear in the drop-down menu on a blank spreadsheet.
By selecting the values, clicking on “Data”, “Sort”, then choosing the “A to Z” option, all the elements of the list are arranged in alphabetical order.
In the second step, you must select the cell of the spreadsheet concerned by the drop-down list, go to the “Data” tab, then select “Data validation” and click on “List” in the “Allow” area. under the “Options” tab.
To retrieve the drop-down list, simply click in “Source”, then select the “List range” defined in the first step. The “Ignore if empty” box must be activated in case the user has the option of leaving the cell empty. Finally, you must activate the “Drop-down list in the cell” box.
It is possible to display a message when the user clicks on the cell concerned by the drop-down list. To do this, simply click on the “Input message” tab, activate the “When the cell is selected” option, type a title and a message (“Select a name from the list”, for example ).
Another feature available: insert a warning to notify the user that he has entered a value that is not present in the list. To do this, click on the “Error Alert” tab, check the box “When invalid data is typed”, choose a title and define a message.
To save time, the document author can also create the drop-down list directly in the “Data” tab by selecting the “Data Validation” command and simply entering the values separated by a semicolon in the ” Source”.
The drop-down list is then ready to be used.
To take your strategy a step further, download Excel Chart Templates.