Microsoft Excel: How to Create a Dropdown List

Making a dropdown list in your Excel spreadsheet can build the effectiveness of your work. This may prove to be useful, particularly when you need your colleagues to give certain data that might be significant to the organization. By utilizing an Excel dropdown show, you can control precisely what can be gone into a cell by giving the clients an alternative to choose from a pre-characterized list.

When you add a dropdown rundown to a cell, a bolt will be shown alongside it. Tapping on this bolt will open up the rundown and give the client the alternative to pick one of the things on the rundown. This won't just spare you space on your spreadsheet yet in addition influence you to look like a superuser and inspire your collaborators and manager. Here's a well ordered guide on the most proficient method to make a dropdown list in Excel.

Step 1: Define the Contents of Your List

1. Open up a new Excel worksheet and put down the contents you want to appear on your list. Make sure each entry occupies one cell, and all entries are vertically aligned in the same column. Also, ensure there are no blank cells between the entries. In our case our dropdown menu will open a list of cities to choose from.

2. Once you’re done assembling the list, highlight all the entries, right-click on them, and select “Define Name” from the menu that will show up.

3. This will open a new window with the title “New Name.” Choose a name for your list and enter the same in the “Name” text box.

4. Click “OK.”

Step 2: Adding Your Dropdown List to a Spreadsheet

The next step is to add your dropdown list to a spreadsheet. Here’s how to do it.

1. Open a new or existing worksheet where you want to place your dropdown list.

2. Highlight the cell where you want to place the dropdown list. Click the “Data” tab, then locate the “Data validation” icon in the data tools section and click on it.

3. A data validation box will appear that has three tabs: Settings, Input Message, and Error Alert. In the Settings tab, select “List” from the “Arrow” dropdown list. A new option titled “Source” will now appear at the bottom of the window. Click the text box and then enter a “=” sign, followed by the name of your dropdown list. In our case it should read =cities.

The “Ignore blank” and “In-cell dropdown” boxes are checked by default. With the Ignore blank cell checked, it means it’s okay for people to leave the cell empty. But if you want every user to select an option from the cell, uncheck the box.

4. Click OK. That’s it. You have added a dropdown list to your spreadsheet.

With that completed, you can now proceed to the Input message tab.

Step 3: Set Input Message for Data Validation (Optional)

At times you may want a message (with description) to pop up when the cell containing the dropdown list is clicked. In that case you’ll need to click the “show input message” box in the Input message tab. You’ll also need to fill out the title and the input message in their respective boxes. Your dropdown list should now look something like the following image.

The last tab is for the error alert. Once defined, this one sends an error message if someone enters invalid data – data that is not on the list.

Wrapping Up

Dropdown lists are very common on websites and are very intuitive for the user. Their versatile nature renders them useful in virtually all industries. Be it in surveys, the business world or even in schools, you’ll always find the need to use Excel dropdown lists.