How do i cerate a dropdown in excel using a SharePoint list?

Question

I have a table in SharePoint that contains multiple row. I want that table to be shown as a dropdown in excel.

Answer 1

Dear respected Shaza Muhammad,

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

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.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad