This SharePoint 2016 tutorial, we will discuss how to use the sharepoint 2013 import spreadsheet app option to create an app in SharePoint 2013/2016.
SharePoint 2016/2013 provides an app that we can use to create a list of the columns and data presented in an existing spreadsheet. But you should make sure to follow a 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 headings 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 the Import Spreadsheet option follow the 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 the browser and click on the gear icon and then click on Add an app. Then from the list of the app select the “Import Spreadsheet” 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 an error like below:
Cannot import the spreadsheet into SharePoint. 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.
If you are getting the error in IE then we may need to enable the “SharePoint SpreadSheet Launcher” add-on in IE.
Check out a detailed SharePoint blog post: Cannot import the spreadsheet into SharePoint. This feature requires a browser that supports ActiveX controls
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 the spreadsheet itself like below:
Then it will create the list like below:
Create List from ExcelSheet in SharePoint 2010
Now we will see how to create a SharePoint list from a Excel work sheet.
First Open the SharePoint site in the browser. Then click on Site Actions -> More Options…
This will load all the templates to create list, library, page or site. From there choose Import SpreadSheet and click on Create as shown in the figure below.
This will open the page where we need to give the name, description and the path of the excel file from where we will import. My excel sheet contains 3 columns namely: Name, EmailID and Salary. And also contains some data. Fill all the details and click on Import.
This will open the Import to Windows SharePoint Services list. There in the range type you can select Range of cells, and in the Select Range you can give a range value. Here for this example we can give Sheet1!$A$1:$C$4, which means it will read from Sheet1 from A1 shell to C4 shell. Then click on Import as shown in the figure below.
This will create the list as shown in the figure below.
You may like following SharePoint tutorials:
- Show Ribbon for ListViewWebPart on Page SharePoint 2013/2016/Online
- Hide All Items link from listview in SharePoint 2013/2016/Online
- Add JSLink in Task List View in SharePoint Online
- Change default list view style using CSS in SharePoint Online/2016/2013
- Make Title field read-only in SharePoint list edit form using JSLink
- Show SharePoint list view data in accordion view using jslink
- SharePoint 2013/2016/Online: How to Load multiple js file in JSLink
- Customize list view using jslink in SharePoint 2013/2016/Online
- SharePoint 2016/Online List View Auto Indexing Automatic Index Management
- Change width of list view column in SharePoint 2013 using SharePoint designer 2013
- Show paging at top in list view web part in SharePoint 2013/2016/Online using jQuery
- Freeze Header Row in List View or Library on Scrolling using jQuery in SharePoint 2013/2016/Online
Hope in this article, you got an idea on how to use import from a spreadsheet in SharePoint 2016 and how to solve the issue this feature requires a browser that supports ActiveX controls in SharePoint 2016/2013.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.