Export Excel Data To SharePoint Custom List

Prerequisite

1. SharePoint 2013 or SharePoint Online environment
2. Excel 2013 or later

Let's assume a scenario where you have an Excel file in which you have maintained all your information in tabular format. Now, move your site to SharePoint 2013/SharePoint Online. Consider the following is your Excel file data.

Now, your requirement is to export this data to SharePoint 2013/SharePoint Online. 

Follow these steps -

The very first step is to convert your Excel data to tabular format. In order to do this, just select the data => click Insert Menu => choose Table. When you do this, you will see the below dialog box.



Note

Here, “My table has headers” is selected by default. If you click OK with this option, then the header in the table will be converted as SharePoint Custom list column header. If you uncheck this and click ok then this will be treated as a record and SharePoint Custom list title will be auto created as “column1, column2 etc” So in my case I am keeping this checked.

Once you click ok your data will be converted into tabular format. And Design menu will be available in the Ribbon.



Note

If your data is already in tabular format, then please skip this step and move to another step.

Once your data is converted to tabular format, then just go to Design menu from the ribbon,  click Export, and select Export Table to SharePoint List, as shown in figure.



Once you click this, you will get the following dialog box. Here, you have to specify the URL of the site, site collection where you want to create the custom list. Once you enter the SharePoint site URL, then it will ask you for credential to authenticate the SharePoint URL. Just enter your credentials. Also, you need to provide the List name. Your custom list will be created with this name and description. Description is optional here.

Once authentication is done, just click on Next. After this, you will see the list information which will be created.



Here, you can see that the column types are auto selected, based on the data your column has. Just click on Finish to proceed. Once list is created successfully, it will give you the following message.

Now, your list has been created. Just log into your SharePoint Site and check whether the list exists or not. You will find your new custom list created with 3 items.



When you open this list, you will find the column headers and data are the same as you mentioned in Excel. Also, if you check the column type in List Setting.


List Setting Page


So, you have finally exported your Excel data to SharePoint custom list.