Synchronize a SharePoint list with a spreadsheet program

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

Synchronize a SharePoint list with a spreadsheet program

NOTE    The following instructions use Excel 2010 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 Site Actions, click View All Site Content, and then in the appropriate list section, click the name of the list.

NOTE    A SharePoint site can be significantly modified in appearance and navigation. If you cannot locate an option, such as a command, button, or link, contact your administrator.

3. On the ribbon, click the List tab, and then in the Connect & Export group, click 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 whether 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 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. For example in Excel 2010, in the Excel table connected to a SharePoint list, on the Design tab, in the External Table Data group, click Refresh.

 

Introduction to synchronizing a SharePoint list with a spreadsheet program

You can synchronize data between a SharePoint list and a compatible spreadsheet program, and keep both sets of data up-to-date. For example you may want to maintain a product inventory in a SharePoint list so it’s easy to share and update, but also do periodic inventory analysis by using a spreadsheet program, such as Excel 2010.

There are three 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 updated in 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 2010, installed on your client computer, and you must have read permission to the list.

NOTE    You cannot synchronize a SharePoint external list with a spreadsheet program.

 

Synchronizing 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 2010, 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.

Synchronizing list data by using the Query List with Excel command in Datasheet view

If you have Excel 2010 installed on your client computer, you can also synchronize a SharePoint list from Datasheet view by using the Query List with Excel command available in the Datasheet view task pane. Using additional commands in the task pane, you can also synchronize the list data and do the following:

• Create an Excel chart
• Create an Excel PivotTable
• Print the list from Excel

Synchronizing list data by using the Export Table command in Excel

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

 

Tags van Technorati: Excel 2010,Spreadsheet,Lists,SharePoint 2010

Create a List based on a Spreadsheet in SharePoint 2010

 

Before you import, prepare your data

• The top row of your spreadsheet should consist of labels or headers. Each label should describe the contents of the data beneath it.
• Make sure your information is properly separated so that later it can be sorted easily. For example, you may have a column called “Name” that contains the first and last name of a customer. However, you might want to separate that column into two columns, one for “First name” and one for “Last name.”
• A list should not contain any blank rows, blank columns, or empty cells.

How to convert your data in Microsoft Excel into a table

1. Click inside the range of data.
2. On the Home tab, click Format as Table, and then choose any style.
3. Verify that the correct range of cells is selected.
4. Make sure the My table has headers check box is selected.
5. Click OK.

How to import an Excel spreadsheet

1. Close Excel.
2. On the Site Actions menu, click More Options.
3. On the left, click List.
4. Select Import Spreadsheet.
5. Click Create.
6. Name the list and enter a description.
7. Click Browse and then select the spreadsheet you want to import.
8. Click Open.
9. Click Import.
10. If the Excel icon is blinking on the taskbar at the bottom of your screen, click the icon, and then click the file name of the file you want to import.
11. For Select Range, click the table range.
12. Click Import.

 

 

Create a list from a spreadsheet in Excel 2010

1. Open your spreadsheet in Excel 2010.
2. Select your data. On the Home tab, click Format as Table and then choose a table style you like.
3. If your data has column headers, click to select My table has headers, and then click OK.
4. On the Design tab, click Export, and then click Export Table to SharePoint List.
5. For Address, type the URL for the site.
6. For Name, type the name you want for the list itself.
7. For Description, type a short description.
8. Click Next. And then click Finish.

Tags van Technorati: Lists,Spreadsheet,SharePoint 2010,Microsoft Excel