SharePoint provides an app which we can use to create a list with the columns and data presented in an existing spreadsheet. But you should make sure to follow few guidelines for the spreadsheet.
- Make sure your spreadsheet looks like a table with no blank columns or rows.
- Make sure your spreadsheet has headers. All columns in the SharePoint app need to have a column title.
- Make sure your data is consistent. For example, if a cell has a comment in it, but it should contain a date, remove the comment text.
- Make sure your column heading in the first row is representative of the data. SharePoint reads the headings in the first row and makes assumptions about the information in those columns.
Create an App using Import Spreadsheet Option:
To create an app using Import Spreadsheet option follow below steps.
First let us create an excel which will have 3 columns like:
- FirstName (Single line text)
- LastName (Single line text)
- DateOfJoining (Date Type Field)
The excel sheet looks like below:
Now open your SharePoint 2016 site in browser and click on the gear icon and then click on Add an app. Then from the list of app select “Import Spreedsheet” option. Then give a name, description and choose the spreadsheet file which we have created before as shown in the fig below:
If in the next step you get error like below:
This feature requires a browser that supports ActiveX controls like below, then you make sure you are trying in IE browser because this features requires ActiveX.
Once you will click on OK to create the list, first it will display the Import to Windows SharePoint Services list dialog box like below.
Here Select Range type as Range of Cells and then select the range, you can directly select the whole range in spreedsheet itself like below:
Then it will create the list like below:
Hope this will be helpful.