SharePoint is a powerful collaboration platform that allows teams to share information, collaborate on projects, and manage workflows. One of the key features of SharePoint is the ability to create lists, which can be used to track information and organize data.
If you have a Microsoft Excel spreadsheet that contains data you want to track in SharePoint, you can easily create a SharePoint list from that data. This can be especially useful if you have a lot of data in Excel that needs to be shared with other members of your team or if you want to create workflows or automation based on that data. In this article, we will walk you through the process of creating a SharePoint list from an Excel spreadsheet using two different methods.
Before we dive into the steps, let's take a moment to discuss why you might want to create a SharePoint list from Excel data and what benefits this can provide.
What is SharePoint List?
A SharePoint list is a collection of data that can be stored and managed on a SharePoint site. These are similar to tables in a database and can store various types of data such as text, numbers, dates, and attachments. It is used to track tasks, projects, contacts, and other types of information and can also be customized with different columns, views, and forms to fit the specific needs of a team or organization.
A SharePoint list is useful for many reasons, such as:
Allows multiple users to view and edit data online or offline
Supports metadata and different types of columns (such as text, choice, date, currency, etc.)
Enables creating views, filters, sorts, and groups to display data in different ways
Integrates with other Microsoft 365 apps and services (such as Power Apps, Power Automate, Power BI, etc.)
Provides versioning, permissions, alerts, and workflows to control and automate data
Offers better data integrity, security, and collaboration than Excel spreadsheets
Let's begin..
Create a SharePoint List from Microsoft Excel
There are two different ways to create SharePoint List from Microsoft Excel:
Using the Import Spreadsheet app
Using Export Table List Option
Method 1: Using the Import Spreadsheet App
The Import Spreadsheet app is a web part that allows you to create a SharePoint list from an Excel spreadsheet. It works by importing the table headings as columns in the list, and the rest of the data as list items. You can choose an Excel file from your device or from the SharePoint site, and select a range of cells to import.
Consider the below steps to create a SharePoint List from Excel using the Import Spreadsheet App:
STEP 1: Go to the SharePoint site where you want to create the list and click the gear icon and select Site contents.
STEP 2: At the top of the Site contents page, Click New > List.
STEP 3: Click the "From Excel" tab.
STEP 4: Enter a name for your new list.
STEP 5: Click Upload file to select from your local device or pick from files already in Microsoft 365.
STEP 6: Update the column type headers; for example, change "Number" to Date and time; choice fields work, too, automatically aggregating unique values.
STEP 7: Click Create to import the Excel table data and create a new SharePoint list.
Method 2: Export Excel Spreadsheet to SharePoint
The Export Table to SharePoint List option is a feature that allows you to export data from an Excel table to a SharePoint list. It works by creating a new SharePoint list on the site and copying the table headings as columns and the rest of the data as list items. You can choose the address or URL of the SharePoint site where you want to create the list.
Consider the below steps to export the Excel spreadsheet to SharePoint:
STEP 1: Open Excel Spreadsheet and go to "Table Design".
STEP 2: Click Export => Export Table to SharePoint List.
STEP 3: In the Address box, type the address or the URL of the SharePoint site.
Note:
For example, if the address is http://contoso/default.aspx, type http://contoso. If the address is http://contoso/teams/accounting/sitepages/home.aspx, type http://contoso/teams/accounting.
STEP 4: Under the Name box, type the name of the list.
STEP 5: Click Next. (Enter Microsoft 365 credentials or Organizational domain credentials, if required)
STEP 6: Review the information and then click Finish.
STEP 7: Click OK.
Note: Exporting a table as a list does not create a data connection to the SharePoint list. If you update the table in Excel after exporting it, the updates will not be reflected in the SharePoint list. You can also export data from a SharePoint list to Excel by clicking Export to Excel on the List tab.
Best Practice to Manage SharePoint List from Microsoft Excel
Managing SharePoint lists from Excel can be useful for importing or exporting data, but it also requires some best practices to avoid errors or data loss. Here are some tips to follow:
Use the modern experience of SharePoint lists, as it supports better filtering, sorting, and indexing of large lists.
Add indexes to the columns that you use for filtering or sorting, as this improves the performance and avoids list view threshold errors.
Remove unnecessary sorting, grouping, totals, and columns from your list views, as this reduces the complexity and size of the SQL queries.
Use column types that are compatible with SharePoint lists, such as text, number, date, etc. Avoid using lookup, people, or managed metadata columns as they can cause errors or mismatches.
Use the Export Table to SharePoint List option in Excel to create a new list from a table, or use the Import Spreadsheet app in SharePoint to create a list from an Excel file.
If you want to update the same SharePoint list from Excel, you can use Power Automate to create a flow that deletes all the items from the list and then adds each row from the Excel table.
If you want to update the same Excel file from SharePoint, you can use the Export to Excel option in the List tab to create an Excel table with a one-way data connection based on a web query file.
Check and verify the data after importing or exporting to make sure that everything is correct and consistent.
Conclusion
Creating a SharePoint list from Microsoft Excel is a convenient and easy way to import data from a spreadsheet to a list. In this article, we have discussed two simple ways: 1) Using Spreadsheet App and 2) Using Export Excel Spreadsheet to SharePoint. You can choose whichever suits you the best. Also, follow some best practices to manage SharePoint lists from Excel, such as using indexes, removing unnecessary sorting and grouping, using compatible column types, and checking and verifying the data after importing or exporting.
I hope this article was useful!
Comments