SharePoint lists are more accessible and easier to share than simple Excel spreadsheets, but sometimes the data in one of these lists needs the more robust tools that Microsoft Office provides. The Export to Excel and Open and Access features give you access to these tools while the data stays accessible in SharePoint.

Unidirectional Exporting to Excel

Any list or library in SharePoint can be exported into Excel.  Just find the list in SharePoint and click on Export to Excel under the list’s tab.

Microsoft will ask you if you want to enable a live connection, meaning that changes in SharePoint will be updated in Excel.  This connection is unfortunately unidirectional. Changes in Excel will not be reflected in SharePoint, and in fact, if you refresh the connection in Excel, any changes you made will be overwritten.

Some options are available by clicking on Connections under the Data tab.

• Name your connection – Use something you’ll recognize
• Description – You might include some notes on how the connection was created or the permissions needed to edit it.
• Enable background refresh – Do you want the spreadsheet to update even if you already have it open.
• Refresh every – How often do you want it to refresh.
• Refresh data when opening the file – Self-explanatory
• Remove data from the external data range before saving the workbook- Check if you want to save the workbook with the query definition but without the external data.
• Refresh this connection on Refresh All – For workbooks with multiple connections you can control when each connection is refreshed.
• OLAP controls – Doesn’t apply here.

More advanced options under the Definition tab.  SharePoint sets up the connection when you export, so you shouldn’t have to make any changes here.

Exporting to Excel Libraries and Calendars

Both SharePoint libraries and SharePoint calendars can be exported into Excel or opened with Access. Connecting to a document library will not bring in the documents, but rather, just the path. Connecting to a calendar is similar.

Ideas for why you would Export to Excel with a connection

• Scenario 1 (Workflow analysis) – An HR group uses SharePoint workflows to track vacation requests, but stores the available vacation time in a spreadsheet. Connect the spreadsheet to the SharePoint list in which the requests are stored. Then use 3D references to compare the two.
• Scenario 2 (Billing scheduled service hours) – A project management group uses SharePoint calendars to schedule consultants. Export to Excel to view this time in a spreadsheet and sort and filter by consultant, customer, etc.
• Scenario 3 (Create a dashboard from a list) Export a custom list into Excel to create a PivotChart of the data. Then publish the PivotChart with Power View or Excel Services back into SharePoint.

Opening with Access- bidirectional

SharePoint lists are just another possible data source for Access, so the connection is bidirectional. Changes to data in Access will be reflected in SharePoint.

Click on the Open with Access button in the Connect & Export group of the appropriate tab (List, Calendar, Tasks, etc.).

Use the Browse button to choose and existing Access database or the location of a new Access database to which you want to connect.  Use the Link to data on the SharePoint site option if you want the connection to be live and bidirectional.

The data will be pulled in as a new table.  You can now use this table as you would any other in Access.

View the connection

View the connection under the External Data tab. Click More and SharePoint List. Choose the connection and click Next.  You’ll see that all of the elements on the SharePoint site are available under the same connection, so that several lists could be compared as different tables in the same database.

You can also Export the database into Excel, XML, or even Word.

Why you would use Open in Access

Open in Access is an extremely powerful tool. Add some creativity and patience, and you can create powerful solutions for managing and interacting with any type of data.

Scenario 1 (Create dashboard from several lists) – Use Access to open several lists at once, then export the list into Excel to create Pivot Charts. Republish the charts as dashboards into SharePoint.

Scenario 2 (Update Calendars from Access) – With the bidirectional connection, changes in Access will update the SharePoint list or calendar.  Create events in SharePoint by copying and pasting rows. Connect a scheduling software with SharePoint, by exporting new events from the scheduling software into an Access database that is connected to SharePoint.

Scenario 3 (Automatically create word documents from a SharePoint list) Use Word Merge to create a mail merge with Word for new items in a SharePoint list.