Excel Tip of the Week: Dropdown Lists

Today’s topic is creating a dropdown list.

In my past job I was in charge of several spreadsheets that required monthly updating. One of the excel tools I used regularly in these spreadsheets were dropdown lists. Instead of updating scores of formulas every month I linked the formulas to a dropdown list which I could easily update with the click of a button.

Here is how you can make your own dropdown list:

1. First, create the list you want. For example, the months of the year. (Bonus tip! Type “January” into a cell then, with the cursor still in that cell, click and drag the square in the bottom right of the cell downward. Excel will automatically fill in the rest of the months.)
2. Go to an empty cell. I prefer to place my dropdown box directly above my list. However, you could place your dropdown wherever you want within the workbook.
3. Go to the Data menu. Then in the “Data Tools” section click on “Data Validation.” Keyboard shortcut: Alt→A→V→V (Quick side note: Data Validation can be used for a number of different things besides just creating dropdown lists.)
4. The data validation box should pop up. On the “Settings” tab where it says “Allow” change that from “Any value” to “List.”
5. Now you should see a box labeled “Source.” Click the icon in that box and highlight the list you created.
6. Hit the enter key. Using my months-of-the-year example, your screen should look something like this:
7. Click “OK” and then congratulate yourself for making an awesome dropdown list.

Notice there is now a button floating to the right of your dropdown cell. You have to click that button to gain access to your dropdown list. When you navigate away from that cell, the button will disappear. For this reason I like to place a special border around my dropdown cell. You could also change the cell’s color or draw an arrow pointing to the cell; it doesn’t matter what you do as long as it is something that will remind you where the dropdown list is.