How To Connect a SharePoint List To Excel
HTML-код
- Опубликовано: 8 июн 2024
- This SharePoint Online list tutorial will demonstrate how to connect Excel spreadsheet to SharePoint list. Specifically, it will demonstrate how to connect SharePoint List to Excel and connect SharePoint list to Excel Power Query. To connect excel to SharePoint List means to establish a connection from within a Microsoft Excel spreadsheet to the SharePoint Online list such that data can be retrieved and refreshed on demand. In the Microsoft 365 version of Microsoft Excel, the SharePoint list to excel data connection is pre-built in the Excel data tab. From that connection, users simply need to provide the address of the SharePoint Online site that the list is hosted on and input there credentials. Once you add SharePoint list connection to excel and link SharePoint list to excel, the connection will be saved and you will be able to refresh the data by clicking a button. Note that you can also follow the steps outlined in this tutorial to connect SharePoint list to excel 2016.
It is also important to note that this video does not demonstrate how to display the values of complex SharePoint list column types such as lookup columns or person type columns. In this video, those values will be displayed as [List]. These values can be displayed using Microsoft Power Query. To learn how to do this, watch part two of this tutorial below:
Found this video helpful? Say thanks by "Buying Me a Coffee": bit.ly/4bgKxE3
🔔JOIN THE SHAREPOINT LIST FUNDAMENTALS PRE-LAUNCH ENROLLMENT - LIMITED SPOTS AVAILABLE🔔: bit.ly/3yIiE9D
GET YOUR FREE COPY OF MY THREE SHAREPOINT TIPS TO SUPERCHARGE YOUR PRODUCTIVITY HERE:
bit.ly/3Q921sH
CHECK OUT PART 2 OF THIS VIDEO:
• Connect A SharePoint L...
Timeline
0:00 - Start
0:25 - What does it mean to connect a SharePoint List to an Excel Spreadsheet
1:18 - How to link a SharePoint List to an Excel Spreadsheet
7:55 - How to connect a SharePoint List to Excel Power Query
8:50 - How to change SharePoint List to Excel synchronization settings
10:00 - How to refresh a SharePoint List to Excel connection
#SharePoint #Microsoft #microsoft365
MORE ABOUT ME
LOOKING FOR TRAINING OR CONSULTING - CONTACT ME AT:
bit.ly/3OLDmbh
LETS BE FRIENDS:
🔔Instagram: / luigiiacobellis
🔔LinkedIn: / liacobe
🔔Twitter: / luigiiacobellis
🔔TikTok: / luigiiacobellis
🔔Reddit: / owndirector1326
🔔Website: luigiiacobellis.com/
🔔Linktree: linktr.ee/Liacobellis
GOT A VIDEO IDEA? FILL OUT THIS FORM:
bit.ly/3yjEewm
SIGN UP FOR MY MONTHLY NEWSLETTER:
📧 landing.mailerlite.com/webfor...
PRODUCTS THAT I USE TO CREATE MY RUclips VIDEOS:
⭐ Amazon StoreFront: www.amazon.com/shop/luiiacobe...
⭐USB Microphone - Yeti X: amzn.to/3QLpfVt
⭐ Web Cam -Dell UltraSharp HDR 4K: amzn.to/3HQ9TuO
⭐ Laptop - Dell XPS 9510: amzn.to/3ng1zL9
⭐ Monitor - Dell Curved 4K UHD: amzn.to/3OKjCoL
⭐ Mouse-Keyboard - Dell Premier Multi-Device: amzn.to/3OlI9kf
⭐ Lighting Kit - LimoStudio 700W Series: amzn.to/3u1V82q
SOFTWARE THAT I USE TO CREATE MY RUclips VIDEOS & CONTENT:
⭐ Screen Recording & Editing - Camtasia: bit.ly/3NmF9mf
⭐ Screenshots & Photo Editing - SnagIt: bit.ly/3NmF9mf
⭐ RUclips Thumbnails - Canva Pro: bit.ly/3nj1Rkx
⭐ Content Tracking - AirTable: bit.ly/3dYsV3H
⭐ Email Newsletter Creation - MailerLite: bit.ly/3ET6SYb
⭐ Mailing List Forms - ConvertKit: bit.ly/3xVD5ff
⭐ Social Media Scheduling - Later: bit.ly/3lgeCuW
⭐ VPN - ExpressVPN - 30 Days Free: bit.ly/3NmGUjl
⭐ Task Tracking - Trello: bit.ly/3xToX69
Note that I may earn a small commission at no additional cost to you from purchases made using these links. 
Наука
🔔JOIN THE SHAREPOINT LIST FUNDAMENTALS PRE-LAUNCH ENROLLMENT - LIMITED SPOTS AVAILABLE🔔: bit.ly/3yIiE9D
Found this video helpful? Say thanks by "Buying Me a Coffee": bit.ly/4bgKxE3
PART 2 OF THIS TUTORIAL WHERE I DEMONSTRATE HOW TO DISPLAY THE NAMES AND EMAILS OF PERSON COLUMNS AND VALUES OF LOOKUP COLUMNS:
ruclips.net/video/dJbwDTMXt74/видео.html
GET YOUR FREE COPY OF MY THREE SHAREPOINT TIPS TO SUPERCHARGE YOUR PRODUCTIVITY HERE: bit.ly/3Q921sH
OTHER SHAREPOINT NEWS TUTORIALS:
How To Add a News Web Part To a SharePoint Online Site:
ruclips.net/video/QtwiGCdw2rM/видео.html
How To Delete a Post in a SharePoint Online News Web Part:
ruclips.net/video/ZrwjhHW_17k/видео.html
WATCH NEXT:
How To Connect a SharePoint List To Excel:
ruclips.net/video/cz9xAaUD4Cc/видео.html
How To Add Email Notifications To A SharePoint List:
ruclips.net/video/qrD1tleQepM/видео.html
How To Add A Calendar To A SharePoint Online Site:
ruclips.net/video/0dMwNZrOLN4/видео.html
How To Create A Status Column In A SharePoint List:
ruclips.net/video/b3taSCzzEpU/видео.html
CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: ruclips.net/p/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh
thanks for the very well structured video. And also for your cristal clear accent. Your English is really easy to understand. Much appreciated !
You're very welcome Virginie and thank you for watching
CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: ruclips.net/p/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh
Thanks so much, this tutorial had exactly what I was looking for. Good stuff
You're very welcome and thanks for watching
Very clear, excellent! Thank you very much.
You're very welcome gaguidi! Thanks for watching
This was a life saver for me. Thank you!
You're welcome!
This has been so helpful
@simonesteevens599 You're very welcome! Thanks for watching!
Thanks 🙏
You're very welcome and thanks for watching!
Great video.. thanks
@mrkd4126 You're very welcome! Thanks for watching!
This is a fantastic tutorial that very concisely explained what I needed and was looking for instructions on for longer than I care to admit. Thank you! Subscribing and following.
Thank you for the kind words. I'm glad you found this video helpful
Hi,I was exactly looking for this video, thanks for video. I have a question, I don’t want this share pony info to be displayed and viewed by any other person and just want to use SharePoint master file for searching the values in another xl file
This is great but how can we actually enter data in Excel and have it updated in SharePoint and MS Lists? Really appreciate the help and will subscribe. Very clear and concise; just doesn't fit my exact use case. Thanks a bunch.
Hi Chris, my apologies for the delay. Unfortunately there is no way to update a list using out of the box functionality in SharePoint. There are some solutions posted on the Microsoft Tech Community that detail workflows you can build in Power Automate to do this and there are a few third party tools that can also do it. Although these are rather costly. I do have in my backlog to post a tutorial outlining how to create a workflow to do this in the future. Hope this helps and thanks for watching
My thoughts exactly. Haven’t find anything on that.
How do I make some of the columns in excel not populate as "[List]," but rather the data selected in that column in SharePoint? I noticed some of your columns populated the same way. Great video! Thanks for your help.
Hi Amanda, so sorry for the delay! it is possible but it requires transforming the data using Power Query. Person type columns are complex columns in that a person has multiple attributes. You would need to specify which attribute you want to incorporate into the table. Here is an article that outlines how to use Power Query. I will also be creating a tutorial to demonstrate this in the coming weeks. Hope this helps!
support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f
@@LuiIacobellis did you create a video for these power query features?
By the way this video was perfect!
@@kateye_diamond Sorry for the delay! Unfortunately, I have not as of yet. I'll be bumping this up on my backlog and will likely publish something in about a month. I'm glad you found this video helpful!
Hi there I just published a tutorial outlining how to address this: ruclips.net/video/dJbwDTMXt74/видео.html
Thanks for sharing, what about updating the Sharepoint list from excel
Hi there, you're very welcome. Unfortunately you cannot update a SharePoint list from an Excel sheet through just SharePoint. You can do this using Microsoft Power Automate. I will be posting a tutorial on how to do this in the future. Hope this helps! Thanks for watching!
Hi, great videos! I have a column where the user can select multiple options, when these feed through to power query/excel, each option creates a new row with the same data but that different column entry. E.g it’s a failure reason and they can select multiple reasons but instead of 1 list entry it shows as 5 in line with the reasons, whereas I just want all reasons separated by a comma in one cell. Can you help please?
Hi Lui, Nice video and very good information. I want to ask what version of excel are you using? Enterprise?
I am using business but I can't access the online services button.
Hi Mihail, thank you for the kind words. I am using Microsoft 365 Enterprise. The Online Services Button is only available in Microsoft 365 for enterprise unfortunately. I hope this helps and thanks for watching.
Great video! Thank you! One question, my SP list has LOOKUP columns and when I create the connection and it pulls the data in, for any LOOKUP column, the value is [Table]. Is there a way to have that data pulled in from the other list? Will it work if I have a connection to that list created? I also have column values of [List] referenced from another list on the same site that doesn't import the values.
Hi there I just published a tutorial outlining how to address this: ruclips.net/video/dJbwDTMXt74/видео.html
Hey your tutorial really helped, need your assistance i have a share point folder where am having a thousand of pdf file with specific naming convention so what i want is to connect that share point with excel so i know what pdf file names are listed there and whenever a new file added so it should add in excel automatically as you explained in tutorial i have followed every step but on navigation page after i put share point link i dont know what to do can u pls assist?
Hello, @Lui Thank you for posting this tutorial I have a query similar to this example some of the columns show up as lists instead of names - I have Employee Name, Created By, and Modified by how to fix this please share any solutions
Hi there I just published a tutorial outlining how to address this: ruclips.net/video/dJbwDTMXt74/видео.html
Hi! This was super helpful. Thank you! What happens once the limitation of entries in SharePoint List is maxed out? Do the older entries in SharePoint list automatically delete from the Excel file?
I'm glad you found this helpful! There isn't a limit to the number of items you store in a SP list. You can only view 5000 at a time. You can export more than 5000 items however. This approach does not perform any update actions or deletions back to the SP List, it just retrieves the data. Hope this helps!
Thanks Lui, this was very helpful, question: can you change columns around in the Spread Sheet if it is pulling data from a source?
Yes you can! You would need to change the order using Power Query however.
Hey, great video! I'm having an issue where the data being grabbed from sharepoint is sometimes in the form of sharepoint's internal identifiers rather than the actual textual data. For example, a "Fund Title" column is often replaced by "Fund Title ID" and the values are replaced with random numbers instead of text, even though my sharepoint list looks just fine. This is happening to about half of the textual columns I'm importing and I can't find any info online. Any ideas?
Hi Travis, apologies for the delay. When you see this issue what type of columns does the issue occur for? Using Power Query, you can actually drill down into the full list of attributes for a column and select which you'd like to add to the excel file. Here is an article that outlines how to use Power Query. I will also be creating a tutorial to demonstrate this in the coming weeks. Hope this helps!
support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f
I have exact the same issue. If I use the sharepoint Excel export, resulting in a *.iqy file, it works fine. Do I use the data connection IDs are mostly displayed instead of text. By the way, if one use the sharepoint data connection in ACCESS, text is always displayed. Also you see in the ACCESS structure overview of that sharepoint data connection, that , if the content is defined in a separate table, the ID from the dataset is used in Excel instead the name. So we have to define for connection/import instead of attribute "ID" the attribute "name" or whatever it may be called. @Lui where are these attribute selecting function?
Great Video -Question - How can we send the data back to SharePoint List from Excel?
Hi there, unfortunately there is no easy way to do this from Excel or SharePoint using out of the box functionality. You would either need to build a Flow in Microsoft Power Automate or use a third-party application. There are several available out there. Hope this helps and thanks for watching.
This is sooo close to what I need to do! Is there a way to do this but with letters we receive from our client? We have a location in sharepoint and want to automatically populate data into a sheet.
Hi Shanelly Becerra there, I would suggest posting this question over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!
Hey Great Video, helped a lot, i am having one issue though, when i pull the data from the sharepoint list, it is coming from an app which is being used, i can see that any field in the app that is a dropdown list only comes up "list" on the download, which means all of my formulas are not working as they are looking for a specific name. any help would be greatly appreciated.
Hi there I just published a tutorial outlining how to address this: ruclips.net/video/dJbwDTMXt74/видео.html
Hi Lui, thanks for the informative video. For some reason Sharepoint does not com up in my Excel as a Data Source. Do you know how to fix this?
Hi Riaan, the SharePoint data source is only available in the latest Microsoft Excel (i.e. Microsoft 365 version). Hope this helps and thanks for watching!
Does this connection have the 5000 row limitation, or will you be able to sync more data than that from Sharepoint lists to excel?
Hi there, based on my testing the 5000 row limitation would also apply to establishing connections to your list data from Excel. Hope this helps and thanks for watching!
Hi thanks for the video but when I copy the link I do not see my files in the list but I see appdata app files composed looks etc any idea why I get this ?
Hi I wonder if I can do the same thing with multiple share points lists
Hi there, you can indeed connect to multiple SharePoint lists provided that they exist on the same site collection. As outlined in the video, when you are attempting to connect, the prompt requires the Site Collection URL. When loading data, there is a button that says select multiple. If you check it, you can select multiple lists. Hope this helps! Thanks for watching.
is there a way to make the pictures i have stored in sharepoint come along with the excel file?
Hi there, unfortunately there is no "easy" way to export images in this manner. The images themselves are not stored in the list directly thus the URL that is included when you export attachments. Here is an article that outlines alternatives to accomplish this: answers.microsoft.com/en-us/msoffice/forum/all/exporting-an-image-from-a-sharepoint-list-to-excel/1f284fc4-599d-4f6b-9676-6da745b3628d - Hope this helps and thanks for watching!
i have watch and done the update option from excel to sharepoint with linked table without using powerautomate. i lost the reference. any help?
Hello! Just asking if this can be done vice versa? i.e. if I update columns in the Excel sheet will it get reflected back to the sharepoint list? Thanks!
Apparently not possible without an easy interface like this one
Correct. You cannot update a SharePoint list from Excel without using a third-party tool OR building a Power Automate workflow. Thanks!
Hi, Thanks for the video !! which MS office being used here ? I have M365 BP but can't get the option "From online service " !! anyone can help please.
This is the Microsoft 365 version of Excel. Older versions of Excel won't feature the "From online services" data connector. Hope this helps!
Excellent
I have one question
In my SharePoint list i used lookup function ex. If 1 means box, 2 means table, 3 means chair
While export to excel, instead of items names like box it is updating value as 1
How to update exactly as per SharePoint list
Hi S.N, Lookup columns are an advanced type of column that feature multiple attributes. In order to display the desired value, you have to use Power Query, to drill down into the full list of attributes for a column and select which you'd like to add to the excel file. Here is an article that outlines how to use Power Query. I will also be creating a tutorial to demonstrate this in the coming weeks. Hope this helps!
support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f
Thank you so much
@@LuiIacobellis This is working now and able to get the value....thanks for the information
@@s.n.senthilnarayanan749 That is great. Just to confirm, you were able to retrieve the specific attribute of a lookup column using Power Query?
Hi Lui.. Thanks for this helpful tutorial.. I have a question.. Can it be possible to move data to excel in document library from SharePoint list when excel is password protected
To the best of my knowledge, you could move the data to an excel stored in a library using Power Automate however, I don't think you could do so if the sheet was password protected.
Hi I noticed that in your "Requestor" field it brought in "List" . Is there a way for it to show the actual linked account and display?
Yes I have the same issue
Hi there, it is possible but it requires transforming the data using Power Query. Person type columns are complex columns in that a person has multiple attributes. You would need to specify which attribute you want to incorporate into the table. Here is an article that outlines how to use Power Query. I will also be creating a tutorial to demonstrate this in the coming weeks. Hope this helps!
support.microsoft.com/en-us/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f
@@LuiIacobellis Has this video been created?
@@christineberry2736 HI Christine, this video will be going live today at 9:00am EST. Thanks
Hi there I just published a tutorial outlining how to address this: ruclips.net/video/dJbwDTMXt74/видео.html
Hello another great video. I have an issue though my colleague can connect to my SP site this way and I want to prevent this , is there a way please
Hi John, the way to prevent this would be to restrict access to the List. If the user doesn't have permission to it, they won't be able to connect. That is why connecting requires authentication with a named user account. Hope this helps.
@@LuiIacobellis issue is connecting the list to Powerapps and giving everyone contribute will allow everyone to see everyone else’s data , item level permissions won’t work as some others also need to go back in and make changes to entries
@@johnbrennan8442 Hi John, sorry I must have misunderstood your initial question. I would suggest posting your use case over on the Microsoft Tech Community as someone will have better insight on how to solve for it. Based on your reply, I'd suggest looking into Microsoft Power Apps (and store the data in Dataverse or Dataverse For Teams). This solution allows you to easily implement granular permissions. Hope this helps!
@@LuiIacobellis Thank you for your quick replies
@@johnbrennan8442 you're very welcome
Hi, Is there any way that connect sharepoint list to Excel online
Hi Nadimpally, unfortunately it is not possible to connect to the SharePoint list from Excel online directly based on my testing. However, if you connect to the SharePoint list in the desktop version of Excel, you can access the SharePoint list data from Excel Online. So basically, connect first in the desktop app and then you can access it using Excel online. Hope this helps! Thanks for watching!
Hey, great Video. I am having an issue, when i am grabbing the data from sharepoint , some of the columns are missing data ,like the requester name, team name etc. Rather i am getting "[List]" as the value in the name column. Can you help me fix this issue .
Hi there, I have a second part to this tutorial demonstrating how to retrieve the values of complex column types. You can watch it here: ruclips.net/video/dJbwDTMXt74/видео.html
Hi, I have tried this and works well. Only issue is that the date that is populating on excel is one day behind than the date on the sharepoint list. How can I correct this?
Hi there, this is likely the result of a time zone settings. SharePoint is likely including the date and time & when the data is exported to excel, it may be converted to another time zone. I'd suggest checking the time zone settings on your SharePoint site to troubleshoot this. This article can help explain how to do this: www.sharepointdiary.com/2017/06/sharepoint-online-change-time-zone-using-powershell.html#:~:text=SharePoint%20Online%20timezone%20settings%20can%20be%20set%20by,right%20of%20the%20page%20to%20save%20your%20changes. Hope this helps!
@@LuiIacobellis thank you. And also encountered another issue. In one of the lists, one of the column is about manager’s name and it’s people or groups option been selected. I have noticed that this is also not been exported properly. It’s just coming as [List] in the entire column instead of names.
@@bonjour1529 It will export properly only your managers are members of the site collection the list belongs to.
How can I connect data between multiple workbooks on share point?
Unfortunately it is not possible to connect multiple SP lists to one workbook. Hope this helps!
Hi, if i dont have enterprise license shall i still use this option, if yes please let me know.
Hi, you would not need an enterprise licence to Microsoft excel for this to work. Thanks
How do u do this for MacBook excel n share point list
Hi there, I've not tested this myself but I did some research and it appears the steps should be the same. Excel for Mac supports external data connections. I'd suggest posting this over on the Microsoft Tech Community (there may even already be a thread on it). Thanks!
How can I do the steps using Macbook? I do not see any options. :(
Hi Rein, unfortunately I don't think its possible to do this on a MAC. I'm not entirely sure though as I am a windows user (sorry). Here is a link to a forum that suggests it not possible. techcommunity.microsoft.com/t5/excel/excel-for-mac-get-data-from-table-range/m-p/2596975 - Hope this helps!
I created the connection following this steps and it loads the items of the list the first time, but if I do a refresh again it will not load the new items or the changes in existing items
Hi there, I'm sorry to hear you're experiencing this issue. When you try to refresh the list have you checked the connection to ensure it hasn't been broken? When the data doesn't refresh it is usually an issue with the connection. Other things to consider, have your credentials changed since you established the connection? I hope this helps and thanks for watching.
How do you do a 2 way sync?
You would need to build a Power Automate workflow to do this OR you can also synchronize data between SP Lists using third party apps such as ShareGate. Hope this helps!
I want to edit the data in excel but not in Lists, is this possible? Every time I change anything in excel the data refreshes and then goes back to whatever is shown in Lists. Really what I need it to load only new data since last refresh and ignore the previous data?
Any help would be grateful as I cant find anything on Google hahaha.
Unfortunately, you cannot edit list data from Excel without using a third-party tool or developing a workflow in Power Automate. Tools such as ShareGate allow you to update SharePoint lists via importing Excel sheets.
I see the data pulled from the list shows as "[List]" in the Requestor and BU columns. I'm having the same problem. Is there a fix for this?
Found your other video which helped me to fix this problem. Thanks!
I was just about to share part 2 but glad you beat me to it! You're welcome
which excel version?
Hi there, I am using the Microsoft 365 version of Excel. You might not see the Sharepoint connector in older versions.
can we extract 100k plus items using this method? Thank you! :)
Unfortunately not. The 5000 item view threshold would apply to this approach to extracting data as well
If only updating excel would update SharePoint then updating those lists would a lot easier
Hi there! Yes, this seems to be a common request. There are third-party tools that allow you to do this. Hopefully Microsoft implements this as a native feature sometime soon. Thanks for watching!
Is this list still online? I am not able to access it
Hi sorry for the delay. I'm not sure what you mean by is this online. If you can clarify, I'll attempt to assist. Thanks!
@@LuiIacobellis I mean the SharePoint link that you shared in the video, I was not able to access it :(
@@alimbhanwadiya I'm not sure what link you are referring to. In this video, I demonstrate how to connect a SP List to an Excel spreadsheet. The list I'm using is within my Microsoft environment.