SharePoint List Using Microsoft Excel

Scenario

 

At times, we get some raw data into Excel and next we have to create a List using details shared in Excel. It becomes a very time consuming activity, creating each and every column and then uploading data using quick edit view. Many times we face the challenges of validation while doing so. Recently Microsoft launched the feature "Ability to create List using Excel".

 

Wow, that's a really cool feature to have! So I decided to walk you through the simple steps required to create a list using Microsoft Excel.

 

Step 1

 

Prepare the Excel file from which the  list will be created. Data must be into Table format. If it is not, then select the data range in Excel and insert the table like the one below.

 

Step 2

 

Click to add a new app and select list to be added. We get a new interface to create the list. Now when you do so, you will see a new option added "From Excel" as show in the below screen.

 

 

Step 3

 

Select the highlighted Option. It will open the screen like below.

 

 

Oops! Upload file is grayed out and when  clicking on it, it  will show the error "not sufficient permission to upload file into Site Asset folder."

 

Next you need to go and activate the "Wiki Page Home Page" to see the Site Asset library in site content. Now when you click on new list from Excel, you will see the active Upload File options.

 

 

Now provide the new list name and select the Excel file and wait until uploading completes.

 

Step 4

 

Once the Excel file is uploaded, we will see all the Tables present in the Excel sheet and will have the option to select the table from which we want to create the list.

 

 

As show in the above image, SharePoint has captured the existing table from the Excel sheet and generated the list schema out of it. A few important points to note here:

• Table dropdown lists all the tables existing in the Excel sheet. You can choose from which you want to create the  list.
• Column types are either text or number. Athough in Excel it's date and choice. A good thing is SharePoint provides options to change the column as per your requirment.
  
  
• Delivery Date
  From dropdown select the column type as Date and Time, it will change the number value to the Date format.
  
  
• Delivery Status
  From dropdown select the column type as Choice.

Now it will look like:

 

Step 4

 

Provide the list name and click on Create. It will take some time and generate the desired list.

 

 

While creating the list I also obsereved a few behaviors of this feature.

• At times the upload button was not working as it takes a lot of time spinning but not uploading the Excel. Don'tworry - in that case you can drag and drop the Excel file in library area showing the below upload button.
• If you create choice columns , then manually you need to go and update all the choice values.

Conclusion

 

Wow!! That's a quick list creation with data. Please give it a try and let me know if you face any issues.