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
7 месяцев назад
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
@@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!
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.
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!
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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QtwiGCdw2rM.html How To Delete a Post in a SharePoint Online News Web Part: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZrwjhHW_17k.html WATCH NEXT: How To Connect a SharePoint List To Excel: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cz9xAaUD4Cc.html How To Add Email Notifications To A SharePoint List: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qrD1tleQepM.html How To Add A Calendar To A SharePoint Online Site: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0dMwNZrOLN4.html How To Create A Status Column In A SharePoint List: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-b3taSCzzEpU.html CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: ru-vid.com/group/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh
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.
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!
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?
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!
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!
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 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?
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 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
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
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.
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.. 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 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
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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-dJbwDTMXt74.html
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
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.
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!
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.
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, 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 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!
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.
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!
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!
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!
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.
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.
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.
@@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.
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
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.
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!
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!
