Export an external list to Excel
Applies To
SharePoint Server 2016 SharePoint Server 2013 Enterprise SharePoint in Microsoft 365 SharePoint Foundation 2013You can synchronize data between an external list and Microsoft Excel, and keep both sets of data up-to-date. For example, you may want to maintain a product inventory in a list so that it’s easy to share and update, but also do periodic inventory analysis by using Microsoft Excel.
Note: You cannot synchronize an external list with versions prior Excel 2013. If you attempt to do so, an error dialog appears stating, “Cannot connect to the list.”
For more info, see Find content about external data.
Working with external data requires several prerequisite tasks to enable secure access to the data. The following information can help you plan your next steps. Also, if you experience problems trying to work with external data, this info can help you identify the issue. To access external data, you or an administrator must do the following:
Prepare the external data source An administrator may need to create an account and provide permissions to the data source to ensure that the right people have access to the data and that the data does not end up in the wrong hands. In an external database, the administrator may also want to create specific tables, views, queries, and so on to limit the results to just what is needed and to help improve performance.
Configure SharePoint services and accounts An administrator must activate Business Data Connectivity Services and Secure Store Service. For info about using these services, see Configure Business Connectivity Services solutions for SharePoint 2013 and Make an External List from a SQL Azure table with Business Connectivity Services and Secure Store.
Configure Secure Store Services An administrator must do the following: determine the best access mode for the external data source, create a target application, and set the credentials for the target application. For info about using these services, see Create or edit a Secure Store Target Application.
Configure Business Data Connectivity Services An administrator must ensure that the user who creates the external content type has permission to the Business Data Connectivity metadata store and that appropriate users have access to the external content type that the external list is based on. For info about using these services, see Configure Business Connectivity Services solutions for SharePoint 2013.
Create an external content type A user must define an external content type which contains information about connections, access, methods of operation, columns, filters, and other metadata used to retrieve the data from the external data source.
Create an external list A user must create an external list based on the external content type and one or more views of the external list as needed.
Make sure Office products are ready for use To synchronize external data with Office products, you must have at least Windows 7 and the following free software products on each client computer: SQL Server Compact 4.0, .NET Framework 4, and WCF Data Services 5.0 for OData V3 (If necessary, you are automatically prompted to download the software). Also, make sure the Office installation option, Business Connectivity Services is enabled (this is the default). This option installs the Business Connectivity Services Client Runtime which does the following: caches and synchronizes with external data, maps business data to external content types, displays the external item picker in Office products, and runs custom solutions inside Office products.
For more general info about using external data, see Find content about external data.
Export the external list to Excel
-
Navigate to the SharePoint site that contains the list.
-
On the Quick Launch, select the name of the SharePoint list, or select Settings
-
Select List, and then select Export to Excel.
By default, this capability is enabled on external lists, but it can be disabled by a system administrator.
-
If you are prompted to confirm the operation, select OK.
-
In the File Download dialog box, select Open.
-
If you are prompted whether to enable data connections on your computer, select Enable if you believe the connection to the data on the SharePoint site is safe to enable.
An Excel Table is created with a data connection based on a web query file. The columns in the exported table are determined by the current external list view. The external data exported is determined by the filters defined on the external content type and in the external list view, and the language settings for the SharePoint site. Any page limits are ignored.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
