How to add drop-down lists in Excel

This video provides you with some basic tips for adding a drop-down list to your Excel table.

The few steps described in this video can greatly simplify the process of organizing and filling information in your Excel spreadsheets. Whether you need predefined values in a column or a standalone combo-box, Excel has all the tools at your disposal. If you've already had the chance to appreciate the value of data validation, you may find a couple of recommendations for having your drop-down lists updated automatically.

Creating drop-down lists: video transcript

Adding a drop-down list to your table is a preliminary that can save a lot of your time when working in Excel. If you want to make sure you use a limited set of values in some column, Excel data validation is exactly what you need.

One prerequisite is to have a list of values you want to appear in the drop-down list. To keep your table clear, you can prepare it on a separate sheet. Keep in mind that the values will appear in the same order in the drop-down list, so you can sort them right here.

If your list may change or grow, there is one trick that will help you: select it and either press Ctrl + T on your keyboard, or go to the Insert tab in Excel and select Table.

Once you have the list ready, you need to select it ad define its name to be able to refer to it for your drop-down list. If you have a table, point to the top header value until you see a black arrow and click once to select the entire list. Type a name for it right here in the name box, just make sure there are no spaces, and press Enter.

Next select the cells where you want to show the drop-down list and go to Data Validation on the Data tab in Excel. The Settings tab gives us the main options: first we need to select List in the Allow box. Leave the "In-cell dropdown" option selected to keep it within the cell boundaries, you can leave the "Ignore blank" option selected if you can have the cell empty.

Then go to the Source field to refer to the list of values you created. You can use the "select range" icon to identify where the cells with your values are, type the equal sign followed by your list name, or simply press F3 to select the created named list, and click Ok to confirm.

There are additional options you can use if you assume other users will work with the table: the Input Message allows you to enter text of the hint that will be displayed when you select such cell. You can also show an error if someone tries to enter a value that's not in the list. You can enter text you want them to see on the Error Alert tab, or you can keep the "Show error" box selected to have the default message.

Once you do this, you can simply click on a cell to select from your list of pre-defined values. When you add or change values in your table, you'll automatically see these changes in the drop-down list. If you need the same list to be shown in more cells, you can drag it down from the bottom-right corner.

Excel allows you to create an independent combo-box as well. When you have a combo-box, you get auto fill options whenever you just start typing a word, and you can also have all the text formatting options.

To add a combo box, go to the Developer tab in Excel. If you don't have it, you can enable it if you go to File->Options->Customize Ribbon, find and select the Developer tab. Once you are there, click Insert, and find Combo Box in the Active X controls. It lets you draw the combo box as you'd like to see it, and then you can fill it with values by right-clicking on it and selecting Properties. You can enter your list name in the ListFillRange field. Then you can change any properties that are important for you like the font and number of rows you want to see. To start using the combo box, simply disable the design mode.

Please post any questions you have about drop-down lists, we'll do our best to assist you.

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.