Q. How do I create a drop-down list to include in my spreadsheet?
A. Creating drop-down lists in your spreadsheets is a great way to ensure that only certain information is entered into a cell. For example, if you want a customer's state listed in a cell, you may want the person inputting the information to use only official U.S. postal abbreviations. With a drop-down list of only those abbreviations, employees would not be able to input "tn," "Tenn.," or anything besides the postal abbreviation "TN" for a customer living in Tennessee.
Using drop-down lists can also make inputting information faster by already having the inputs available. If you have cells where only specific information should be inputted, drop-down lists are a valuable feature to add. You can access an Excel workbook here and an accompanying video at the end of this article for creating a drop-down list.
To create a drop-down list, start by writing out the items that will be in it. Enter these somewhere in your workbook, perhaps on a separate tab. Be sure that the items are in an Excel table. If they are not, click anywhere within the data and click Ctrl+T to covert the range to a table.
Next, select the cell(s) where the drop-down list should appear and then choose Data Validation from the Data Tools group on the Data tab. See the screenshot below to see the Data Validation icon.
Clicking on the icon circled in the screenshot opens the Data Validation window. In that window, select the Settings tab and then, under Allow:, select List and, under Source:, select the cell(s) containing the list of items for the drop-down box (which you created in the first step of this walk-through). Do not include the header of your Excel table unless you want to include it as a drop-down option.
The Data Validation window has two checkboxes: Ignore blank and In-cell dropdown. Place a check next to Ignore blank if you want to allow the cell(s) with the drop-down list to be left blank. If you want to require the user to select an item from the drop-down list, do not place a check here. Place a check next to In-cell dropdown to create the drop-down list in each selected cell(s).
At this point, you could click OK and have a drop-down list in your designated cells. However, you have additional options. You could include an input message, which would make a message pop up when a user clicks on the cell with a drop-down list. This helps guide the user on what to enter in the cell(s). You could also include an error alert, which would display a message if a user tried to enter something that was not included in the drop-down list. This could either stop them from doing so or simply warn them or provide more information.
To create an input message, select the Input Message tab in the Data Validation window. Make sure that Show input message when cell is selected is checked. Next, you can optionally enter something under Title:. Lastly, enter a message under Input message: and click OK. See the Input Message tab of the Data Validation window below.
You can also create an error alert by selecting the Error Alert tab in the Data Validation window. Make sure that Show error alert after invalid data is entered is checked. Under Style:, you can choose between Stop, Warning, or Information. Stop will prevent a user from entering data that is not in the drop-down list and will display a message along with the icon . Warning and Information will not prevent a user from entering data that is not in the drop-down list. Warning will display a message along with the icon , and Information will display a message along with the icon . Once you choose the Style:, you can optionally enter an alternative title and/or message under Title: and Error message:. However, if you would like to allow users to enter data that is not in the drop-down list and not receive any warning or information, uncheck Show error alert after invalid data is entered. See the screenshot below for the Error Alert tab of the Data Validation window and the resulting error alert that appears when trying to enter data that is not in the drop-down list.
A completed drop-down list resulting from these steps is shown below.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
