Navigate to Site Actions –>Site Settings. In the left navigation pane select ‘List’
Select the list from which the data need to be exported. In the ribbon navigate to ‘List’ tab under List Tools—look for Export to Excel.
This exports only the fields/columns displayed in current view. To export all the column(s) data we need to add the required columns to the current view (or) create a new custom view and then export.
To modify the current view, click on ‘Modify View’ – where all the available columns are displayed.
Import spreadsheet as SharePoint Custom list:
Go to Site Actions –> More Options –> Select Import Spreadsheet and create a new list.
While creating a new list, browse the excel which is to be imported. Clicking on ‘Import’ opens the excel sheet and prompts for select range. Select the range of cells which needs to be imported. This creates new list in SharePoint site.
*Sometimes credentials of the SP site from which the excel is exported are asked .
The above process creates a new list. But what if we need to import list items into existing list?
Moving of list items:
SharePoint allows moving of list items from one list to another list. Using this we can move the imported list items from the created custom list to an already existing list.
Navigate to : Site Actions –>Site Settings –>Content and Structure (under Site administration)
Here select the created custom list. We find all the list items that were imported. Select the list items which need to be moved and click on ‘Actions’
Clicking on ‘Move’ in Actions drop down pops up a SharePoint asset picker. Select the list to which the items have to be moved.
While importing spreadsheet, sometimes the below error message window pops-up.
*An Excel Add-In attempts to use the IOWSPostData.Post() method to publish the Excel range.
One of the solution for this issue is—
Go to C:\Program Files\Microsoft Office\Office12\1033 and locate the file “EXPTOOWS”
Open the file and navigate to VB code (Ctrl+F11)
Include “Iver=3” as shown below under “lVer=Application.sharepointversion(URL)”. (The code shown here may slightly differ according to the excel version). Save the excel and start the process of importing spreadsheet.