Legacy SharePoint versions have provided the capability to create a custom list using excel file. This feature is enhanced a bit in SharePoint Online Modern interface and helps importing your excel data into a custom list with all required columns in a matter of few minutes.
You can choose the excel file either from your site or upload from your computer/laptop.
Steps to create custom list from excel spreadsheet
Make sure you define a table in excel which has the data, including headers/columns. You need to define tables to import as a list.
- Navigate to you SharePoint site (modern interface).
- Go to Settings --> Site Contents.
- Click New --> List (you can also do this from the modern home page).
- Click From Excel.
- List name - Enter a name for your new list.
- Show in site navigation - Yes/No.
- Choose file location
- 'Choose a file already on this site' - if the excel spreadsheet is present in a Documents library
- Upload file - to upload from device. The Excel file will be uploaded to the Site Assets library. If the Upload file button is greyed out, you don't have permission to create a list from a spreadsheet.
- Next, you will be asked to 'Select a table form this file' - there could be multiple tables, select the one you want to use.
- If there are no tables defined in your excel, you will see a message 'You don't have any tables defined in this file. You need to define tables to import as a list.'.
Follow the on screen instructions to define a table and select required cells- Open the excel document
- Select the cells to include
- Select the data, and click 'Format as Table'
- After saving, return here and click 'Refresh'
- Check the column types below and choose a new type if the current selection is incorrect - Inspect and select column type for each of your header/column, leave default or select do not import.
- Click Create and your list is ready.
Few important points to consider
- Format and define Tables in the Excel, excel without tables cannot be imported.
- Restrict excel rows to 20000. If there are more entries, you will receive a message 'This table exceeds the maximum number of supported rows'.
- Excel should not have empty rows.
- Use Quick Edit to add more entries to the list, that's the most convenient.
- Title column is always mandatory. Choose your Title column wisely.
- Column chosen as Title field cannot have blank values. If there are blank values, you will receive a message 'An error occurred while creating your items. Title: You must specify a value for this required field'.
- You need to manually add values to choice column.
- Once rows imported to SharePoint list, you can work directly with List settings like versioning, views, filters, workflows, etc.
- 32-bit browser like Microsoft Edgeis recommended as this feature relies on ActiveX filtering. But it will work with other browsers like Microsoft Internet Explorer, Google Chrome and Mozilla Firefox as well.
There are other options like copying excel data in Quick Edit mode or even import directly from Excel, but the "Import Spreadsheet" is the most convenient and quick one.
More Posts related to SharePoint,
- SharePoint error - An exception occurred when trying to issue security token: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..
- PowerShell iterate and get all SharePoint sites and sub sites
- SharePoint Online: Editing session has ended Message
- How to Share Microsoft SharePoint Site with Users or Groups
- How to delete SharePoint Online List Item using REST API
- SharePoint List excel import - This table exceeds the maximum number of supported rows
- How to disable SharePoint subsite creation option for owners
- SharePoint 2010 August 2015 Update KB3055049 - Duplicate Document ID issue bug fixed
- SharePoint Server 2016 Preview installation error - This Product Key isn't a valid Microsoft Office 2016 Product Key. Check that you've entered it correctly.
- How to upload file programmatically to SharePoint Document Library using Server Object Model C# .Net
- Access URL for SharePoint Tenant Admin Center (Online Office 365)
- Error when deleting SharePoint Online folder or file
- How to extend retiring SharePoint 2010 Workflows and continue with Office 365
- How to generate client id and secret to register SharePoint App with OAuth
- How to delete SharePoint List Item programmatically using C#.Net
- How to retrieve all SharePoint Online List Items using Rest API
- [Solved] SharePoint Search Internal server error exception
- Create SharePoint Site Collection using PowerShell New-SPSite
- Fix SharePoint PowerShell error - The term Get-SPweb is not recognized as the name of a cmdlet function script file or operable program
- How to enable anonymous public access for SharePoint Online site collection, file, folder without login ?
- SharePoint Server 2016 installation System Hardware requirements
- How to create SharePoint Online List Item using REST API
- Changed AD user display name showing old name in SharePoint
- Recommended size and resolution for SharePoint Online Site logo
- Microsoft 365: How to Turn Off Delve in SharePoint Online for All Users
More Posts:
- Create S3 bucket using AWS CLI Command mb - AWS
- How to resolve Certificate Expired WhatsApp Error - WhatsApp
- [fix] Java Spring Boot JPA SQLSyntaxErrorException: Encountered user at line 1 column 14 - Java
- [Fix] java: incompatible types: incompatible parameter types in lambda expression error - Java
- How to Update Device Drivers on Windows 10/11 Manually - Windows
- Location of eclipse.ini file on Mac OS X - Mac-OS-X
- hibernate.cfg.xml Configuration and Mapping xml Example - Java
- How to delete SNS Topic using AWS CLI - AWS