How To Create A Drop-Down List In Excel: Quick & Easy Methods

Do you want to know how to create a drop-down list in Excel to increase your efficiency and proficiency? Microsoft Excel is more than just an old-school computer program that our parents use at their desk jobs. When used effectively, it can be a rewarding tool in your arsenal.

Whether you are a business owner, a professional, need to manage your household finances or schedule, or simply need a budget, keeping things organized is key to success. There are tons of apps and websites to help you stay organized nowadays and, old as it is, Microsoft Excel remains dependable and faithful to that task.

Guess you cannot beat the classic indeed.

What are drop-down lists in Excel?

A drop-down list helps the user select text or values from a list of options, instead of typing them manually in a cell. This allows you (the creator) to control all data entries for important cells.

Microsoft Excel drop-down lists are one way to simplify data input within Excel, which is helpful where time is money. The drop-down list is the most popular data validation tool in Excel.

Here’s a quick step-by-step guide to creating drop-down lists.

How To Create A Drop-Down List In Excel

Drop-down lists control data entry. However, before you insert the drop-down list, you need to decide what data entries should be allowed. This is what we call the ‘list items’.

Pick anywhere you want to store the drop-down list of items. It could be in the same sheet, another sheet, or even in another Excel file. Write the desired drop-down menu options in a list.

Simply move your cursor to the ‘Data’ tab and left-click it.

Now click ‘Data validation’ in the middle of the ribbon.

Note: (Do not click the little arrow – simply click the top part of the button).

Now, a dialog box called ‘Data validation’ should appear. This is where you choose the settings for your drop-down list.

In the data validation dialog box, click the drop-down list and pick what kind of data validation method you want to use. Select ‘List’ from the… well… list!

Remember back in step 1 where you chose the allowed options (list items) for your drop-down list? Now it is time to connect that list of options with the drop-down menu.

Left-click once in the ‘Source:’ field.

Then go to the location of your list with the allowed values that you wrote in Step 1. Select the cells containing your drop-down list items.

And click ‘OK’.

Pro Tip: As you can see from the picture above, a few empty cells are included at the bottom of the source range. This is so you can add new drop-down list items to the source just by writing in the blank cells.

This makes it so much easier than going back and changing the source range size every time you add new items. Also, it is much easier than creating a dynamic drop-down list, where the source range automatically adjusts to the data.

Once you click ‘OK’, your drop-down list is ready for use.

What to do if you get an Error alert

Drop-down lists are mainly used for selecting data entry instead of typing it manually. However, the user can do manual data entry in the cell that contains the drop-down list.

But if the user enters invalid data, an error alert will show. The default error message doesn’t give a great indication of what the user can actually write in the cell.

To fix it, simply go to the ‘Error alert’ tab after clicking ‘Data validation’.

From here, make sure there is a checkmark in the “Show error alert after invalid data is entered”. Then, add the title and error message and choose an icon if you don’t like the default.

Hit ‘OK’.

Now, this error message pops up whenever invalid data is entered into the cell.

If you don’t check the “Show error alert after invalid data is entered” checkbox, anyone can write anything in the cell with the drop-down list.

Input message in Excel Drop-down lists

Drop-down menus make it harder to input something wrong in your spreadsheet. But the ‘Input message’ addition makes data entry even easier – and more bulletproof.

When creating a drop-down list, you can add an input message from the ‘Input message tab’ in the data validation dialog box.

Simply type your input message and it will appear when the user selects the cell containing the drop-down list.

The input message is an easy way to improve the user experience of your spreadsheets. This is something most spreadsheet creators neglect.

How to add color to a drop-down list in Excel

Color can be a powerful element in an Excel drop-down list, and it is very easy to incorporate. You can add conditional formatting rules to the cell containing the drop-down list.

Here’s how to create a color-coded drop-down list with conditional formatting

1. Mark your drop-down cells, which in this case are in column A, and go to Home > Conditional Formatting then select New Rule.
2. Click the New Rule option. Here, you can see the New Formatting Rule dialog box; click the Format-only cells that contain the option under the Select a Rule Type section.
3. Under the Format-only cells with section, choose Specific Text from the first drop-down list and containing from the second drop-down list. Then, click the doc button 1 button to choose the value you want to format with a certain color, see screenshot.
4. Click the Format button, then click the Fill tab.
5. Choose a color you like, and then click OK to close the dialog.
6. Repeat steps 1 to 4 for each other drop-down selection.
7. Now, click Conditional Formatting, then click Manage Rules. Select the cell range to Apply the Conditional Formatting.
8. Once you’ve set the colors for the values, you can choose any value from the drop-down menu and the cell will automatically change to the color you chose.

What is the shortcut key for the drop-down list?

By using the keyboard shortcut “Alt + D + L“, you can create drop-down lists quickly and easily.

How do I create a yes/no drop-down in Excel?

Creating a yes/no drop-down in Excel uses the same process we followed for the example above. All you need to do is follow these steps:

1. Select the cells you want to include in the drop-down lists.
2. Click on Data Validation.
3. Insert Yes, No in the Source field on the pop-up, only separated by a comma.
4. Click OK to save your yes/no drop-down list.

How to create a drop-down list in Excel Google Sheet

Create a dropdown list on your computer

1. In Google Sheets, open a spreadsheet.
2. Select the cell or cells where you want to create a dropdown list.
3. Select an option:
   • Enter “@.” In the Menu, under the components section, click “Dropdowns.”
   • At the top, click Insert   Dropdown.
   • Click Data  Data validation  Add rule .
   • Right-click on a cell  Dropdown.
4. On the Data validation rules panel, under “Criteria,” select an option:
   • Dropdown from a range: Choose the cells to include in the list.
   • Dropdown: Enter the dropdown value.
     • Click Add another item to add additional dropdown values.
5. OPTIONAL: If you enter data in a cell that doesn’t match an item on the list, it is rejected. If you want people to be able to enter items not from the list:
   • Click Advanced options, and under “If the data is invalid”, select “Show a warning.”
6. Click Done.

Change or delete a dropdown list on your computer

1. In Google Sheets, open a spreadsheet.
2. Select the cell or cells you want to change, then select an option:
   • Click Data  Data validation.
   • Click the dropdown  Edit button .
3. Edit the dropdown list:
   • To change the options listed, edit the items under “Criteria.”
   • To delete a list, select an option:
     • Click Remove Rule.
     • If cells are empty, select the cells. Then, press the Backspace key.
     • If cells are empty, select the cells. Then, click Edit  Delete  Values.
   • To change the display style: Click Advanced options. Then, under “Display Style,” select either:
     • Chip
     • Arrow
     • Plain text
4. Click Done. If you change the content of the range you select, the changes are made in the list automatically.

Note: If you delete a value with an assigned color from the criteria source range when the dropdown is populated from a range, the value and color will still appear under the criteria. However, it will be uneditable.

To remove the value from the list, change the source range or any other item’s color.

Also, to create and edit drop-down lists on an iPhone or iPad, you will need to use a computer. You can then select an option from the list on your iPhone or iPad.

Create a drop-down list on Android

1. On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
2. Tap the cell or cells where you want to create a drop-down list.
3. In the top right, tap More .
4. Tap Data Validation.
5. Under “Criteria,” choose an option:
   • List of items: To add an item, tap +Add and enter an item. To save an item, tap Done .
   • List from a range: Enter the cells that will be included in the list.
6. The cells will have a Down arrow . To remove the arrow, next to “Show dropdown list in cell,” turn off the switch .
7. If you enter data in a cell that doesn’t match an item on the list, you’ll see a warning. If you want people to only enter items from the list, choose “Reject input” under “On invalid data.”
8. In the top right, tap Save. The cells will show a drop-down list.

Change or delete a drop-down list

1. On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
2. Select the cell or cells you’d like to change.
3. In the top right, tap More .
4. Select Data Validation.
   • To change the items listed, go to “Criteria” and edit the items. In the top right, tap Save.
   • To delete a list, go to the bottom right and click on Remove rule.

Note: If you change the contents of the range you’ve selected, the changes will be made in the list automatically.

Is a drop-down list the same as data filtering?

A drop-down list is different from data filtering, although they are often mistaken. Data filtering is added to the headers of each column, so you can filter the visible data by category — for example, filter the data in a column by month, name, place, and more.

A drop-down list lets users select an item to fill the cell with.

RELATED ARTICLES:

• How to Change Email Signature in Gmail: Detailed Guide
• How to Edit a Post on Facebook: Step-by-Step Guide
• How to Sign a PDF on iPhone 2023: Quick Guide