Export a SharePoint list to excel

In this SharePoint tutorial, We will discuss how to export SharePoint list to excel. Also, We will cover the below topics:

• How to open a SharePoint list in excel
• Access to the resource is forbidden (error) excel SharePoint
• How to open a SharePoint list in excel
• Get data from SharePoint Folder in excel
• How to export SharePoint list to CSV using Microsoft flow

How to export a SharePoint list to excel

Here, I am going to explain how to export a SharePoint list to excel.

• Open the SharePoint Online list which you want to export to excel.
• Click on Export to Excel in the command bar to download your list in your local system.

• Once you click on Export to excel it will download to your local system. The file will be something like query

• When you try to open the file, you might get a Microsoft Excel Security Notice dialogue box. Just click on the Enable to open the excel.

Here, you can see the SharePoint Online list items is exported to Microsoft Excel.

This is how to export a SharePoint list to excel.

You may also like, How to display excel spreadsheet in SharePoint. and SharePoint custom list example.

How to open a SharePoint list in excel

Now, we will see how to open a SharePoint list in excel. We will see here how to open a SharePoint Online list in excel.

• Open Microsoft Excel to open the SharePoint Online list.
• In Microsoft Excel, click on Data tab >> Get Data.
• Select From Online Services >> From SharePoint Online List.

Then in the next dialog box provide SharePoint Online Site URL and click on OK.

Note: Enter the Site URL, no need to add list URL.

• Once you click on the OK, it will open the Navigator where you can see all the SharePoint Online lists presented in the SharePoint site.
• Select a SharePoint list or you can also select multiple lists to open it.
• Now, Click on Transform Data at the bottom of the page and it will open the SharePoint list in Power Query Editor.

Note: If you have not signed in excel, then it might ask you to provide the credentials.

If you will click on Load, then it will display all the columns from the SharePoint list that includes the default columns also. If you want to display only the selected columns, then click on Transform Data.

From the Power Qwery Editor window, you can select the columns, click on File >> Choose Columns.

In Choose Columns window, you can choose the list columns to display in the excel, then click on OK.

After selected the columns, click on Close & Load to see your list.

Here, you can see your SharePoint Online list in Microsoft Excel with your selected columns.

This is how we can open a SharePoint Online list in excel.

Access to the resource is forbidden (error) excel SharePoint

While trying to open the SharePoint list items in excel, you might get an error “Access to the resource is forbidden“, if you will try to connect to SharePoint Online site from Data -> From Web and then provide the URL.

Once you click on the OK, a dialogue box will appear with an error message “Access to the resource is forbidden”.

To fix the issue, try to connect to the SharePoint site from, Data -> Get Data -> From Online services -> From SharePoint Online List.

Get data from SharePoint Folder in excel

Here, you will see how to get data from SharePoint folder in excel.

Open an excel workbook, go to Data -> Get Data -> From File -> From SharePoint Folder.

Now, it will ask to provide the Site URL and then click on OK. It will redirect to the Navigator preview with all the list of files in the SharePoint site.

If you will click on Load, then it will display all the columns from the SharePoint list that includes the default columns also. If you want to display only the selected columns, then click on Transform Data.

This is how to get data from SharePoint Folder in excel.

How to export SharePoint list to CSV using Microsoft flow

Here, I am going to explain how to export SharePoint list to CSV using Microsoft flow.

• Open SharePoint Online list, click on Automate>> Power Automate >> Create a flow in the commad bar.

In Create a flow window, click on Show more then select “Copy SharePoint list items into a CSV each week”.This template will copy the SharePoint list data and save it as CSV file in OneDrive.

Once you select the template, Microsoft will redirect you the flow site and set the connections across services.

Once the connections are established, you can edit the flow and set the SharePoint Path in Get items.

In Create CSV table, flow creates the CSV table and also creates Output.csv file name.

Now, click on Save .

In Test Flow window, select “I’ll perform the trigger action” option then click on Test.

In the next window click on Run Test.

Here, you can see that your flow ran successfully.

Open OneDrive and you can see your excel file created by flow.

Here, you can see that SharePoint data is exported to the excel file.

This is how to export SharePoint list to CSV using Microsoft flow.

You may like the following SharePoint tutorials:

• Power Automate update SharePoint list increment integer field
• Power Automate update SharePoint list item
• How to delete all items from SharePoint list
• SharePoint list permissions
• How to create Power BI report from SharePoint list
• Create SharePoint list view
• SharePoint list delete title column or SharePoint list title column remove
• How to create a calculated column in SharePoint List or Library

In this SharePoint tutorial, We discussed how to export a SharePoint list to excel. Also, We covered the below topics as:

• How to open a SharePoint list in excel
• Access to the resource is forbidden (error) excel SharePoint
• How to open a SharePoint list in excel (export sharepoint list to excel)
• Get data from SharePoint Folder in excel
• How to export SharePoint list to CSV using Microsoft flow