Create a list based on a spreadsheet
Applies To
SharePoint Server Subscription Edition SharePoint Server 2019 SharePoint Server 2016 SharePoint in Microsoft 365 Microsoft ListsWhen creating a Microsoft list, you can save time by importing an existing Excel spreadsheet. This method converts the table headings to 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.
Another way to move data into SharePoint is to export a table from Excel. Learn more about SharePoint and find details about supported browsers.
-
For Modern SharePoint, experience, see Which browsers work with Microsoft 365 for web and Microsoft 365 Add-ins.
-
For SharePoint subscription edition, see Plan browser support in SharePoint Server Subscription Edition.
-
For SharePoint server 2016 and 2019, see Plan browser support in SharePoint Servers 2016 and 2019.
Important: Creating a list from an Excel spreadsheet is not available in the GCC High and DoD environments.
Create a list based on an Excel (.xlsx) file
-
There are different ways to start a list from an Excel file. You can go to the Lists app in Microsoft 365, SharePoint, or Teams.
-
Select From Excel.
-
Select Upload file to transfer a file from your device or Choose a file from your One Drive,
When you upload an Excel file from your device, it will be saved to the Site Assets library of your site. This means other people can see the original Excel data.
Note: If the Upload file button is greyed out, you don't have permission to create a list from a spreadsheet. For more information, see your organization’s site admin.
-
Select a table from this file and choose what details you want to see in your list. Then, select Next.
-
Customize your list:
-
Add a title: Give your list a title in the name field.
-
Type a description.Add info about your list in the description field.
-
Add color.Choose a color for your list.
-
Add an icon.Choose an icon for your list.
-
Save to: Select a location to save your list.
-
-
Select Create.
Notes:
-
If the spreadsheet you are importing doesn’t have table, follow the prompts on the screen to create a table in Excel, and then import it into your list. If you are having trouble creating a table, go to the Home tab on the Excel ribbon and look for Format as Table.
-
You can use tables with up to 20,000 rows to create a list.
-
Create a list based on a comma-separated value (.csv) file
-
There are different ways to start a list from an Excel file. You can go to the Lists app in Microsoft 365, SharePoint, or Teams.
-
Select From CSV.
-
Select Upload file to transfer a file from your device or Choose a file from your One Drive.
If you upload from your device, the .csv file will be added to the Site Assets library of your site, which means other people will have access to the original .csv file data.
Note: If the Upload file button is greyed out, you don't have permission to create a list from a spreadsheet. For more information, see your organization's site admin.
-
Choose what details you want to see in your list and then select Next.
-
Customize your list:
-
Add a title: Give your list a title in the name field.
-
Type a description.Add info about your list in the description field.
-
Add color.Choose a color for your list.
-
Add an icon.Choose an icon for your list.
-
Save to: Select a location to save your list.
-
-
Select Create.
Create a list based on a spreadsheet in SharePoint 2016
Note: When you're using a site template, it is no longer possible within SharePoint to create a list from an Excel workbook. However, you can still achieve the same thing by exporting data to SharePoint from Excel, as described in Export an Excel table to SharePoint.
-
On the site where you want to add a spreadsheet based list, select Settings , and then select Add an app.
-
In the search box, enter spreadsheet or go to Apps you can add > classic experience.
-
In the search box, enter Import Spreadsheet and select it.
-
Customize your list:
-
Add a title: Give your list a title in the name field. The name appears at the top of the list in most views and becomes part of the web address for the list page. It 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.
-
Type a description.Add info about your list in the description field.
-
Browse for a location. Look for a location to save your list.
After you customize your list, select Import.
-
-
The spreadsheet opens in Excel, and the Import to Windows SharePoint Services List window appears.
-
In the Import to Windows SharePoint Services List window, select Table Range, Range of Cells, or Named Range. If you want to select a range manually, select Range of Cells, and then Select Range. In the spreadsheet, select the upper left cell, hold down the Shift key, and select the lower right cell of the range you want.
The range appears in the Select Range field. Select 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, select the List tab or select Settings, and then select List Settings.
-
The spreadsheet data appears in a list in SharePoint.
Important: Be sure to use a 32-bit web browser, such as Microsoft Edge, to import a spreadsheet, as importing a spreadsheet relies on ActiveX filtering. Once you import the spreadsheet, then you can work with the list in any SharePoint-supported browser.
The columns in a list are determined by the type of information in the columns of the spreadsheet. For example, if there’s a column in the spreadsheet with dates, it will usually become a date column in the SharePoint list.
You can import data from a spreadsheet into all versions of SharePoint. The steps to do this are a little different for each version. The examples provided feature Excel, but any compatible spreadsheet software will 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 more info about 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 on the Settings menu, select List Settings.
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.