How to Add a Drop-Down List in Excel on SharePoint

To add a drop-down list to an Excel workbook stored on SharePoint, open the workbook in Excel, select the cells that should have the list, then choose Data > Data Validation, set Allow to List, and provide the list items or their cell range as the source. Save the workbook back to SharePoint so collaborators can use the list. SharePoint stores and shares the file; the drop-down itself is an Excel data-validation setting.

What You’ll Need

An Excel workbook saved in a SharePoint document library, preferably in .xlsx format. A CSV file cannot retain Excel data-validation settings.
Edit permission for the workbook. If you only have view permission, you can inspect the list but cannot reliably save changes to it.
The choices you want people to select. For a short, stable list, you can enter the choices directly. For a longer list or choices that may change, put them in worksheet cells.
Excel for Microsoft 365 on Windows for the full set of instructions below. Excel for the web can create and edit basic drop-downs too; a few management options may differ.

Step-by-Step

Open the workbook from SharePoint. In SharePoint, go to the document library and select the Excel file. Choose Open in Desktop App if you want to follow the Windows desktop steps. If you choose Open in Excel for the web, the workbook opens in your browser and saves changes to SharePoint as you work. If the file opens read-only, check your permissions or ask the owner to grant edit access before continuing.
Decide where the choices will live. For a small list that rarely changes, you can enter the choices directly into the Data Validation dialog. For a list that may be updated, place each choice in its own cell in a worksheet. For example, create a sheet called Lists, type Status in A1, and enter Open, In progress, and Complete in A2:A4. Keep the heading out of the list range so it does not appear as a selectable choice. A cell range is easier to maintain and avoids problems when a choice contains punctuation.
Select the cells that need the drop-down. Return to the sheet where people will enter data, then select the target cell or range. For example, select B2:B100 if each row needs a status choice. You can apply one validation rule to a whole range at once. Avoid selecting the entire worksheet unless every cell really should have the same list, since that can make ordinary data entry confusing.
Open Data Validation. In the Windows desktop app, go to Data > Data Validation. In the dialog, open the Settings tab if it is not already selected. Set Allow to List. Leave In-cell dropdown checked so users can see and open the list from a cell. If you do not see the command, make sure you are editing the workbook in Excel rather than viewing a preview in SharePoint.
Set the list source. For a short list, enter the choices in the Source box, separated by the list separator Excel expects in your regional settings; commas are common. For the example list stored in A2:A4 on the same sheet as the entry cells, select that range as the source. If the choices are on another worksheet, Excel’s validation dialog does not accept a direct reference to a different sheet in every setup. Use a workbook-level defined name instead: select the choice cells, go to Formulas > Define Name, give the range a clear name such as StatusOptions, and confirm the range excludes the header. Then enter StatusOptions as the validation source. In the dialog, the source for a defined name is entered as =StatusOptions.
Choose how Excel should respond to input. On the Data Validation dialog’s Error Alert tab, leave Show error alert after invalid data is entered selected if entries should be limited to the list. Choose Stop for the strongest warning, then add a short title and message such as Select a status from the list. On the Input Message tab, you can show a brief instruction when a user selects a cell. These messages are optional, but they help explain what the field is for.
Apply and check the result. Select OK in the dialog. Click one of the validated cells and open its arrow to confirm the choices appear in the intended order. Select an item and check that it is entered in the cell. If you applied the rule to many rows, check a cell near the bottom of the range as well; this confirms the rule was applied throughout the selection.
Save and confirm the SharePoint copy. In Excel for Windows, save the workbook with Ctrl+S and wait for the save or upload status to finish. If the workbook is open in a browser, it normally saves automatically; wait for the saved status before closing the tab. Reopen the file from the SharePoint library if you need to confirm that the updated list is present in the shared copy, rather than only in a local or downloaded copy.

Excel for the web: Open the workbook from the SharePoint library, select the destination cells, and look for Data > Data Validation in the ribbon. Choose List, set the source, and apply the rule. The web app supports basic list validation, but if you cannot create a defined name, edit a complicated source, or find a setting shown in the desktop dialog, open the workbook in the desktop app, make the change there, and save it. The workbook remains in SharePoint; opening it in the desktop app does not require making a separate copy.

Common Mistakes

Including the heading in the source. If the range starts at A1 in the example, Status may appear in the choices. Select only the actual options, such as A2:A4.
Using the wrong range or worksheet. The source must point to cells containing the intended choices. A misspelled defined name, a range that includes blank cells, or a name scoped only to a different sheet can make the list empty or incomplete. Use a clear workbook-level name when the choices are on a separate sheet.
Assuming validation prevents every invalid value. Data Validation can warn or block someone typing an unlisted value, depending on the Error Alert setting. However, copying and pasting data into a validated cell can bypass the warning or replace the validation rule. Treat the list as an entry aid, not a security boundary.
Typing a long or punctuation-heavy list into Source. Directly entered choices have a limited source length and can be awkward when a choice contains the separator character. A range of cells is more dependable for longer lists or values with commas.
Expecting new source entries to appear automatically. If the validation source is a fixed range such as A2:A4, adding an item in A5 does not necessarily expand the range. Update the source range or use a managed named range that includes the additional choice.
Saving the wrong file format or copy. CSV does not preserve validation rules. Also check that you edited the workbook in the SharePoint library and not a downloaded copy with the same filename.

Tips

Give the source sheet a descriptive name such as Lists or Lookup, and place related choices in clearly labeled columns. This makes future updates easier for workbook owners.
Use a defined name for a list stored on another worksheet. Choose a short, meaningful name with no spaces, and use the same spelling in the validation source. A workbook-level name is generally the easiest option when several sheets need the same drop-down.
Keep the choice wording consistent. If one option is In progress, avoid also adding In Progress unless you intentionally want two separate choices. Consistent labels make filtering and summaries more reliable.
Use an Input Message when a label alone does not explain the expected value. For example, a cell headed Request status could display Choose the current stage when selected.
When updating choices, test a few existing entries too. Removing an item from the source does not automatically replace old cells that already contain that value.
Coordinate editing with collaborators. Excel coauthoring can let people edit a shared workbook at the same time, but avoid changing the validation source or structure while another person is actively editing the same area.

Troubleshooting

The Data Validation command is missing or disabled: Confirm that the workbook is open for editing and that you have selected worksheet cells, not a chart or another object. A protected worksheet may prevent changes; ask the workbook owner to unprotect it or provide an editable area. If the command is unavailable in Excel for the web, use Open in Desktop App and try Data > Data Validation there.

The arrow does not appear: Select a cell that should have the rule. The in-cell arrow is usually visible when the validated cell is selected, not when the cell is inactive. Reopen Data > Data Validation and check that Allow is List and In-cell dropdown is checked. If the rule is missing, reapply it to the intended range.

The list is blank or missing choices: Reopen the validation settings and inspect the Source field. Check that a typed source uses the correct list separator for your Excel settings. For a cell range, confirm that the cells are not empty and that the selected range includes every intended choice. For a defined name, check Formulas > Name Manager in desktop Excel and verify the name, its scope, and the cells it refers to.

A collaborator can type a different value: Check the Error Alert tab and make sure Show error alert after invalid data is entered is selected. Choose Stop if you want Excel to reject typed entries that are not on the list. Even then, pasted content may bypass validation, so review incoming data if list-only values matter to a process.

The change is not visible to others: Wait for Excel’s save status to complete, then confirm you are working in the SharePoint library copy and not a local download. Check that another person has not left the workbook checked out or that an upload conflict has not created a second copy. If changes still do not appear, close and reopen the library file after confirming it has saved.

Frequently Asked Questions

Can I add a drop-down list while the workbook is in SharePoint?
Yes. Open the workbook in Excel for the web or choose Open in Desktop App from the SharePoint library. Excel stores the data-validation rule in the workbook, and the saved rule is available to collaborators who can edit or use the file.

Do I need to download the workbook first?
No. You can edit the SharePoint workbook in the browser or open it in the desktop app while it remains stored in the library. Downloading is not required and can lead to confusion if you later edit a separate copy.

Can the list choices come from another worksheet?
Yes. Put the choices on that worksheet, define a name for the option cells, then use that name as the validation source. This avoids relying on a direct cross-sheet reference in the Source box and makes the list easier to reuse.

Will the drop-down expand when I add another choice?
Not if the source is a fixed range that stops before the new cell. Extend the source range or update the defined name so it includes the new choice, then check the drop-down again.

Can several cells share the same drop-down?
Yes. Select all the destination cells before opening Data Validation and apply the rule once. If the cells are in different areas, apply the rule to each area and verify each one, especially if the selections are on different worksheets.

Can I make the list mandatory?
Data Validation can reject many invalid typed entries when its Stop error alert is enabled, but it does not guarantee that every cell is filled or that pasted data follows the rule. Use a separate review process, such as checking for blanks before submission, if completeness is required.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.