How to create a drop-down list in Excel

One of Excel’s many useful features is the ability to create drop-down lists that can then be added to a spreadsheet. These are great when you want people to answer a question, using preset choices.

We’ll take you step by step through the process of creating a drop-down list in Excel.

Set the contents of list

The first thing you’ll need to do is open up a new Excel worksheet. In the left-most column enter the various contents you want to appear in your list. Each entry needs to occupy one cell, have no blank cells between them, and all must  reside in the same column.

When you’ve assembled your list, highlight all of the entries and right-click on them. From the menu that appears select Define Name.

This brings up a window with the heading New Name. In the Name field enter a title for your drop-down list, being sure not to include any spaces.

Now click OK.

Add down-down list to a spreadsheet

Open up an existing, or new, worksheet into which you want to place the drop-down list. Highlight the cell where the list needs to appear then go up to the menu bar at the top of the page and select Data>Data Validation.

In the box that appears you’ll see three tabs – Settings, Input Message, and Error Alert – which we’ll go through one by one.

The Settings tab will open by default and in here you’ll see two drop-down menus. Click on the Allow menu then select List. A new option will now appear at the bottom of the window, entitled Source. Click on this and then enter an = sign followed immediately by the name of your list.

Make sure the In-cell dropdown box is ticked and, if you want people to be able to leave the field blank rather than choose from your list, tick the Ignore blank box too.

With that completed you can move onto the Input Message tab, which allows you to create a message that will appear when a user clicks on the dropdown cell. This can be handy if the question you’re posing is a complicated one.

Just add a brief title in the relevant field, and then enter the explanation in the main Input message box. If you don’t need this feature simply untick the Show input message when cell is selected option at the top.

The last tab is for the Error Alert. This acts in a similar manner to the Input Message, in that it will inform the user if they enter an invalid response. Again you can enter a title and message, but this section also has a setting for Style.

Clicking on this will open up three available icons to accompany your message: Stop, Warning, and Information. Select the one that you feel matches the tone you’re after or to dispense with an alert message altogether untick the Show error alert after invalid data is entered box.

Click OK and you will now see an arrow in the cell you selected for the dropdown list. When you click on this your range of answers should appear as if by magic.

That’s it. You’ve added a dropdown list to your worksheet. Now people will be amazed by your advanced Excel-Fu and bear you upon their backs to celebrate your greatness. Or they might just select one of the answers from the list. Either way, it’s a winner.