Dropdown lists are one of the easiest ways to make Excel spreadsheets more accurate, consistent, and user-friendly. Whether you’re creating order forms, data entry sheets, inventory trackers, or project templates, dropdown menus help prevent typing mistakes and standardize your data.

In this tutorial, you’ll learn two different ways to create dropdown lists in Microsoft Excel using Data Validation. You’ll also learn when each method works best, making it easier to choose the right solution for your spreadsheet.

---

What You’ll Learn

• How to create a dropdown list by typing items directly into Data Validation
• How to create a dropdown list using a named range
• When to use each method
• How named ranges make dropdown lists easier to maintain
• How dropdown lists improve data consistency
• Which method is best for short lists vs. long lists

---

Common Uses

• Order forms
• Inventory tracking
• Employee schedules
• Project trackers
• Budget templates
• Customer databases
• Survey forms
• Data entry worksheets
• Status or priority tracking

---

Requirements / Compatibility

This tutorial was originally recorded using Microsoft Excel 2020-era Microsoft 365.

Both methods shown remain fully supported in:

• Microsoft 365
• Excel 2024
• Excel 2021
• Excel 2019
• Excel 2016
• Excel for the web
• Excel for Mac

The Data Validation interface has remained largely unchanged, so the steps shown here continue to work in current versions of Excel.

---

Practice & Resources (List of Resources)

Practice both methods using the downloadable workbook included with this tutorial.

Resources include:

• Excel Practice Workbook featuring:
  • An instruction sheet
  • A worksheet for Method 1
  • A worksheet for Method 2
  • A separate data sheet for the named range example
• Dropdown List Quick Reference Guide (PDF) comparing both methods, when to use each one, and the steps for creating them.

These resources make it easy to experiment with both techniques and decide which approach works best for your own spreadsheets.

Click the button below to view the resources available for this tutorial.

---

Video Tutorial

---

Breaking Down the Concept

Excel’s Data Validation feature allows you to limit what users can enter into a cell. One of the most useful validation types is a dropdown list, which lets users select from predefined options instead of typing manually.

This tutorial covers two ways to build those lists.

Method 1: Type the list directly

Enter each option directly into the Source field, separated by commas.

Example: Small,Medium,Large,XL

This approach is quick and works well for short lists that rarely change.

Method 2: Use a Named Range

Store the list elsewhere in your workbook, assign it a Named Range, and reference that name in the Source field.

Example: =shirt_sizes

This method is much easier to maintain because you only update the source list instead of editing every dropdown individually.

---

Which Method Should You Use?

Quick Comparison — Choosing the Right Dropdown List Method

Aspect	Method 1 – Typed List	Method 2 – Named Range
Setup speed	Fastest — no separate list needed	A bit more setup
Best for	Short, stable lists (a handful of items)	Longer lists, or lists that change often
Editing later	Reopen Data Validation and retype	Update the source cells / redefine the name
Reuse across sheets	Not easily reusable	Easy to reuse the same named range elsewhere

---

Step-by-Step Instructions

Method 1: Create a Dropdown List by Typing Items

Best for short lists you won’t edit often.

1. Click the cell (or select the range of cells) where you want the drop-down to appear.
2. Go to the Data tab on the ribbon, then click Data Validation.
3. On the Settings tab, under Allow, choose List.
4. In the Source box, type your options separated by commas — for example: XS,S,M,L,XL,XXL
5. Click OK. The cell now shows a small drop-down arrow with your options.
6. To edit the list later, click the cell again, reopen Data Validation, and change the Source text.

---

Method 2: Create a Dropdown List Using a Named Range

Best for longer lists, or lists you want to protect from accidental edits.

1. Put your list of options on its own worksheet (this keeps it safe from being reordered or deleted by accident).
2. Select the cells containing the list.
3. Click in the Name Box (top-left corner, just left of the formula bar) and type a name for the list, e.g. ShirtSizes, then press Enter. This creates a named range.
4. Go to the sheet and cell where you want the drop-down. Click Data > Data Validation.
5. On the Settings tab, under Allow, choose List.
6. In the Source box, type an equal sign followed by the range name, e.g. =ShirtSizes
7. Click OK. The drop-down now pulls its options from the named range.
8. To add or remove items later: go to the Data sheet, select the new full range of items, click the Name Box, type the same name again (e.g. ShirtSizes), and press Enter. This redefines the named range and the drop-down updates automatically.

---

Additional Tips

• Keep dropdown choices short and easy to read.
• Named ranges make future updates much easier because you only maintain one master list.
• If your dropdown list will grow over time, consider creating it from a table or dynamic named range.
• Data Validation controls what users can enter but doesn’t automatically correct existing invalid data.
• If you copy and paste over a validated cell, the validation rules may be overwritten.

---

Related Features

People creating dropdowns often also use:

• Data Validation rules
• Named Ranges
• Tables
• XLOOKUP
• VLOOKUP
• IF statements
• Conditional Formatting

---

---

Stay Updated

There are two easy ways to get notified when new tutorials are published:

Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials

or enter your email below to receive new blog posts directly in your inbox: