Automatically create SharePoint custom list populated with Excel data - Part 1

By:   |   Updated: 2011-02-17   |   Comments   |   Related: > Sharepoint


It is easy to export SharePoint lists to an Excel spreadsheet, but how about the reverse? If we already have data in Excel format with predefined columns, headers, formulas, etc... how can we make those available in a SharePoint list?


Via Microsoft Office Excel 2007 we can create a table and then export it to a new SharePoint list. SharePoint provides OOTB (Out Of The Box) functionality to import Excel spreadsheets and to save it as a custom list. There are multiple techniques for getting this job done. Parts I and II of this tip will explore three different approaches.

  • Export an Excel spreadsheet table to a SharePoint custom list (Default text data type for table - Columns headers)  - Part I.
  • Export an Excel spreadsheet table to a SharePoint custom list (Multiple data type for table - Columns headers) - Part II.
  • Import an Excel spreadsheet table to a SharePoint custom list (SharePoint OOTB Custom Lists feature) - Part II.

1. Export an Excel spreadsheet table to a SharePoint custom list : (Default text data type for table - Columns headers)

- Open Microsoft Excel 2007 , Select (Highlight) a range of cells then click on the "Table" button under Insert tab:


- Make sure that the displayed range of cells match what you have selected (highlighted) then click on Ok button :

page layout

- Change the columns headers and enter some data, then Click on "Export" : "Export Table to SharePoint List..." under Design tab:


- Then, type your SharePoint site URL at the "Address" section , Provide a name and description for your exported table and click Next button:

sharepoint list

- Make sure that all of your selected (highlighted) column headers are displayed, then click the Finish button:

export table

- Wait until you get a message box telling you that "The table was successfully published and may be viewed on" :

windows sharepoint services

- After clicking on the link (shown above) we'll see our table that was exported to a SharePoint custom list in datasheet view:

sharepoint tips

- Then, we can return to the default standard view by clicking on "Show In Standard View" button under "Actions" menu items :

show in standard view

- And finally here's our new automatically created SharePoint custom list imported with our Excel table data:


In the next part of this article, we will see the exciting technique of doing the Excel export again, while preserving data types in the columns.

Next Steps

Last Updated: 2011-02-17

get scripts

next tip button

About the author

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Recommended Reading

Developer Dashboard in SharePoint 2010

Taking Control of Checked Out Documents in SharePoint

Understanding SharePoint Exports to Excel

Recover a deleted SharePoint site or site collection with the *New* Site Recycle Bin

Learn to Query the SharePoint List Data Service with LinqPad

get free sql tips
agree to terms

Learn more about SQL Server tools