In this SharePoint tutorial, we will discuss how to open SharePoint list in excel or how to export SharePoint list to excel. I will also show, how to update excel from SharePoint list automatically.
Also, we will see how to fix the error, Access to the resource is forbidden excel SharePoint that comes while trying to open SharePoint list in excel.
Open SharePoint list in excel
Now, let us see how to Open SharePoint list in excel. Here, we will connect with the SharePoint Online site from excel.
In my SharePoint Online site, I have created a custom list as Employees. And the list looks like below with some data.

Now, let us see how to open this SharePoint Online list in excel.
First of all Open a excel file in your desktop.
Then click on Data -> Get Data -> From Online Services -> From SharePoint Online List like below:

Then it will ask you to provide the SharePoint Online Site URL like below, you can provide also the list URL, but you might get an error.

Once you click on OK, it will ask you to enter the Credentials. Here first select Microsoft account.
It will show the Sign-in button if you have not already signed in with an Office 365 account.
Click on the Sign-in button and then it will ask you to enter the user name and password.

Then you can see it will show you Sign in as different user. Then select the SharePoint site where you list exists.
Then click on the Connect button.

Then it will display all the lists and libraries presented in the SharePoint Online site.
Select the particular list, in this case I selected Employees list. In the right site it will display all the columns that includes all the out of box hidden columns also.
If you want to load all the columns, then click on the Load button.
But if you want to load the select columns then click on the Transform Data button like below:
Here, I want to select a few specific columns from the SharePoint Online list.

Once you will click on the Transform Data button, it will open the Excel Power Query Editor from where we can select the columns that we want to display from the list.
Here click on Home -> Choose Columns -> Choose Columns like below:

Then it will Open a dialog box, from where we can Choose Columns.
Here, you can uncheck every column and select only those columns that we required.
Here I wanted to show the below SharePoint list columns:
- ID
- Title
- FullName
- Designation

Once you click on Choose columns, then click on OK. Then Close & Load button from the ribbon, like below:

Now, you can see your SharePoint List data or items will be there in the excel.

This is how we can open SharePoint list in excel.
Update excel from sharepoint list automatically
Once your SharePoint Online list data in excel, the next question is how to update excel from SharePoint list automatically.
Meaning If you insert an item to SharePoint Online list, then it will automatically update excel spreadsheet from SharePoint list.
From the excel spreadsheet, click on Data -> Queries & Connections like below:

Then click on the Refresh icon like below:

This will open the Query Properties dialog box from where we can enable auto update excel from SharePoint list option.
If the properties window did not open, then click on the Refresh icon and then click on … and then click on Properties like below:

Here in the Query Properties dialog box, check the below checkboxes:
- Enable background refresh: Once you enable background refresh, it will refresh and sync list data to excel in the background.
- Refresh every minutes: Here, you can set the interval when you want the refresh to happen. I have set it for 2 minutes. So in every 2 minutes, the refresh will happen and if any new records are there in the SharePoint list, the list item will be synced with the excel.
- Refresh data when opening the file: If you will enable Refresh data when opening the file, then when you will open the excel, it will do the refresh.
- Refresh this connection on Refresh All: Once you enable this option, if you will click on Refresh All button from the ribbon, then refresh will happen.

Click on OK.
Enter a few items to the SharePoint list, and wait for 2 minutes, it will update excel from SharePoint list automatically.
You can see here I have added 3 items to the SharePoint Online list.

Once the automatic refresh will happen, you can see the SharePoint list items have been automatically updated in the excel.

This is how we can update excel from SharePoint list automatically without using flow.
Access to the resource is forbidden excel SharePoint
In the beginning, when I was trying to Open SharePoint list in excel, I got an error as access to the resource is forbidden excel SharePoint.
The error looks like below:
Unable to connect. We encountered an error while trying to connect. Details: “Access to the resource is forbidden“.

The error comes because I tried to get data by using the below method,
Data -> From Web and then I enter the SharePoint site URL to connect.

To fix excel sharepoint connection access to the resource is forbidden issue, the ideal way is to connect from Data -> Get Data -> From Online Services -> From SharePoint Online List.

In case you want to check out all the SharePoint connections, then you can go to Data -> Get Data -> Data Souce Settings.

This will open the Data source settings page from where you can see all your data connections.
There you can edit permissions or you can also clear permissions and make the connection again.

This is how we can fix error, access to the resource is forbidden excel SharePoint online.
The remote server returned an error 404 not found excel SharePoint
I also got an error that says, Unable to connect. We encountered an error while trying to connect.
Details: “Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:”. The remote server returned an error 404 not found.

The error was coming when I give the direct SharePoint Online list URL like below:

To fix this issue, enter the SharePoint site URL and then choose your particular list.

This is how we can fix error, The remote server returned an error 404 not found.
You may also like the following tutorials:
- How to create PowerApps collection from Excel
- Create a canvas app from Excel in Power Apps
- Create a Data Table from Excel Data using Power Apps
- Create a Power BI report from Excel using Power BI Desktop
- How to directly save Excel File into SharePoint Online Document Library
- How to export excel table to SharePoint List
- Import excel spreadsheet into SharePoint list 2013
- SharePoint audience targeting – Everything you need to know
In this SharePoint tutorial, we learned:
- How to open a SharePoint list in excel
- Update excel from sharepoint list automatically
- Access to the resource is forbidden excel SharePoint
- The remote server returned an error 404 not found excel SharePoint
I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com
Hello.
I am regular reader of your blog especially on PowerBI and PowerAUtomate. Can you please publish a blog that how to pull multiple excel to PowerBI (all the excel has same type of columns)? Can you please publish the step by step tutorial. I am using PowerBI Oct2020 version.
Regards
Avian
Hi ! Many thanks for your tutorial !
Is there an easy way to keep in sync excel file and linked list ?
F.E. – Add a row in Excel and update the list on Sharepoint
Edit or Delete a row from Sharepoint List and update the linked Excel file
Thanks in advanced for your help
Regards
Hi Mario, write data back is not possible “out of the box”.
You could do that with a 3rd party software that you install in Excel : Synchronizer for Excel and SharePoint. It will export your existing list to Excel, let you add rows in Excel, then sync it back to SharePoint as often as you want. After the sync, Excel also gets the updates from SharePoint, so that both always stay in Sync (Disclaimer: It is developed and maintained by my company.)
Can I open Sharepoint in Excel 2013?
Yes, follow the same steps.
Good Morning
i have found that Get Data from On-Line Services is available only to some Office 365 subscription
it’s unbelievable, in this case Microsoft Sucks
I have qq. Is the excel spreadsheet should be open all the time to automatically update it. When I closed the spreadsheet and update the record or add a row in SharePoint the excel will not update until I opened it. Please help
I have added this query connection Excel in SharePoint library. How to auto refresh this excel in the library without opening the excel file.
Hi Swa,
it’s unfortunately impossible as Excel is not opened, it can’t change the file.
You could write some script that would open the file, refresh and save the file, but it’s getting more technical.
Great help thank you. But when I try to convert my list to spreadsheet, the column of peoples names on sharepoint gets turned into numbers in excel. How do I fix this?
Hi Kathleen,
user names are usually well exported to Excel if these users are members of the site collection hosting your SharePoint list. (sorry for the late answer)
apparently you need a 365 enterprise license to get data from online services
This is a very good tutorial. Thank you team for this and hope to see more tutorials in future about Sharepoint online, powerapps and power automate.
How can we write back data? Great tutorial.
Hi Mario, write data back is not possible “out of the box”.
You could do that with a 3rd party software that you install in Excel : Synchronizer for Excel and SharePoint. It will export your existing list to Excel, let you add rows in Excel, then sync it back to SharePoint as often as you want. After the sync, Excel also gets the updates from SharePoint, so that both always stay in Sync (Disclaimer: It is developed and maintained by my company.)
Hi I was wondering if there is a way to update the excel list once it was connected to update sharepoint list.
Hi,
Is there any way on updating excel it will automatically update the sharepoint list without using power automate?
Thank you so much, this was the most helpful website
Thank you. This post has been very helpful.
Now that the connection is established, can updates done in the Excel file automatically update in SharePoint. I understand updates done in SharePoint will reflect in Excel but would it be able to do that the other way around