Create a list based on a spreadsheet

You can save time when you create a SharePoint list by importing an existing spreadsheet file. When you create a list from a spreadsheet, its headings become columns in the list, and the rest of the data is imported as list items. Importing a spreadsheet is also a way to create a list without the default Title column.

Important: 

• Use a 32-bit version of Internet Explorer 10 or 11 to import a spreadsheet. Importing a spreadsheet relies on ActiveX. Once the list is imported, you can then work with the list in any SharePoint supported browser.

• Another method of moving data into SharePoint is to export a table directly from Excel. For more info, see Export an Excel table to SharePoint.

• For more info on SharePoint supported browsers, see Plan browser support in SharePoint Server

Important: If you get an error message stating that a valid spreadsheet you're importing isn't valid or contains no data, add the SharePoint site you're on to the trusted sites list on the Security tab in Internet Explorer Internet Options.

Updated April 25, 2017, thanks to customer feedback.

Online, 2016, 2013 2010, 2007



Create a list based on a spreadsheet in SharePoint Online, 2016, and 2013

1. On the site where you want to add a spreadsheet based list, go to Settings and then click Add an app.

2. In the Find an app field, type spreadsheet and then click the search icon .

   

3. In the search results page, click Import Spreadsheet.

   

4. In the New app page, type a Name for the list.

   The name appears at the top of the list in most views, becomes part of the web address for the list page, and appears in site navigation to help users find the list. You can change the name of a list, but the web address will remain the same.

   

5. Type an optional Description.

   The description appears underneath the name in most views. You can change the description for a list at any time using list settings.

6. Browse to or type the File location of the spreadsheet. Click Import when done.

   The spreadsheet will open in Excel, and you'll see the Import to Windows SharePoint Services List window.

7. In the Import to Windows SharePoint Services List window, choose Table Range, Range of Cells, or Named Range. If you want to select a range manually, choose Range of Cells, and then click Select Range. In the spreadsheet, click the upper left cell, hold down the Shift key, and select the lower right cell of the range you want.

   

   The range will appear in the Select Range field. Click Import.

   

   After you import a spreadsheet, check the columns of the list to make sure that the data was imported as you expected. For example, you may want to specify that a column contains currency instead of a number. To view or change list settings, open the list, click the List tab or click Settings , and then click List Settings.

8. The spreadsheet data will appear in a list in SharePoint.

   

Create a list based on a spreadsheet in SharePoint 2010 or SharePoint 2007

1. Click Site Actions , click View All Site Content, and then click Create .

   Note:  A SharePoint site can be significantly modified. If you cannot locate an option, such as a command, button, or link, contact your administrator.

2. In SharePoint 2010, under All Categories, click Blanks & Custom, click Import Spreadsheet, and then click Create.

   In SharePoint 2007, under Custom Lists, click Import Spreadsheet, and then click Create.

   

3. Type the Name for the list. Name is required.

   The name appears at the top of the list in most views, becomes part of the web address for the list page and appears in site navigation to help users find the list. You can change the name of a list at any time, but the web address will remain the same.

4. Type the Description for the list. Description is optional.

   The description appears underneath the name in most views. You can change the description for a list.

   

5. Browse or type the File Location of the spreadsheet that you want to import and then click Import.

6. In the Import to Windows SharePoint Services list dialog, select the Range Type, in Select Range specify the range in the spreadsheet that you want to use to create your list.

   

   Note:  Depending on your spreadsheet program, you may be able to select the range of cells that you want directly in the spreadsheet. A table range and a named range must already be defined in the spreadsheet to be able to select it in the Import to Windows SharePoint Services list dialog.

7. Click Import.

After you import a spreadsheet, check the columns of the list to make sure that the data was imported as you expected. For example, you may want to specify that a column contains currency instead of a number. To view or change list settings, open the list, click the List tab or click Settings, and then click List Settings.

The types of columns that are created for a list are based on the kinds of data that are in the columns of the spreadsheet. For example, a column in the spreadsheet that contains dates will typically be a date column in the SharePoint list. The following image is the SharePoint list that was created by importing the spreadsheet in the previous image.

All versions of SharePoint let you import a spreadsheet of data, though how you do it varies slightly between the versions. Examples here use Excel, but another compatible spreadsheet would work. If your spreadsheet program's native file format isn't supported, export your data to a comma delimited format (.CSV) and import using that file.

For links to topics on how to customize and add your imported list to a page or site see, Introduction to lists.

Note: Typically, the columns are set up on the SharePoint site based on the type of data that they contain. After you import a list, however, you should inspect the columns and data to make sure that everything was imported as you expected. For example, you may want to specify that a column contains currency rather than just a number. To view or change the list settings, open the list, and then click List Settings on the Settings menu.

Top of Page

Add a site to your trusted sites zone

• Open Internet Explorer, click Tools, and then click Internet options.

• Select the Security tab, click Trusted sites, and then click Sites.

• The site you're on should appear in the Add this website to the zone: field, click Add.

• Click Close, and then click OK.

Leave us a comment

Was this article helpful? If so, please let us know at the bottom of this page. If it wasn't helpful, let us know what was confusing or missing. Please include your version of SharePoint, OS, and browser. We'll use your feedback to double-check the facts, add info, and update this article.