How To Connect a SharePoint List To Excel

[@LuiIacobellis]

🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔: bit.ly/3XQq45i

[@ChrisSmithFW]

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.

[@LuiIacobellis]

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.

[@amandaliebig5654]

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.

[@LuiIacobellis]

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

[@kateye_diamond]

@@LuiIacobellis did you create a video for these power query features? By the way this video was perfect!

[@LuiIacobellis]

@@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!

[@LuiIacobellis]

Hi there I just published a tutorial outlining how to address this: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@LuiIacobellis]

CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: kzbin.info/aero/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh

[@kevinnehls898]

Poorly worded title. This is NOT for connecting a SharePoint List to Excel, updating the list from Excel. This is for connecting Excel to a SharePoint List, updating Excel from the list.

[@user-mw9xn5qj9c]

Hi! I am working with Excel online - I can't seem to be able to Get Data from my Online Services. Does this only work for the desktop version?

[@LuiIacobellis]

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: kzbin.info/www/bejne/mnvFqHeKgr2nbZY 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: kzbin.info/www/bejne/h6Xamnp5mdxlqK8 How To Delete a Post in a SharePoint Online News Web Part: kzbin.info/www/bejne/kKPam5t-jMRkbc0 WATCH NEXT: How To Connect a SharePoint List To Excel: kzbin.info/www/bejne/maucqXSXiqlnecU How To Add Email Notifications To A SharePoint List: kzbin.info/www/bejne/p6OnYqeimraYpq8 How To Add A Calendar To A SharePoint Online Site: kzbin.info/www/bejne/ZpWwqIGQp7R_hJY How To Create A Status Column In A SharePoint List: kzbin.info/www/bejne/mGTXkoZ5r994prc CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: kzbin.info/aero/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh

[@spitsparrow]

So it WONT go the other way. From changing excel data first and have it update a sharepoint list? It only goes one direction? IS this still current?

[@jacisnerosm]

Thanks for sharing, what about updating the Sharepoint list from excel

[@LuiIacobellis]

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!

[@junaidakhtar229]

i have watch and done the update option from excel to sharepoint with linked table without using powerautomate. i lost the reference. any help?

[@demontrav1918]

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?

[@LuiIacobellis]

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

[@myrthis]

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?

[@VirginieLouvois]

thanks for the very well structured video. And also for your cristal clear accent. Your English is really easy to understand. Much appreciated !

[@LuiIacobellis]

You're very welcome Virginie and thank you for watching

[@achinchwadkar]

Great Video -Question - How can we send the data back to SharePoint List from Excel?

[@LuiIacobellis]

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.

[@aliz9899]

If only updating excel would update SharePoint then updating those lists would a lot easier

[@LuiIacobellis]

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!

[@Designsbyfuze]

is there a way to make the pictures i have stored in sharepoint come along with the excel file?

[@LuiIacobellis]

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!

[@reinlopez8321]

How can I do the steps using Macbook? I do not see any options. :(

[@LuiIacobellis]

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!

[@benedictdudas5480]

can we extract 100k plus items using this method? Thank you! :)

[@LuiIacobellis]

Unfortunately not. The 5000 item view threshold would apply to this approach to extracting data as well

[@nadimpallydevisree402]

Hi, Is there any way that connect sharepoint list to Excel online

[@LuiIacobellis]

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!

[@shafiulamin4128]

mate, this could have been described in 4/5 minutes.

[@LuiIacobellis]

Thanks for the feedback

[@jagdishagarwal4173]

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

[@RebekahHowes-gp2wz]

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?

[@delpepitozipepit1010]

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 ?

[@azharsayyed2473]

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?

[@MuthurajaSelvamani]

Hi, if i dont have enterprise license shall i still use this option, if yes please let me know.

[@LuiIacobellis]

Hi, you would not need an enterprise licence to Microsoft excel for this to work. Thanks

[@dcochran12]

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?

[@dcochran12]

Found your other video which helped me to fix this problem. Thanks!

[@LuiIacobellis]

I was just about to share part 2 but glad you beat me to it! You're welcome

[@s.n.senthilnarayanan749]

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

[@LuiIacobellis]

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

[@s.n.senthilnarayanan749]

Thank you so much

[@s.n.senthilnarayanan749]

@@LuiIacobellis This is working now and able to get the value....thanks for the information

[@LuiIacobellis]

@@s.n.senthilnarayanan749 That is great. Just to confirm, you were able to retrieve the specific attribute of a lookup column using Power Query?

[@vincenthsu535]

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?

[@joshthacker3120]

Yes I have the same issue

[@LuiIacobellis]

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

[@christineberry2736]

@@LuiIacobellis Has this video been created?

[@LuiIacobellis]

@@christineberry2736 HI Christine, this video will be going live today at 9:00am EST. Thanks

[@LuiIacobellis]

Hi there I just published a tutorial outlining how to address this: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@Gaurangg09]

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

[@LuiIacobellis]

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.

[@habibabdulla3122]

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.

[@LuiIacobellis]

This is the Microsoft 365 version of Excel. Older versions of Excel won't feature the "From online services" data connector. Hope this helps!

[@JimHearn]

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.

[@LuiIacobellis]

Hi there I just published a tutorial outlining how to address this: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@sbhandari07]

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 .

[@LuiIacobellis]

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: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@dj_jazzywhut]

Thanks Lui, this was very helpful, question: can you change columns around in the Spread Sheet if it is pulling data from a source?

[@LuiIacobellis]

Yes you can! You would need to change the order using Power Query however.

[@spencerb52a]

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.

[@LuiIacobellis]

Thank you for the kind words. I'm glad you found this video helpful

[@rachelhutson535]

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?

[@LuiIacobellis]

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!

[@vijaysharma7677]

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

[@LuiIacobellis]

Hi there I just published a tutorial outlining how to address this: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@chrispickering3956]

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.

[@LuiIacobellis]

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.

[@RiaanPretorius]

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?

[@LuiIacobellis]

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!

[@PeterHarket]

Does this connection have the 5000 row limitation, or will you be able to sync more data than that from Sharepoint lists to excel?

[@LuiIacobellis]

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!

[@martinbaker2225]

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.

[@LuiIacobellis]

Hi there I just published a tutorial outlining how to address this: kzbin.info/www/bejne/mnvFqHeKgr2nbZY

[@Joemoejojo]

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.

[@LuiIacobellis]

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!

[@ZestQuest_zone]

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.

[@LuiIacobellis]

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.

[@tavaresstephens1572]

How can I connect data between multiple workbooks on share point?

[@LuiIacobellis]

Unfortunately it is not possible to connect multiple SP lists to one workbook. Hope this helps!

[@mastercp82]

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

[@LuiIacobellis]

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.

[@gamerznoctural161]

How do u do this for MacBook excel n share point list

[@LuiIacobellis]

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!

[@agi.k.5319]

Hi I wonder if I can do the same thing with multiple share points lists

[@LuiIacobellis]

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.

[@wrm8460]

Thanks 🙏

[@LuiIacobellis]

You're very welcome and thanks for watching!

[@98Doomster]

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!

[@jav0033]

Apparently not possible without an easy interface like this one

[@LuiIacobellis]

Correct. You cannot update a SharePoint list from Excel without using a third-party tool OR building a Power Automate workflow. Thanks!

[@yogakarona3691]

Thanks so much, this tutorial had exactly what I was looking for. Good stuff

[@LuiIacobellis]

You're very welcome and thanks for watching

[@gaguidi]

Very clear, excellent! Thank you very much.

[@LuiIacobellis]

You're very welcome gaguidi! Thanks for watching

[@avery2240]

This was a life saver for me. Thank you!

[@LuiIacobellis]

You're welcome!

[@alimbhanwadiya]

Is this list still online? I am not able to access it

[@LuiIacobellis]

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!

[@alimbhanwadiya]

@@LuiIacobellis I mean the SharePoint link that you shared in the video, I was not able to access it :(

[@LuiIacobellis]

@@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.

[@bonjour1529]

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?

[@LuiIacobellis]

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!

[@bonjour1529]

@@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.

[@driard]

​@@bonjour1529 It will export properly only your managers are members of the site collection the list belongs to.

[@mrkd4126]

Great video.. thanks

[@LuiIacobellis]

@mrkd4126 You're very welcome! Thanks for watching!

[@simonesteevens599]

This has been so helpful

[@LuiIacobellis]

@simonesteevens599 You're very welcome! Thanks for watching!

[@DanielPerez-hy6qi]

How do you do a 2 way sync?

[@LuiIacobellis]

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!

[@johnbrennan8442]

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

[@LuiIacobellis]

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.

[@johnbrennan8442]

@@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

[@LuiIacobellis]

@@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!

[@johnbrennan8442]

@@LuiIacobellis Thank you for your quick replies

[@LuiIacobellis]

@@johnbrennan8442 you're very welcome

[@vanquangnguyen7246]

which excel version?

[@LuiIacobellis]

Hi there, I am using the Microsoft 365 version of Excel. You might not see the Sharepoint connector in older versions.

[@Bobsikus]

7:25 most imporant information, thank you, for me pointless then, I wanted to update the list via Excel because it would be much faster ... thank you microsoft for again complicating possibly good thing