How to Import Excel Table to SharePoint Online List

How to Import Excel Table to SharePoint Online List

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.

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.

Sign up for exclusive updates, tips, and strategies

    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:

    Sample Excel list containing data insurance policies

    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:

    SharePoint will let you know you don't have a defined table in the file

    Now back to the list:

    So what you need to do now is select the cells with data first.

    Select all the data you want to format as table

    Then, click on the “Format as Table” button on the ribbon (under the “Home” tab). Select whichever table style you prefer.

    Click on the format as table button and select a table style

    A small window will appear where you can also input the data for the table. Click on the “OK” button to proceed.

    Click on the Ok button to proceed with the table creation

    After that, your table should look like this (of course, with the table style that you chose).

    Excel list formatted as a table

    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.

    How to add a new list in SharePoint

    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.

    Select "From Excel" option

    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.

    Click on the next button after specifying the column types

    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.

    Give the list a name and click on the create button

    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.

    SharePoint finished importing the sample data insurance policy list

    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.

    About Ryan Clark

    As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage Modern Workplace and Digital Process Automation investments. I am also a Microsoft Most Valued Professional (MVP) for Office Apps & Services.

    Subscribe
    Notify of
    guest
    0 Comments
    Inline Feedbacks
    View all comments
    0
    Would love your thoughts, please comment.x
    ()
    x
    Scroll to Top
    Tweet
    Share
    Share
    Pin