Additional Info

Share

Additional Info

Drop-down lists

The articles listed in this category is about drop-down lists [Data Validation]. If you are looking for combo boxes then check out this category.

A drop-down list in Excel lets you specify valid values a user can enter in a cell, it makes it easier for the user to type a value since the list shows the valid values and clicking on them automatically enters the value in the cell.

Don't forget to check out the articles in this category, there are links in the sidebar or if you are on mobile then click on the hamburger icon. There are also links at the very bottom of this page.

Table of Contents

How to create a drop-down list

  1. Select the cell where you want to create the drop-down list.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data Validation" button.
  4. Select "List" in the drop-down menu.
  5. Type the values you want to show up in the drop-down list, separate value with a comma. You can also use a cell reference.
  6. Click OK button.
Back to top

Is there a short-cut to create a drop-down list?

Yes, there is. Make sure you have selected the cell you want to create the drop-down list in.

Press Alt then A then V and lastly V.
Back to top

How to edit a drop-down list

The following steps shows you how to edit the values in a drop-down list.

  1. Select the cell containing the drop-down list you want to edit.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data Validation" button.
  4. The source field shows you the values the drop-down lists shows when you click on it. Change these values to edit the drop-down list.
  5. Click OK button to apply changes.
Back to top

How to add values to a drop-down list

The following steps shows you how to add values to an existing drop-down list in Excel.

  1. Select the cell containing the drop-down list you want to edit.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data Validation" button.
  4. The source field contains the values shown to the user. Simply type mode values and use a comma as a delimiting character.
  5. Click OK button to apply changes.

Read the following article if you want to automatically add new values to a drop-down list.

Back to top

How to link to a drop-down list

Simply use a cell reference to the cell containing the drop-down list, that way you can use the selected value in a formula or macro. The image above shows a drop-down list in cell B2.

If this is not working then you probably have a combo box on your worksheet.

Tip! Use a named reference if you often use the value in formulas etc. Simply select the cell containing the drop-down list and then click in the name box and enter the name. Then use that name in your formula, it will even show up in the formula bar.
Back to top

How to copy drop-down list

Can I copy the drop-down list to an entire column?
You can, however, I don't recommend it. There are more than a million rows in a worksheet, it may slow down your workbook and create a larger workbook file than necessary.

Copy - Paste

The image above shows you how to copy and paste a drop-down list.

  1. Select the cell containing the drop-down list you want to copy.
  2. Press CTRL + c which is the short cut keys to copy a cell.
  3. Select the cell range you want to copy the drop-down list to.
  4. Right-click on the selected cell range and click on Paste or press CTRL + v to paste the copied cell to the selected cell range.

Select - Drag

  1. Click and hold on the black dot located at the bottom right corner of the cell containing the drop-down list you want to copy.
  2. Drag downwards with mouse as far as needed.
  3. Release mouse button.
Back to top

How do I reference an Excel defined Table in a drop-down list?

  1. Click the cell containing the drop-down list.
  2. Go to tab "Data" on the ribbon.
  3. Click "Data Validation" button. A dialog box appears.
  4. Select "List" in the drop-down menu.
  5. Use the INDIRECT function in order to use a reference to an Excel defined Table, see image above.

This article explains in greater detail how to use references to Excel defined Tables: How to use a Table name in Data Validation Lists and Conditional Formatting formulas


Back to top

How to create a date drop-down list

The following article explains how to build a drop-down list containing dates:
Create a drop down calendar

Back to top

How to create a big drop-down list

Simply change the width and height of the cells containing the drop-down lists. If this doesn't work for you then you need a combo box.

Back to top

How to change the font and font size of a drop-down list

  1. Select the cell containing the drop-down list.
  2. Click the font drop-down selector to change the font.
  3. Click the font size drop-down selector to change the size.

As you have probably already noticed, it won't change the font or size of the list, you need a combo box to accomplish that.

Back to top

How to copy the contents of a drop-down list

Copy the selected value

  1. To copy the selected value simply select the cell.
  2. Press short cut keys CTRL + c to copy the cell.
  3. Right-click the destination cell and then click on paste value. This will only paste the selected value of the drop-down list and not the drop-down list itself.

Copy all values in the drop-down list

  1. Select the cell containing the drop-down list you want to edit.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data Validation" button.
  4. The source field contains the values shown to the user. Select and copy these values.

Back to top

Interactive drop down list

The following articles demonstrates interactive drop-down lists:

Back to top

Drop-down list items sorted from A to Z

This article explains how to set up a drop-down list containing values sorted from A to Z:

Create a drop down list containing alphabetically sorted values

How to auto populate other cells when selecting a value in a drop-down list

The following article demonstrates how to auto populate cells after a value is selected in a drop-down list:

Use a drop down list to search and return multiple values

Back to top

Can I use a user defined function in a drop-down list?

Yes, you can. You need to use a named range as well. This article explains in detail how: Use filtered table values in a drop-down list

Back to top

Can I create a drop-down list programmatically using VBA?

Yes, this article demonstrates how to insert a drop-down list using VBA: Add values to a data validation list [VBA]

Back to top

Latest articles

Add new items to a drop down list automatically

A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]

Quickly change chart data source

In excel it is not easy to change a chart´s data source without manually changing the data source in chart […]

Use drop down lists to compare data series in an excel chart

The two drop down lists below the chart let´s you compare two data series from separate tables. It is easier […]

Use a map in an Excel chart

This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]

Dynamic data labels in a chart

My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]

How to change cell formatting using a Drop Down list

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Populate drop down list with filtered Excel Table values

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

Use a drop down list to filter and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

Add or remove a value in a drop down list programmatically

This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Use a drop down list to display data in worksheet header [VBA]

Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

Basic invoice template

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

Dependent drop down lists – Enable/Disable selection filter

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

Create a drop down calendar

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

Invoice template with dependent drop down lists

This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]

Create dependent drop down lists containing unique distinct values in multiple rows

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Create a drop down list containing alphabetically sorted values

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]