Synchronize a SharePoint list with a Excel

You can synchronize data between a SharePoint Online and 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 Excel.

Introduction to synchronizing a list with Excel

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

For more information on creating and editing lists in SharePoint, see these topics:

Note:  The following instructions use Excel 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 Excel.

  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 the List tab on the ribbon, and then click Export to Excel.

    SharePoint Export to Excel button on ribbon highlighted
  4. If you are prompted to confirm the operation, click OK.

  5. In the File Download dialog box in your browser, 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.

  7. In the Import Data dialog box, select the How you want to view this data and Where do you want to put the data options.

    Import data dialog box from Excel 2016
  8. Optionally, you can click Properties and set the connection properties.

  9. Click OK when you're done. The list should appear in Excel.

    Excel spreadsheet with imported list and Refresh All button highlighted.

Excel creates an Excel Table with a data connection based on a web query file. Changes are not made automatically. To see further changes made to the SharePoint list in Excel, you must manually update by clicking Refresh All on the Data tab.

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.

From Excel, you can 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.

Note: You need to have permissions to export tables to SharePoint. If this doesn't work, contact your SharePoint administrator or manager.

  1. In the Excel worksheet, click anywhere in the table that you want to export. This displays the Table Tools, adding the Design tab.

  2. On the Design tab, in the External Table Data group, click Export, and then click Export Table to SharePoint List.

    Export to SharePoint link in Excel hightlighted
  3. In the first export dialog box, in the Address box, type the URL of the SharePoint site that you want to export the table data to.

    Notes:  If you have already exported table data to this SharePoint site, the address may appear in the drop-down list, so you can select the address instead.

    Check with your administrator for a URL if you don't have one.

    Export to SharePoint dialog box page 1
  4. Type the Name that you want to use for the SharePoint list, and optionally type a description.

  5. Click Next.

  6. Review the list of columns and data types in the second dialog, and then do one of the following:

    • If the columns and data types that you are exporting are correct, click Finish

    • If the columns and data types are not correct for the SharePoint list, click Cancel, and then verify that the column cells in Excel can be converted into a data type that can be supported in the SharePoint list.

      Export to SharePoint dialog second page.

      Note:  You may need to convert complex table data on the Excel worksheet to a simpler format, such as plain text or a number. Data types that are supported are text (single or multiple lines), currency, date and time, numbers, and hyperlinks.

    • Click OK to confirm the table was successfully published.

      Note: You can click the link to see your list on the SharePoint site. You can also view your list later by clicking Open in Browser in the External Table Data group on the Design tab.

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×