You are probably familiar with drop-down lists from one of the numerous forms that each of us has filled out somewhere on the Internet. These are the fields with the small, downward-facing arrow on the side, which presents a selection of possible entries for the field – of course, if you click on the arrow.
Excel also has the function of creating dropdown lists. Their use is always useful when the content in columns is repeated frequently and you don’t want to type every time or when you want to limit the end-user of your document to a few options.
CREATE A DROPDOWN LIST USING AN EXAMPLE
I have decided to create an overview of my DVD collection, which has grown quite extensive over the years. To save myself unnecessary work, I would like to create a dropdown list for the Genre column.
THE FIRST STEP: SET THE DROPDOWN VALUES
First I have to tell Excel what values I would like to have in my dropdown list. To do this, I open another table and enter the data there one after the other in a coherent row or column and without empty cells.
THE SECOND STEP: CREATE THE ACTUAL DROPDOWN LIST
Next, I mark all the cells in my list, right-click on them once and choose Define Name. This opens a window in which I enter a suitable name for my dropdown list in the Name field. This must not contain any spaces and will not be visible anywhere later. However, I need it to create a connection between my selection and the actual dropdown list. In our example, I choose Genre and then confirm with OK.
THE THIRD STEP: INSERT THE LIST
I switch back to the table in which the dropdown list should appear and select the cell in which I want to insert the list. Now I select the Data tab and click on Data Review here. A window is opening. In the Settings tab, under Allow, select the List entry. A source I enter an equal sign (=), followed directly by the name of the list previously created. So for us, that would be = genre.
I make sure that the checkmark for cell dropdown is set. With another checkmark at ignoring empty cells, I allow that the user does not have to select an entry. Finally, you can confirm with OK. That’s it, we’re done! In my field, I now have a full dropdown list that I can copy and paste quickly to the entire column. Now I don’t have to enter every genre by hand.
A FEW ADDITIONAL TIPS
In the Data Check window, you can use the Input Message and Error Message tabs to further refine your dropdown list. These options are particularly useful if you are not the only user of the Excel folder and you want to give your colleagues or customers a few pointers along the way. Under the input message, you can create a note that appears when you select the dropdown list. Simply enter a suitable title and text.
CONCLUSION
Dropdown lists are quick and easy to create in Excel and can save a lot of work. They are also used in all forms that are filled out by other users and in which only very specific entries are to be permitted. With a few clicks, you can easily get to the desired result.
