How to create a dropdown menu with Excel data in a SharePoint list

Question

Hi all,

I have an Excel sheet with names in column A. Now I want to create a column "Name" in a SharePoint list, which only allows names from this Excel sheet - best by providing the user a dropdown menu with available names.

How can I do that?

Thank you in advance and kind regards,

Leon

Answer 1

Hi Leon,

We can import the Excel spreadsheet to SharePoint as a list and then create a lookup column which get data from that list. Below are the detailed steps:

1. In IE, go to your site > Settings icon > Add an app > in the second page, click Import Spreadsheet.
2. Give a name to the list, click the Browse button > select the Excel file which contains the name column > click Import.
3. In the pop-up page, select Range of cells in Range Type drop down list, click the - button under Select Range, select the cells contain names and click the following button then click Import.

4. Go to Site settings > Site columns > Create > type a column name > select Lookup (information already on this site).
5. Scroll down to Get information from, select the list we just created using Import Spreadsheet > OK.
6. Go to the list where you want to add this column, click LIST > List Settings > Add from existing site columns > select Custom Columns in Select site columns from:, select the column we created in step 4 > OK.

7. Now when we create an item in this list, we can select the names from the column we added.

Regards,

James

Answer 2

Hi Leon,

Does the information above answer your question?

Regards,

James

Answer 3

Hi James,

thank you very much for your help, it is working fine like this. However, when I modify the initial Excel Sheet, the changes are not taken into account in SharePoint. Within the scope of the method above, do I only perform a "fixed" upload of data as opposed to a synchronization? As the Excel sheet is updated on a very regular basis, a synchronization would be helpful.

Best,

Leon

Answer 4

Hi Leon,

We can't sync a local Excel file with a SharePoint Online list directly. However, I would share with you a workaround:

1. After finishing the steps above, open the list we created in step 2 in my last reply using IE browser.
2. On the LIST tab > click Open with Access > OK, under Tables, double click the listname and make changes to the data. These changes will be synced to the SharePoint Online list.

3. Next time we want to update the source data, we can open the Access Database file (.accdb) in C:\Users\username\Documents and make changes to the data.

We can ignore the prompt about "Save to SharePoint Site" in Access, it's optional.

Regards,

James