Dropdown list is such a useful function that I use it in most of my Excel sheets. A dropdown list is a list of predefined values where you can select a value with a click of your mouse. In addition to convenience, dropdown lists offer greater accuracy in data entry. And you don’t need to be an Excel expert to create a dropdown list in Microsoft Excel.
In this guide i will walk you through the steps to create dropdowns in Excel. Assume, that we want to create dropdowns for Household Expenses that should have the following categories:
Travel, Car Repair, Groceries, Credit Card Bill, Pet Items, Miscellaneous Shopping and Others
These are only a few items for the illustration of list creation process. You can add as many items as you want in your list. To create a list, follow these steps.
Open the workbook and type all items in one column. Select all the items in the list and go to the Name box (left corner of the Excel window, under the ribbon). Click in the box and give your list a name. In this example, we have given it the name “Expenses”.
Now, click the cell where you want to create the dropdown. This cell may be on the same sheet or any other sheet in the same workbook. Don’t worry about selecting all the cells where you want the dropdown list to appear. You can copy the list to all cells afterwards. Click the Data tab on the ribbon and select Data Validation under Data Tools
On the settings tab, click Allow and choose List. Go to the source field and enter equal sign (=) followed by the name of the list. In our example it will look like following: =Expenses
You will see a little arrow next to the cell. Clicking this arrow will bring the dropdown menu, and your can select the item you want.
If you prefer keyboard, use Alt + up or down arrow to bring the dropdown menu.
To copy the list to other cells, just copy and paste the cells.
To edit the settings, select the cells containing the dropdown list and go to Data Validation under Tools group.
If you want to change a list item, simply go the column where you have typed the list items and edit.