Are you looking for a way to import your Excel table or spreadsheet to a custom list on your SharePoint Online site?
Fortunately, the modern experience makes this process easy. Before, you will have to choose a method with the cons you can live with.
In this article, I’ll show you how to easily import your Excel table or spreadsheet to a custom list on your SharePoint site.
Let’s get started.
Table of Contents:
What you need to know about the import process
There are some things you need to know first. Yes, the process is easy, but it’s important that you understand the things mentioned below before you start importing.
Take note of the following:
- Before you can import an Excel list or spreadsheet to SharePoint, you need to format your data in the spreadsheet as a table. In case you don’t know how to do this, I’ll show you what to do.
- If you import the Excel file from your computer, it gets saved in the site assets library. On the other hand, if it already exists in your drive, it will get saved in the default document library of the site.
- The Excel file doesn’t have any association with the custom list created from importing it. You can’t change any data on the custom list from the Excel file and vice versa.
- During the process, you will be able to specify the column type of each column. Although you can ignore the others, you must specify the “Title” column as it will be the main item of the row.
- It’s also important that the Excel table doesn’t have any empty rows, especially the “Title” column. Otherwise, you will get an error message about specifying the value being required.
- The column types you can specify for each column are limited. You will not be able to select people, location, and hyperlink column types.
- There is a choice column type. Unfortunately, it doesn’t work as expected. The field will not show the choices found on the Excel table — it will only import what’s already chosen for the cell.
- You will not be able to use the columns created from the process on other lists on your site since they are created at the site library level only.
- After creating the list, you will not be able to re-import the Excel table or spreadsheet as a means to update the list. The import process is a one-time operation only. For updates, your only choice is to do it the old fashion way — copy and paste.
- Currently, the maximum number of rows you can import from an Excel table is only 20,000. If the system detects more rows than the maximum, you will get an error about the table exceeding the maximum number of supported rows.
With that, let’s proceed with formatting your Excel spreadsheet as a table.
How to format your Excel list as a table
Let’s say you want to import an Excel list about insurance policies similar to the one shown below:
Now, as stated in the previous section, you need to format your Excel list as a table.
The reason is that SharePoint can only detect tables in the Excel file to import. You will get the following if the list isn’t formatted as a table:
Now back to the list:
So what you need to do now is select the cells with data first.
Then, click on the “Format as Table” button on the ribbon (under the “Home” tab). Select whichever table style you prefer.
A small window will appear where you can also input the data for the table. Click on the “OK” button to proceed.
After that, your table should look like this (of course, with the table style that you chose).
With that, we can now proceed with the actual process of importing the Excel table to your SharePoint site.
How to create (import) a list from an Excel table
Navigate to the target site where you want to import the Excel table file.
Now, click on the “New” button on the command bar and select “List” from the options.
By the way, I wrote an article before about SharePoint lists and how to create one. Read it if you want to learn more about SharePoint lists.
Then, click on the “From Excel” button.
You can then upload the file or choose from the document library.
After doing so, you can then check the column types. All the columns aside from the “Title” are set to the “Single line of text” type, which you can change.
Once you’re done, click on the “Next” button to proceed.
In the next window, you can name the custom list and give it some description (optional). You can also choose to show the list in site navigation.
Depending on the list size, it might take a while for the system to finish creating the list.
Once done, it will redirect you to the custom list right away.
Creating a custom list from an Excel file
As you can see, importing an Excel list to SharePoint Online is easy — just make sure the data is formatted as a table then import it to SharePoint.
You can then use the custom list as you wish. Remember though that the custom list and the Excel file are not interlinked.
You may also do some changes in the settings like making sure the column types are correct, enable version history, and maybe disable the attachments.
Do you have some questions? If so, drop them down in the comment section below so we can talk about it.
For inquiries and concerns, contact me directly through the contact page and I’ll get back to you as soon as possible.