SharePoint 2013 export large list to Excel

We have been working with a great deal of SharePoint customers lately and theres one major query they ask for:How do I import excel list items to an existing SharePoint List?

For SharePoint Online users we have an easy method available now which lets you create a list in SharePoint by importing an existing Excel Spreadsheet.
Allow me to explain how easy is will be for you to import an excel spreadsheet to a new SharePoint List. Especially with our customers, they have been asking for a solution to upload their sales contacts, companies, opportunities and activities list, which they used to do it manually earlier.

So, how do we do this? Why should we create a custom import button, or depending on Microsoft Access, to import data? I do agree the fact that if you have thousands of records, you have no choice but to go for the custom buttons. Otherwise, keep reading

Specifically, for our non-profit customers, theyre always in need of transferring Excel data to a SharePoint List. You may have noticed theres an Export to Excel feature, as shown below, but there isnt a Import from Excel button. We can only hope that Microsoft comes up with a solution soon!

The only way for us to tackle this issue is to simply copy/paste Excel items into the SharePoint List. But wait. Didnt we try it earlier? Yes, we didnt succeed because we were using Google Chrome, Safari or Mozilla FireFox we browsers to do this action. I will be providing a step-by-step guide along with screenshots using Microsoft SharePoint Online instance. If youre using a SharePoint On-Prem, dont worry, it works for it too.

Steps

1. Open your Microsoft Excel spreadsheet, copy the cells that you wish to transfer to the desired SharePoint List.
2. Make sure the field type matches with the cells in theList.

For example, the date fields much match with the List Field Type, else it will throw an error.

1. Login to your SharePoint Online Instance usingInternet Explorerand navigate to the desiredList where you want to paste the Excel data. If you see the image below, the list has already some items. Please note that you should have sufficient permissions in order to edit the List. Get in touch with your SharePoint Administrator to verify.

1. Click on quick edit button as you see on the above image. This will open the list in a datasheet view. Navigate to the bottom of the list and select the empty cell where you wish to paste the values.

N.B. Use keyboard cursors to do the same, else it wont work!

1. Press Ctrl-V to paste the values from your Excel Spreadsheet.
2. Click on Allow access button when you see the below popup on screen.

1. The Excel data gets pasted to the bottom of your List. Now, SharePoint validates the data and if the values do not match the data type, it shows a warning icon next to the items. It wont allow you to save the records unless you have fixed it.

1. Once done, click on Stop button to complete the Excel Copying Process.
2. Voila! You just got your data pasted on yourList! Amazing, isnt it?