Friday, December 2, 2016

Exporting Excel tables into a SharePoint list and vice versa

If you have an Excel workbook you would like to bring it over to SharePoint, you can either just upload the Excel workbook to a SharePoint document library, or, to work with the data directly in SharePoint, you can export the table from Excel to a SharePoint list. Here are the steps:

To export your data from Excel to SharePoint, your data in the Excel worksheet must be in a table format. Select the cells you want to use to create the list in SharePoint. Then, on the Home tab in the ribbon, click Format as Table.




Next, click Export Table to SharePoint List...




In the dialog box, type or paste the site URL where you want the list to be created, and give it a name (required) and description (optional).




 Click Next.




Click the link in the dialog box to go to the list in SharePoint. You can now work with the list in SharePoint where you can add, edit and delete data.




This process can also work the other way. You can export a SharePoint list into Excel where you can build charts, reference the cells in formulas and work with the data in other ways. This way, the data stays in SharePoint and Excel is just a reporting tool. The displayed in Excel can be refreshed so it always reflects the current data stored in the SharePoint list. To export a SharePoit list into Excel, from ribbon, on the List tab, click Export to Excel. This will create an Excel query file that will open in Excel.




Click Open, which should launch Excel and import the table.




If you get a Security Notice pop-up, click Enable.




Decide how you want the data displayed and where you want the data. In my example, I'm importing as a standard table in a new worksheet.

Click OK.




You should now have the data in Excel.




You can go back to SharePoint and edit, add or delete data. Here, I've added row of new data. 




Then we can go back to Excel and click Refresh on the Table Tools > Design tab in the ribbon. 




Once the refresh is done, which should be nearly instant on a small data set like this, we can see the new row of data in Excel.




Remember that using this method, SharePoint is the data source. If you edit the data in Excel, it will not publsih back to SharePoint. Excel is just used for reporting on the data in the scenario. 

Hopefully you'll get some use out of working between Excel and SharePoint like I've shown in this blog post. 

No comments:

Post a Comment