Advertisements

Create a list based on a spreadsheet

Applies to: SharePoint Online, SharePoint Server 2016, SharePoint Server 2013 Enterprise, SharePoint Server 2013, SharePoint Server 2010, SharePoint Server 2007.

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.

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

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

  1. On your preferred site for adding a spreadsheet based list, click Settings Office 365 Settings button , and then press Add an app.
  1. In the Find an app field, type spreadsheet, and then click the search icon Search box magnifying glass icon .
find an app field with Spreadsheet typed in and search button highlighted
  1. In the search results page, click Import Spreadsheet.
Import spreadsheet app highlighted in New apps dialog
  1. In the New app page, type a Name for the list.

The name emerges at the top of the list in most views, joins the web address for the list page, and displays in site navigation to aid users finding the list. You can edit the name of a list, but the web address will remain intact.

New app dialog with name and file location filled in, import highlighted
  1. Type an optional Description. The description shows beneath the name in many views. You can alter the description for a list at any time using list settings.
  1. 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.

  1. In the Import to Windows SharePoint Services List window, choose Table Range, Range of Cells, or Named Range. If you want to choose 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 pick the lower right cell of your desired range.
Excel spreadsheet with range highlighted

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

Import to spreadsheet dialog with Import highlighted

After you import a spreadsheet, check the columns of the list to guarantee sure that the data was imported as you expected. For instance, you might want to elaborate that a column includes currency rather than a number. To see or modify list settings, access the list, click the List tab or press Settings Office 365 Settings button , and then pick List Settings.

  1. The spreadsheet data will appear in a list in SharePoint.
List in SharePoint Online
© Microsoft 2020

The column types that are designed for a list are linked to 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 grant you permission to 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 programme’s native file format isn’t supported, export your data to a comma delimited format (.CSV) and import using that file.

For more info about how to customise and add your imported list to a page or site, see .

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 on the Settings menu, select List Settings.

Top of Page

Add a site to your trusted sites zone

  • Open Internet Explorer, click Tools, and then pick Internet options.
  • Pick the Security tab, press Trusted sites, and then choose Sites.
  • The site you’re on should appear in the Add this website to the zone: field. Select Add.
  • Click Close, and then tap OK.
Advertisements

Leave a Reply

%d bloggers like this: