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

Comments For This Article


Recommended Reading

Reading SharePoint Lists with Integration Services 2017

Using a SharePoint List as a Data Source in SQL Server Reporting Services 2008 R2

Understanding SharePoint Exports to Excel

SharePoint Backup and Restore

File Handling in a SharePoint Document Library Using Python

get free sql tips
agree to terms