How to insert a drop-down list in Excel: Data Validation and dependent lists

Summary. To insert a drop-down list in Excel, select the cell, choose Data > Data Validation, set Allow to List, select a source range or type the items separated by commas with no spaces, keep In-cell dropdown ticked and select OK. Excel for the web uses Data > Data Tools > Data Validation; some Mac versions label the command Validate. Keep the items in an Excel table (Ctrl+T) and every drop-down based on it updates when the list changes. For a dependent list, name each sub-list with Formulas > Create from Selection and use =INDIRECT($A2) as the second list's source. To remove a list, select Clear All in the same dialog; Ctrl+G, then Special, finds every validated cell. On Windows, Alt+Down Arrow opens the list. Two limits: Excel for the web can change a named-range list only in the desktop app, and validation does not stop pasted or filled data.

About this article. For anyone who builds forms, trackers or registers in Excel. On 15 November 2026 we checked every step, label and shortcut against Microsoft Support pages for Excel for Microsoft 365 (Windows and Mac), Excel 2024 and Excel for the web, and version facts against Microsoft Learn. Current releases on that date were Microsoft 365 Current Channel Version 2610 (Build 20527.20196) and Excel for Mac 16.115, both released on 10 November 2026. Labels can differ slightly by version and channel; no vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.

Drop-down list options at a glance

The right source depends on how often the list changes, based on Microsoft's create and edit pages.

How to insert a drop-down list in Excel for Windows

These steps apply to Excel for Microsoft 365 and Excel 2024. Microsoft's pages still list Excel 2021, 2019 and 2016, where the steps are the same, but Excel 2016 and 2019 reached end of support on 14 October 2025 and Excel 2021 on 13 October 2026.

1. On a separate sheet, type the items one per cell in a single column. Microsoft recommends an Excel table: select any item and press Ctrl+T. See naming a table in Excel to give it a readable name.

1. Select the cells where the drop-down should appear.

1. Select Data > Data Validation (in the Data Tools group).

1. On the Settings tab, in the Allow box, select List.

1. Click in the Source box and select the items, leaving out the header. You can type the items instead, separated by commas with no spaces, such as Yes,No,Maybe.

1. Tick Ignore blank if the cell may stay empty, tick In-cell dropdown (without it there is no arrow), then select OK.

Worth it for shared files: on the Input Message tab, tick Show input message when cell is selected and add a title and message of up to 225 characters. On the Error Alert tab, tick Show error alert after invalid data is entered and choose a Style. Stop blocks anything not in the list; Warning and Information show a message but let other values through. With no title or message, Excel shows a default alert saying the value is not valid.

To open the list without a mouse, select the cell and press Alt+Down Arrow.

Excel for Mac and Excel for the web

Excel for Mac (Microsoft 365, 2024 and 2021):

1. Type the valid entries on a sheet in one column or row, without blank cells.

1. Select the cells you want to restrict.

1. On the Data tab, select Data Validation (some versions show Validate).

1. On the Settings tab, open the Allow pop-up menu and choose List.

1. Click in Source, select the entries on the sheet, press Return (or select Expand) to restore the dialog, then select OK.

You can also type comma-separated values straight into the Source box.

Excel for the web:

1. Select the cell, then choose Data > Data Tools > Data Validation.

1. On the Settings tab, set Allow to List.

1. In Source, select the cells holding the items (without the header) or type them with commas, for example Fruit,Vegetables,Grains,Dairy,Snacks.

1. Tick Ignore blank if needed and keep In-cell dropdown ticked.

1. Optionally tick Show message on the Input Message tab, and tick Show Alert and pick a Type on the Error Alert tab, then select OK.

How to create a dependent drop-down list

A dependent (cascading) list changes with the first choice: pick a department in column A and column B offers only that department's roles. It combines named ranges with INDIRECT, which turns a text string, including a defined name, into a reference.

Worked example. On a sheet called Lists, enter this table in A1:C4:

1. Name each sub-list. Select Lists!A1:C4, choose Formulas > Create from Selection, tick Top row only and select OK. Excel creates three names that cover only the cells below each label: Sales (A2:A4), Finance (B2:B4) and Engineering (C2:C4). Microsoft documents this command for Windows and Mac; its Excel for the web steps name a range by typing in the Name Box instead.

1. Build the first list. On your data sheet, select A2:A50, open Data Validation, choose List and enter =Lists!$A$1:$C$1 as the source, so column A offers the three departments.

1. Build the dependent list. Select B2:B50 with B2 as the active cell, open Data Validation, choose List and enter =INDIRECT($A2) as the source. The $ fixes column A while the row changes per cell. While A2 is empty, INDIRECT has no valid reference and returns #REF!, so Excel may warn that the source is an error; accept the warning, and the list works once A2 has a value.

1. Test it. Pick Finance in A2; the arrow in B2 now lists Accountant, Controller and Payroll Specialist.

Rules that trip people up:

• No spaces in names. A name must start with a letter, an underscore or a backslash, cannot contain spaces and cannot look like a cell reference such as Z$100 or R1C1. If a department label has a space, name its list with an underscore (Customer_Success) and convert the label in the formula: =INDIRECT(SUBSTITUTE($A2," ","_")).

• The second cell does not clear itself. Validation shows messages and blocks entries only when someone types into the cell, so changing the department leaves the old role in place. To spot stale entries, use Data > Data Validation > Circle Invalid Data.

• Keep everything in one workbook. INDIRECT returns #REF! when it points at a workbook that is closed, and Microsoft notes that external references are not supported in Excel for the web.

• Growing lists. Create from Selection names fixed ranges. When you add roles, update the range in Formulas > Name Manager > Refers to.

How to edit a drop-down list

Microsoft's method depends on where the items live:

• Excel table source: type a new item at the end of the table column, or delete one. Every linked drop-down updates.

• Named range source: add or delete items, then open Formulas > Name Manager, select the name, reselect the cells in Refers to, and select Close and Yes.

• Cell range source: add or delete items, then select a cell with the drop-down, open Data > Data Validation and reselect the range in Source. Tick Apply these changes to all other cells with the same settings to update every copy.

• Typed (comma) list: open Data > Data Validation and edit the source text, keeping commas and no spaces.

Excel for the web edits a list directly only when its items were typed into Source. For a cell range, select Cancel, change the cells, then reselect the range if its size changed. A named range such as Departments needs a desktop version of Excel, Microsoft says.

How to remove a drop-down list

1. Select the cell with the drop-down. On Windows, hold Ctrl and click to select several cells.

1. Select Data > Data Validation.

1. On the Settings tab, select Clear All, then OK.

To find every drop-down on a sheet, press Ctrl+G, select Special, choose Data Validation and then All (or Same for cells that match the current one), and repeat the steps. Home > Find & Select > Data Validation selects the same cells. In Excel for the web, select the cells and use the same Data Validation and Clear All route.

Common drop-down problems and fixes

The causes and fixes below come from Microsoft's data validation pages.

For IT teams and businesses

Drop-down lists are among the simplest data-quality controls in Excel. Use them for fields that feed a report or system: cost centres, status codes, country, tax category. Keep the master lists on one sheet, convert them to tables, and hide and protect that sheet, as Microsoft suggests, so people cannot edit the options. If you protect the data-entry sheet too, do it after setting up validation and unlock the validated cells first. Our guide on locking cells in Excel covers those steps.

Treat Data Validation as guidance, not enforcement: Excel does not flag existing entries that break a new rule, and copied or filled data skips the check. Before an import, run Circle Invalid Data or a check for duplicates. Microsoft's own examples include a Custom rule, =COUNTIF($A$2:$A$10,A2)=1, that refuses a duplicate as it is typed; enter it for A2 first, then copy it down. Pair a drop-down column with automatic row numbers for a simple, consistent register.

How eCorpIT can help

eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics team helps organisations move from error-prone spreadsheet entry to validated forms, clean data models and dependable reporting. Talk to us at /contact-us/.

Last updated: 15 November 2026.

References

1. Create a drop-down list — Microsoft Support

1. Add or remove items from a drop-down list — Microsoft Support

1. Define and use names in formulas — Microsoft Support

1. INDIRECT function — Microsoft Support

1. Remove a drop-down list — Microsoft Support

1. Keyboard shortcuts in Excel — Microsoft Support

1. More on data validation — Microsoft Support

1. Update history for Microsoft 365 Apps (listed by date) — Microsoft Learn

1. Update history for Office for Mac — Microsoft Learn

1. Ending Support in 2025 — Microsoft Lifecycle, Microsoft Learn

1. Ending Support in 2026 — Microsoft Lifecycle, Microsoft Learn

1. Apply data validation to cells — Microsoft Support

1. Names in formulas — Microsoft Support

1. Use the Name Manager in Excel — Microsoft Support