Create a drop down list in Excel on Sharepoint

Try to fix by the following:

1. Use a Named Range

• Go to the sheet with your list (in the desktop app).
• Select the 5 cells you want to use.
• Go to Formulas → Define Name → Give it a name like DropdownOptions.
• Then go to your dropdown cell → Data Validation → List → Type:

=DropdownOptions

• Save and upload the file back to SharePoint.

2. Move the List to the Same Sheet

• Copy the 5 options to a hidden column on the same sheet.
• Use that range directly in your data validation.

3. Use Excel Desktop to Set It Up

• Open the file in Excel Desktop.
• Set up the dropdown using the full feature set.
• Save and sync it back to SharePoint.
• Excel Online will usually respect the validation once it’s already in place.