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 сan 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.

See also

One Response to "How to add drop-down lists in Excel"

  1. Jenny says:

    Hello,

    I need a help to do a conditions using date in excel 2007:

    Query:

    I need to set a follow up with the date in excel. There are two status. Open and close.

    Conditions:

    1)If status "open" & date of today more than 3 days then "Remainder" and color changes (entire row should be highlighted).

    2) If status "Open" & date of today more than 5 days then "Exceeded" and another color changes (entire row should be highlighted).

    3) If status "closed" then "Completed".

    Can any one help me with this issue?

    Thank you in advance.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard