How to Create a Drop Down List in Excel: A Comprehensive Guide

If you are tired of constantly typing data into your Excel, you should now learn how to create a drop down list in Excel. In case you are looking for a way to make your data entry faster, now we will look at the drop-down list in Excel!

Introduction: how to create a drop down list in excel

Excel can work for various tasks from simple data entry to complex calculations. One of many features of Excel is the drop-down list. In this way, we can choose from a list of pre-defined options instead of typing in data as selections. With this, we can save you time and reduce errors.

create drop down list in excel: how to add drop down list in excel

Considering how it will help us for a big time, creating a drop down list in Excel is easy and you can do it in just a few steps.

1. You select the cell or cells to make your drop-down list.
2. Then we click on the Data tab in the Excel ribbon.
3. Now, you click on the Data Validation button in the Data Tools group.
4. In the Data Validation dialog box, you select List from the Allow drop-down menu.
5. In Source field,  we enter the options you want to appear in the drop-down list. It is separated by commas.
6. Now we click OK to save your settings.

So; when you click on the cell with the drop-down list, you will see a small arrow appear to the right of the cell. Because clicking on the arrow will display the options you defined in Source field.

Using a Drop down List in Excel: how to add drop down in excel

Once we have our drop-down list in Excel, using it is simple. You will just click on the cell with the drop down list and select the option you want from the list. The selected option will pop up in the cell.

Here are some tips for using drop-down lists in Excel and you can get better at it.

1. You can use the same drop-down list in multiple cells by selecting all the cells. And they we can be entering the cell address (e.g. A1) in the Source field when you are creating the list.
2. You may want to allow users to enter their own data in addition to the options in the drop-down list. Here we select List from Allow drop-down menu and Ignore blank from Error Alert tab in Data Validation box.
3. You can use conditional formatting to highlight cells if they match with an item in drop-down list.

Customizing a Drop-down List in Excel:What are drop-down menus in Excel?

Good news is, we can customize your drop-down list in a variety of ways.

1. You can add or remove options from the list by editing Source in Data Validation dialog box.
2. We can change the drop-down list’s width by dragging the cell’s border.
3. It is also possible to change the options’ font, color or style in the drop-down list. Here you will be selecting the cells with the list and formatting them as you prefer.
4. You can add a message or warning to the drop-down list by entering text in Input Message or Error Alert tabs again in Data Validation.

Additional Tips and Tricks: data validation excel

Before we wrap up, you can read below additional tips and tricks for a way smoother usage.

1.  If you are using a large data set for your drop-down list, we can consider creating a named range to make it easier to reference.
2.  You can use data validation to limit the user’s input to only what’s available in the drop-down list. Hence, here we are ensuring all data is accurate and consistent.
3. If the user tries to enter data not available in the drop-down list, you can use data validation to display an error message.
4. You can use keyboard shortcuts to quickly select options in the drop-down list. We can simply press Alt+Down Arrow to open the list. And then we can use the arrow keys to navigate and Enter to select.
5. Also, you can use conditional formatting to highlight cells for standing out some data.

FAQson How to make drop down list in excel

1. How to create a drop-down list referencing data from another sheet in the same workbook? You can do it by using the INDIRECT function in Source field. So if your data is in a sheet named Data and the list is in a sheet named Main. Here your formua to use =INDIRECT(‘Data’!A1:A10) in Source field.