I occasionally need to transfer data from Excel to a SharePoint list. However, in SharePoint 2013, while there is an “Export to Excel” option, there is no “Import from Excel” functionality. How sad!
Fortunately, as described in a helpful blog originally posted in 2008 for SharePoint 2007, you can use cut/paste to copy items from Excel into a SharePoint list. While the basic concept is the same for SharePoint 2013, a (minor) update to the steps provided in that blog is warranted. In this blog, I will provide detailed steps, screen shots, and an important tip for copying and pasting from Excel to a SharePoint 2013 list. My specific examples are for a SharePoint Online (SPO) tenant, but they should work similarly in an on-premise environment.
1. Open your Excel workbook and navigate to the sheet from which you wish to copy data. In This example, we will copy some tasks to a simple SharePoint task list.
2. Select the cells you wish to copy into your SharePoint list and press Control-C to copy the cells.
Note: the cells must match the data types represented in the default view of the SharePoint list. For example,
dates must be in date format.
3. Log into your SPO tenant and navigate to the list into which you want to paste data. In this example, the list already contains some items.
Note: you must have sufficient permissions to edit the list.
4. Above the list, click edit this list. This will open the “datasheet” view of the list, as if you had clicked Quick Edit in the Ribbon. The editing window will automatically appear at the first item.
This is an important tip: You must navigate to the bottom of the list to add items to the end. However, do NOT click into the last box at the bottom of the list; pasting at that point will not work properly. Instead, use the keyboard navigation keys to go to the bottom (empty) item.
5. Press Control-V to start the paste process.
6. If you see the following window, click Allow access.
The items will be added to the bottom of the list. SharePoint validates the data as it adds the items. If you see a warning icon next to any of the items, it usually means the data you are pasting does not match the data type required by the column(s) in SharePoint.
7. Click Stop editing this list to complete the process. That’s it!
I hope this short tutorial helps some folks who need to copy data from Excel into SharePoint lists. See you next time!
===== ===== =====
Do you work in SharePoint often? Have you ever had to demo it to a live audience?
Check out our Top 5 Tips for a GREAT Live Demo from our demo experts.