Synchronize a list with a spreadsheet program

You can synchronize data between a native list and a spreadsheet program, such as Microsoft Excel 2013, 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 Excel 2013.

 Note    You can also synchronize an external list with Excel 2013. Find more information about external data in the See Also section.

In this article


Introduction to synchronizing a list with a spreadsheet program

There are several ways to synchronize data between a SharePoint list and a spreadsheet program. Whichever way you choose, this synchronization is one-way, or unidirectional. Changes made in the SharePoint list can be written to the spreadsheet program, but any changes that you make in the spreadsheet program are not written to the SharePoint list.

To synchronize a SharePoint list, you must have a compatible spreadsheet program, such as Excel 2013, installed on your client computer, and you must have read permission to the list.

Top of Page Top of Page

Synchronizing native list data by using the Export to Spreadsheet command

You can synchronize the data in a SharePoint list with a compatible spreadsheet program by using the Export to Spreadsheet command in the list ribbon. As the SharePoint list continues to be updated, depending on your spreadsheet program, you can also use a command in your spreadsheet program to manually update your spreadsheet with the latest data.

For example in Excel 2013, you can refresh the data connection, which replaces and overwrites the Excel data with the current list data. If your SharePoint list contains folders, the folder structure does not appear in the resulting Excel table. However, the Item Type and Path columns are added to the Excel table so you can filter and sort the data, based on its type and location or subfolder of the list.

Top of Page Top of Page

Synchronizing native list data by using the Export Table command in Excel

From Excel 2013, you can also export an existing Excel table to a new SharePoint List by using the Export Table to SharePoint List Wizard. This approach is useful when you already have the data in an Excel table and you want to create a SharePoint list during the synchronization process.

Top of Page Top of Page

Synchronize a native list with a spreadsheet program

 Note    The following instructions use Excel 2013 as an example. If you are using a different spreadsheet program, consult the Help information for that program.

  1. Navigate to the SharePoint site that contains the list you want to synchronize with a spreadsheet program.
  2. Click the name of the SharePoint list on the Quick Launch, or click Settings, click Site Content, and then locate the list.
  3. Click List > Export to Spreadsheet.
  4. If you are prompted to confirm the operation, click OK.
  5. In the File Download dialog box, click Open.
  6. If you are prompted to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.

Excel 2013 creates an Excel Table with a data connection based on a Web query file. To see further changes made to the SharePoint list in spreadsheet program, you must manually update the data.

Top of Page Top of Page

 
 
Applies to:
SharePoint Foundation 2013, SharePoint Online Enterprise (E1), SharePoint Online Enterprise (E3 & E4), SharePoint Online Midsized Business, SharePoint Online operated by 21Vianet - Enterprise (E1 & E2), SharePoint Online operated by 21Vianet - Enterprise (E3 & E4), SharePoint Online operated by 21Vianet - Midsized Business, SharePoint Online operated by 21Vianet - Small Business, SharePoint Online Small Business, SharePoint Server 2013 Enterprise