In this SharePoint tutorial, we will discuss how to create a list from excel in SharePoint Online.
This is a requirement that comes from most of the business users or site owners, can we create a list in SharePoint Online from an excel?
Previously, we can use the import spreadsheet option to create a list from an excel. But your browser requires ActiveX control. Apart from this, the import spreadsheet is not a user-friendly way to create the list.
This will work in SharePoint Online modern site.
SharePoint Online tutorial contents:
- Create a list from excel in SharePoint Online
- What will happen if you do not format excel as table
- Can I create this in the SharePoint Online classic site?
You can follow the below steps to create a list from excel in SharePoint Online.
Step-1: Format Excel as Table
The first thing we need to do is to format the excel as table.
Here, I have created an excel with the below columns:
- Employee Name
And the excel looks like below:
Once the excel is ready, Open the excel, select the records and click on “Format as table” like below:
Then it will open the Format As Table dialog box, here check the checkbox “My table has headers“.
Now, your excel table should looks like below:
Now, you can upload the excel file to the SharePoint documents (Shared Documents) document library.
But this is an optional step, you can directly also upload the excel while creating the list from the SharePoint Online site.
You can simply, drag the file to the shared documents document library in SharePoint.
Step-3 Create List from Site Contents Page
Now, we will create a list from the site contents page from the SharePoint Online site.
Open the SharePoint Site, Go to the Site Contents page. Then select +New -> List.
Here, there are 3 ways you can create list:
- New List
- Create List from an existing list
- From Excel
Here Select From Excel.
- Then provide a List name in the Create a list field.
- Then either you can upload the excel file directly from your desktop.
- Or you can select the excel from the SharePoint Online document library.
Here I have uploaded the excel file directly from my desktop.
Then it will load all the data from the excel file with the column names and the data type for each column.
Here you can change the data type from the drop-down for each column like below (last column).
If the excel file contains multiple tables, then you can select the table from the Select a table from this file dropdown list.
Then click on the Create button to create the list.
Now, you can see the list got created like below that will be having the same list name, columns and column data types.
This is how we can create list from excel in SharePoint online.
What will happen if you do not format excel as table
In the previous section we discussed, we need to format as a table the excel data. But what will happen if we do not do that?
Once you upload the excel file, then it will ask you to do the below 4 steps:
- Click on the Open button, that will open the excel file using excel online.
- Then Select the columns and data that you want to include.
- Then click on Format as Table.
- Click on Refresh once you back to this page.
All other steps will be same as explained about.
As of now you can not create a list in SharePoint Online classic site from a list. This will work in the modern SharePoint experience.
Basically, this option is available in the modern site contents page in SharePoint Online.
In this tutorial, we learned how to create list from excel in SharePoint online.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site EnjoySharePoint.com