Home / Tech
Tech

How to Create a Drop Down List in Excel: Step-by-Step

Illustration for How to Create a Drop Down List in Excel: Step-by-Step

A drop down list in Excel lets someone pick an entry from a list instead of typing it, which keeps your data tidy and cuts down on typing mistakes. It is one of the quickest ways to make a form, tracker or budget sheet easier to use. This guide shows you how to create an Excel drop down list with Data Validation, how to build it from typed items or a range of cells, and how to edit, copy and remove it.

Illustration showing the main idea of How to Create a Drop Down List in Excel: Step-by-Step

What is a drop down list in Excel?

A drop down list is a cell that shows a small arrow when you select it. Clicking the arrow reveals the allowed choices and you pick one. Microsoft describes drop-downs as a way to help people work more efficiently in worksheets by choosing an item from a list that you create. Excel builds them with a feature called Data Validation, which you will find on the Data tab.

Typical uses include status columns (Not started, In progress, Done), department names, yes or no answers and product categories. Because everyone chooses from the same list, you avoid variations such as “UK”, “U.K.” and “United Kingdom” appearing in the same column, which can break filters and formulas later.

How to create a drop down list in Excel: step by step

  1. Type your list of entries in a single column or row, with no blank cells in between. Many people keep these on a separate sheet.
  2. Select the cell or cells where you want the drop down to appear.
  3. Go to the Data tab and select Data Validation.
  4. On the Settings tab, under Allow, choose List.
  5. Click in the Source box and select your list of entries, or type them (see the next section).
  6. Make sure In-cell dropdown is ticked, then select OK.

Select one of the cells and the arrow should appear. These steps follow Microsoft’s support instructions for recent versions of Excel, including Microsoft 365. Button names can differ slightly between versions.

Three ways to fill the Source box

Microsoft’s guidance shows that you can type the items, point to a range of cells or use a named range. Which one suits you depends on how often the list will change.

Method What you enter in Source Best for
Type the items Yes,No,Maybe Short lists that rarely change
Cell range =$A$2:$A$4 Lists you want to keep on a sheet and edit easily
Named range =PriceRange Reusable lists, or lists kept on another sheet

If you type the items directly, separate each one with a comma. In some regional settings Excel expects a semicolon instead, so if your list appears as one long entry, try a different separator.

How to edit a drop down list

If the list comes from a range of cells, change the entries in those cells and the drop down updates. If you typed the items into the Source box, select the cell, open Data Validation again and edit the text there.

To add items to a range-based list, insert new rows inside the range rather than typing below its last row, because Excel does not automatically include entries added after the end of the range. A popular alternative is to turn the list into an Excel Table (select a cell in the list and press Ctrl+T) and base a named range on that table column, so new items are picked up as the table grows. Check that this behaves as you expect in your version of Excel.

If you want the choices in alphabetical order, sort the source data first. Microsoft suggests sorting the data in your list before you build the drop-down.

Stop people entering other values

Excel checks whatever someone types against your list. The Error Alert tab in the Data Validation window controls what happens when the entry is not on the list. Choose the Stop style to block anything that is not in the drop-down, and add a short message explaining the allowed options. The Warning and Information styles let the entry through after showing a message. If the option to show an error alert is unticked, Excel will accept any value.

How to copy or remove a drop down list

To reuse a drop down in other cells, copy the cell, select the destination cells, then use Paste Special and choose Validation. This pastes only the drop down rule and leaves the existing values alone. To remove a drop down, select the cells, open Data Validation, choose Clear All and select OK.

Checklist-style illustration for How to Create a Drop Down List in Excel: Step-by-Step

Common problems and fixes

  • Data Validation is greyed out: the sheet may be protected or the workbook may be shared. Unprotect the sheet or stop sharing first.
  • No arrow appears: check that In-cell dropdown is ticked and that you have selected the cell.
  • The list shows blank entries: your source range includes empty cells. Trim the range or use a named range that covers only the filled cells.
  • The items are on another sheet: recent versions of Excel let you refer to another sheet directly. If the reference is rejected, create a named range and use that in the Source box.
  • Excel for the web: data validation drop-downs are supported, although some options can look different from the desktop app.

Once your data entry is tidy, you may want to remove duplicates in Excel, keep your headings in view with our guide to freezing panes in Excel, save time with Excel keyboard shortcuts or find out what a macro in Excel is so you can automate repeat tasks.

Frequently asked questions

How do I make a drop down list in Excel from a range of cells?

Type the items in a column, select the cell where you want the drop down, go to Data, then Data Validation. Set Allow to List, select the range in the Source box, for example =$A$2:$A$6, and select OK.

Can I create a drop down list without typing the items in cells?

Yes. In the Source box, type the items separated by commas, for example Yes,No,Maybe. This suits short lists that rarely change.

How do I add more items to an existing drop down list?

Edit the source cells, inserting new rows inside the range, or edit the text in the Source box through Data Validation. If you use a Table with a named range, new items can be picked up as the table grows.

Why can’t I see the drop down arrow?

Check that you have selected the cell, that In-cell dropdown is ticked in the Data Validation window and that the validation rule is still applied to that cell.

How do I remove a drop down list in Excel?

Select the cells, open Data Validation, choose Clear All and select OK. The values already in the cells normally stay in place.

Does this work in Excel for Mac and Excel for the web?

Data Validation sits on the Data tab in Excel for Mac, and Excel for the web supports data validation drop-downs, although menus can look slightly different.