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

By: Hesham Saad   |   Updated: 2011-02-17   |   Comments   |   Related: > SharePoint

Problem

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?

Solution

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 :

- 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:

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

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

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

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

- 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

• Check for Part II of this article.
• Import data from a SharePoint list.
• Fill data automatically in worksheet cells.

About the author

Hesham Saad

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips