How To Connect a SharePoint List To Excel

hey there louis acabalis here thanks for stopping by in this tutorial i'm going to show you how you can connect a sharepoint online list to a microsoft excel spreadsheet now before we get started if you find this tutorial helpful please hit that thumbs up below and be sure to subscribe to stay up to date on the latest sharepoint online tutorials that i publish now let's go ahead and let's get started now what does it mean to connect an excel spreadsheet to a sharepoint online list well you can actually establish a connection between an excel spreadsheet and a sharepoint list that will retrieve the data that's stored in the sharepoint list dynamically and publish that data in an excel spreadsheet now once you establish that connection you can actually use a tool like power query to help you slice and dice and transform the data that is being retrieved from your sharepoint list directly in excel and what's even more awesome is that you can actually set your excel spreadsheet to synchronize regularly so that as new items are added to the sharepoint list or as existing items are updated those changes will also be visible in your spreadsheet so let's go ahead let's check out how to do this all right now you can see here that i have a sharepoint online list titled invoices and in this list i have a bunch of different columns with different data types so this is the list that i'm going to connect to an excel spreadsheet now to actually establish that connection what you want to do is you want to open a new workbook and you want to click on the data tab in the ribbon and next you want to click on get data and next you want to go ahead and click on from online services now it's important to note that i am using microsoft excel the microsoft 365 version so if you're trying to do this on an older version of excel it might look slightly different but the steps are generally the same now you can see here because i am using the microsoft 365 version of excel i have this option that says from sharepoint online list so i'm going to go ahead and click on it and now what i'm being prompted to do is to enter in the site url now i'm going to go ahead and pull up my sharepoint list and it's important to note that in the site url field what you want to do is you want to grab everything up to the name of the actual site so you can see here that i have selected everything after the word cites and before the word lists and i'm not including the forward slash so i'm going to go ahead and copy this and i'm going to bring up my excel spreadsheet and i'm going to go ahead and paste that in here now the next option is to select your implementation now i've gone ahead and tested the difference between these two and essentially what i've seen is that if you select 1.0 by default it's going to bring over all of the columns that exist in the sharepoint list including the default audit trail columns that are created behind the scenes and if you select 2.0 it's only going to bring over those columns that are included in the default view so for all intents and purposes you can use either of these you'll still be able to get all of the information that's in the sharepoint list and for demonstration purposes i'm just going to go ahead and select 2.0 beta to keep this tutorial a little bit cleaner next you want to go ahead and click on ok and you'll notice that this prompted me to establish the connection and if you did not enter in the correct url you would not have made it to the screen so again it's important to note that you want to make sure you grab the correct url as i just explained in the previous step now what you're being asked to do here is to actually provide the method in which you want to connect to this sharepoint instance so i'm going to go ahead and click on my microsoft account and i am going to click on sign in and this is going to prompt me to enter my microsoft 365 credentials now very important to note that if you're doing this and you're doing this in the context of an organization's microsoft environment you may not actually be able to use this method and you might have to go ahead and select windows and use your sort of windows sign-on credentials so if you have any issues with this step and again you're doing this in the context of an organization's microsoft environment you want to speak to your microsoft 365 administrators and figure out what the appropriate authentication method is for you now i'm just going to go ahead and click my account here and i am going to be signed in so you can see here it says you are currently signed in and i'm going to go ahead and click the connect button next what you can see here is the navigator menu and essentially what this is going to do is this is actually going to show you everything that exists on that sharepoint site that we entered the url in for so if i just go ahead and expand this you can see here i have a tree view of that site so human resources and below that you can see all of the different elements that exist on that site now if i had multiple lists on this site i would see all of those here and i could go ahead and select them now you'll notice that the invoices list that we saw earlier in this tutorial is listed here so i'm going to go ahead and click this and what's really cool about the navigator is that it is also going to allow you to preview that list that you want to connect to so i'll just minimize this screen here and you can see if i scroll through this it is going to display all of those columns that we saw on the sharepoint list and if i scroll over you're also going to see that it is going to display some of those audit fields that are included by default in the background now once you've selected your sharepoint list you want to go ahead and click load and you can see here that this is running and it's actually fetching the data and once it's complete you can see all of the data from our sharepoint list is now displayed here in this excel spreadsheet now to just do a quick recap you can see invoice number customer first name customer last name amount status invoice date requester month year etc and if i bring up our list again you can see invoice number customer first name customer last name amount status invoice date etc so that is how to establish a connection between a sharepoint online list and an excel spreadsheet now an important note when you're trying to connect excel to a sharepoint list you cannot actually update the data that is stored in the sharepoint list from the excel spreadsheet so you can only retrieve the data you can't actually modify the data in the excel sheet and expect it to update in the sharepoint list okay so if i try to do something like this and save this sheet or refresh the connection it's not actually going to populate back in my sharepoint list okay so very important note now the next thing that i'm going to show you how to do is how to open power query when you've established this connection and again power query is a transformation tool that you can use to help you slice and dice manipulate transform this data to create reports etc now to do that you want to bring your cursor over to the queries and connections panel and you want to just hover your cursor over the sharepoint list name and it's going to bring up this preview window so i'm just going to move my cursor back and you can see it's brought up this invoices menu now if i go ahead and click on this edit button this is going to open another window and essentially this window is microsoft power query so you can see here power query is open and again this is where you can actually come to you know manipulate this data transform it etc so that's how to open power query now i'm just going to close out of this now the last thing that i'm going to show you is how to actually set this list to synchronize in the background so again if you wanted to save this and have this automatically refresh that is something that you can actually do and again to do that you want to bring your cursor over to the queries and connections menu and hover it over your sharepoint list this time you want to click on the more options button here and you want to go ahead and click on properties and this is going to bring up the query properties menu now you can see here there is a group called refresh control and you want to make sure that you check enable background refresh and you also want to make sure refresh every is checked and you can go ahead and actually set the duration so you can see by default it's set to 60 minutes and if you wanted to you could also set the refresh data when opening the file option to on and again every time you open this sheet it's automatically going to fetch any changes any updates from that sharepoint list and bring them into this excel workbook now i'm just going to click out of this so i'm going to click cancel and what i'm going to do is i'm actually going to open my sharepoint list enter a new item and then i'm going to show you how that new item is going to dynamically appear in this excel sheet all right now you can see here that i'm back in my sharepoint list and i've gone ahead and i filled out the new item form and next i'm going to go ahead and click save so you can see here the new item invoice number 9999 was added here and now what i'm going to do is i'm going to bring up our excel sheet and you can see here that that invoice number 9999 is not listed here now to go ahead and refresh this connection or fetch any changes that were implemented in your list what you want to do is you want to go ahead and click on the data tab and here you want to go ahead and click refresh all and you can see here row 11 invoice number 9999 jason bourne in the amount of 99 000 that item that we just added to the list has now dynamically been added to this excel spreadsheet so that's it in this tutorial i showed you how you can establish a connection between a sharepoint online list and an excel spreadsheet i also showed you how you can open power query in excel once you've established that connection and how you can also update or set synchronization settings in the excel spreadsheet to pick up any changes that were implemented in your sharepoint online list i hope you found this tutorial helpful if you did please hit that thumbs up below and be sure to subscribe to stay up to date on the latest sharepoint online tutorials that i publish i'm louis akabelis thanks for stopping by talk soon

Channel: Lui Iacobellis

Views: 87,442

Keywords: connect sharepoint list to excel, connect sharepoint list to excel power query, connect sharepoint list to excel online, connect excel to sharepoint list, connect sharepoint list to excel 2016, connect excel and sharepoint list, sharepoint list to excel data connection, connect sharepoint list to excel spreadsheet, link sharepoint list to excel, add sharepoint list connection to excel, How to connect sharepoint list to excel, How to connect a sharepoint list to excel

Id: cz9xAaUD4Cc

Length: 12min 6sec (726 seconds)

Published: Wed Mar 16 2022