How to Import Excel List Items to an Existing SharePoint List

We have been working with a great deal of SharePoint customers lately and there’s 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, they’re always in need of transferring Excel data to a SharePoint List. You may have noticed there’s an “Export to Excel” feature, as shown below, but there isn’t 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. Didn’t we try it earlier? Yes, we didn’t 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 you’re using a SharePoint On-Prem, don’t 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 the List.

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

3. Login to your SharePoint Online Instance using Internet Explorer and navigate to the desired List 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.

4. 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 won’t work!

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

7. 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 won’t allow you to save the records unless you have fixed it.

8. Once done, click on “Stop” button to complete the Excel Copying Process.
9. Voila! You just got your data pasted on your List! Amazing, isn’t it?