How To Import and Export Data Between Excel 2010 and SharePoint 2010

SharePoint 2010 offers functionality to export data to Excel 2010 while a link is maintained to the original data. This will allow you to use data from SharePoint in such a matter that suites your business. Whenever the data changes in SharePoint 2010 you are able to refresh that data in Excel. It is also possible to import data from Excel into SharePoint 2010 very easily. It allows you to import business data into a custom list and use it in your business processes defined in SharePoint 2010. This could involve BI related processes or just other lists and views linked to the custom list.

In the following examples I will show you how to export data to and import data from Excel into SharePoint 2010. We start with exporting data to Excel. When you browse to a list in SharePoint 2010 and choose from the ribbon “List” under “List Tools” you will notice an option called “Export to Excel”.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

By clicking on the option in the ribbon, it tries to download an iqy file. This file contains the information needed for Excel to get the data from. Click “Open” to open de lin into Excel.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

The Excel application opens the link and shows the data from SharePoint in a Workbook sheet. Currently the data is linked which means that whenever data in SharePoint is changed the data can be refreshed in Excel again. This is only possible when the link is still there. As soon as “Unlink” option is clicked the link is gone and you need to do an export again. As long as the link is there you will see a square around the data which is linked to SharePoint.

Note: Data changed in Excel is NOT updated in the SharePoint list.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

Because you now have the data in Excel you are able to use all the Excel functionality like generating nice graphs based on the data.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

What if the the data is changed in SharePoint? For example we change the percentage of “Taak 4″ in SharePoint. After the percentage is changed to 90% the “Save” button is clicked.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

To reflect the change in Excel (while we are still linked) we use the “Refresh” menu command in the “Table Tools” ribbon.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

This will refresh the data and the depending graphs.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

Lets say we want to import data from Excel into SharePoint 2010. We have the following Excel containing usage data of different browsers over the months.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

Go into SharePoint 2010 and select the option “More options…” under “Site actions”.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

This will open the create dialog of SharePoint 2010 which normally allows you to create content like sites, pages and lists. Look for something called “Import spreadsheet” and press the “Create” button.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

“Import spreadsheet” will create a custom list from an Excel data file. Give the custom list a name and use the browse functionality to select the earlier mentioned xlsx file. Click on the “Import” button to start the import.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

This will open Excel and let you select the range of cells to import into the custom list of SharePoint 2010. The first row in the Excel sheet will define the column names.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

When the “Import” button is clicked the Excel sheet is closed and the new custom list is opened in SharePoint 2010. As you can see the columns are based on the first row of the selected data. The data is not linked in any way back to Excel. So you will need to export it again to get linked data. The custom list is standard SharePoint and will allow you to do anything with this list what would be possible in SharePoint 2010.

How To Import and Export Data Between Excel 2010 and SharePoint 2010

 

By |2016-11-02T17:15:25+00:00July 16th, 2012|Our 2 Cents, Our Blog, SharePoint 2010, SmackTalk|0 Comments

About the Author:

Leave A Comment

Did You Know….

GWEN-GARCIA-LEETS-IMAGEN-OFICIAL-SWN..Gwen Is the official spokeswoman of Smackwagon Design! GWEN Garcia Leets, is also sponsored by other internationally renowned companies. GWEN Currently is a host on an entertaining television program in Mexico called Marathon TeleHit

Interested in learning more about this beauty ? Click here to view GWEN's website to & keep you always informed of her most recent projects and events

CALL US TODAY!

Sharing is Awesome, Do It!

Share this web design company with your friends
close-link