How to add to a drop down list in SharePoint Excel

Question

I'm working in Excel SharePoint. I already have a drop down list created. Its created as an Excel table. I thought, as it is an Excel table, adding additional lines directly to the end of the list it would automatically recognise this and add these to the drop down list. But it hasn't. I cant see any help online for this issue. I have then tried to add these manually through the Data Validation option but within SharePoint the Data Validation function behaves differently and doesn't seem to recognise the original table so therefore wont let me add.

If any anybody has any advice that would be great! Thanks.

Answer 1

Dear Matthew Crump1,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

From your description It seems you want to know how to add to a drop-down list in SharePoint Excel

As per your description, I'm sorry and I'm afraid to say that in SharePoint online does not have a direct built-in feature to display a SharePoint list as a dropdown in Excel directly. However, to achieve this using Microsoft Power App (customization solution) or easily way is, you may first connect to SharePoint lists to Excel, and then in Excel you may create a dropdown list.

To display a SharePoint table as a dropdown in Excel, you can follow these steps:

Step1: Export SharePoint Table into Excel

I SharePoint list Click on the "Export to Excel" option available in SharePoint. This will create an Excel file with the table's data.

Or connect SharePoint list into Excel using power Query (in Excel blank file click on data tab>Get data>Form other sources>Form SharePoint list)

For your reference: Import data from data sources (Power Query)

Step 2: Convert Excel Data to a Dropdown List

Open the Excel file (query file) and import as table, then select the column that you want to use as a dropdown list.

Click on the "Data" tab in the Excel ribbon>Select "Data Validation" from the "Data Tools" group.

In the "Data Validation" dialog box that appears, choose "List" from the "Allow" dropdown.

For your reference: Create a drop-down list

Now you have successfully displayed the SharePoint table as a dropdown in Excel.

Your understanding and patience will be highly appreciated.

If there is any misunderstanding, please feel free to correct me.

Have a nice day!

Best Regards,

Sophia

Answer 2

Dear Matthew Crump1,

Good day! Thank you for posting to the Microsoft Community. We are happy to help you.

Thank you for your feedback may I know if your issue has been resolved? Do you need further assistance?

Your understanding and patience will be highly appreciated.

If there is any misunderstanding, please feel free to correct me.

Have a nice day!

Best regards,