“In our organization, we deal with lots of customer data and analyze their behavior patterns to suggest them the suitable products for their businesses. But after analyzing the data, we want to store them in the SharePoint lists as we need to share them with other departments or analyze them again. The data is present in the large-sized excel sheets. How can we accommodate these sheets in the SharePoint? We don’t want to disrupt the arrangement of the columns as they are important for the analysis and results.”
The above statement is of my friend, who is a Data analyst and work on Excel sheets consistently. But he is not aware of the SharePoint environment and how does it support the import of Excel Sheets to SharePoint. So, in the current article, we will go through the various methods which allow you to import MS Excel to the SharePoint list.
# Method 1. Quick Edit on the List
In the quick edit option in the list of a SharePoint, you can input the data from the Excel spreadsheet directly to the list you have created earlier. You need to create the columns similar to the columns of the spreadsheet.
Log in to your SharePoint site and create a list (or select an existing list). You need to create columns in the same manner as in your spreadsheet. Because you will copy the whole rows to the list, and any mismatched data will create a problem for you.
You can click the ‘Add Column’ and choose among the option to create a suitable column.
After creating the column, click the ‘Quick edit’ option.
The list columns are open to receive the data. Copy the row from the excel and paste it in the list row.
The simple copy-paste process will bring the whole data to the SharePoint list.
It is a time taking process where you need to copy-&-paste whole rows from your spreadsheets. If the size of the spreadsheet is large and the number of rows is higher, then the process may take a lot of time.
If you copy any row incorrectly, then it will be harder to find out the error.
# Method 2. Using the Import Spreadsheet Option
There is another method that involves using the ‘Import spreadsheet’ web part option where you can directly import the Excel sheet to the site. Here is the process;
Click the Settings gear option on the site page.
Select the option ‘Add an App.’
Choose ‘Import Spreadsheet’ in the App list.
Provide a name to the spreadsheet, a small description, and the File location. Browse the file from its location and click Import.
The Excel file will open up where you need to select the range of cells that you want to import. Select them and click Import.
The table will be copied to the SharePoint site list. The table will have proper columns and rows as it was saved in the Excel.
The process works only at the Internet Explorer, and if you are using any other web browser, then it will give an error message that ‘This feature requires a browser that supports ActiveX controls.’ So, either you perform this method only at Internet Explorer.
If there is a drop-down list, then its entire content will be copied as the text. So, the method may disrupt the arrangement of your data.
# Method 3. Direct Export from MS Excel
In the third method, you can directly export the table to SharePoint list without any external assistance.
Open the MS Excel sheet, which you want to see at the SharePoint list, then choose the option ‘Format it as a table.’
While the table is highlighted, go to the Export option and select the ‘Export table to SharePoint list.’
In the Address field, input the link of the site of the SharePoint list. Provide a Name and description to the sheet and click Next.
The next screen will present all the columns and their respective data types. Click Finish.
A message will appear that showing that the table was successfully published and the link where you can view the table at the SharePoint list.
The table is presented in Datasheet view. So, to see a normal list, you will need to click the Stop Editing option. Also, note that a classical list is created rather than a modern one. And if you had a drop-down list, it will be saved as text only.
All the above three methods present simple processes to export an Excel sheet to the SharePoint list. Though it is easy to export an Excel sheet to a SharePoint list, this is not the case with other data on file systems. When there is a large amount of data, you need a professional SharePoint Migration tool. If you want a secure migration, then you should use Kernel Migrator for SharePoint tool. It not only migrates the data but also protects it from any change or editing. Multiple filtering options allow you to include the important data and leave the rest. After completing the migration, you will also get a migration report.