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

How to automatically add new items to a drop down list

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

How to filter chart data

What if you want to show a selection of a data set on a chart and easily change that selection? […]

Compare data in an Excel chart using drop down lists

I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]

How to build an interactive map in Excel

This article describes how to create a map in Excel, the map is an x y scatter chart with an […]

Highlight group of values in an x y scatter chart programmatically

I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]

Run a Macro from a Drop Down list [VBA]

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

Advanced Techniques for Conditional Formatting

Table of contents How to change cell formatting using a Drop Down list Highlight cells based on coordinates Highlight every […]

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 […]

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 […]

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 […]

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 dependent drop down lists containing unique distinct values

Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

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 […]