Copy and Paste from Excel to a SharePoint List

[et_pb_section bb_built=”1″][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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!

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/1.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light” header_2_text_color=”#7ec142″]

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.

Detailed Steps

  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.

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/2.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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.

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/3.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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.

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/4.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

5.  Press Control-V to start the paste process.
6.  If you see the following window, click Allow access.

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/5.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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.

[/et_pb_text][et_pb_image _builder_version=”3.0.106″ show_in_lightbox=”off” url_new_window=”off” src=”http://3sharp.com/wp-content/uploads/2013/11/6.png” always_center_on_mobile=”on” use_overlay=”off” force_fullwidth=”off” show_bottom_space=”on” /][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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!

[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_divider _builder_version=”3.0.106″ show_divider=”on” /][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_blog _builder_version=”3.0.106″ background_color=”#7ec142″ show_thumbnail=”on” show_more=”off” show_author=”off” show_date=”off” show_categories=”off” show_comments=”off” show_pagination=”off” posts_number=”3″ include_categories=”14,43,228″ show_content=”off” use_overlay=”off” fullwidth=”on” background_layout=”light” pagination_font_size_tablet=”51″ pagination_line_height_tablet=”2″ /][/et_pb_column][/et_pb_row][/et_pb_section]

44 thoughts on “Copy and Paste from Excel to a SharePoint List

    1. I have seen this as well. Be sure to follow the tip: do not click into the cell where you wish to paste the data. Rather, go to the cell using the keyboard navigation keys. Then, press Control-V without clicking into the cell first. The procedure was developed with IE 10, and the SharePoint site was a “Trusted Site”. Good luck and let us know if this continues to be an issue.

      1. This worked for me! do make sure that you do not click on the box, but navigate with the arrows. This functionality is different between desktop SharePoint and SharePoint online.

      2. I was doing it in Chrome or Firefox. It worked with IE10… Not tested if it will work with IE9 too

      3. Thanks for the tip! Mine was pasting all in one cell, until I used the keyboard arrows like you recommended. Then it worked like a charm and pasted in all the individual cells for many rows!

    2. Direct from Excel – I get the same problem pasting all text into a single cell, however if I first paste into NOTEPAD, then CTRL-A/C, then paste into SharePoint Datasheet view – it works like a charm.

      1. I was constantly getting xx cells could not be pasted. Do you wish to continue with the operation and accept the changes?

        Excel > Notepad > Sharepoint 2013 worked great!

  1. I receive the message: “The formatting from the clipboard is not recognized” or something.
    It happens when in a cell within the Excel file, there are line breaks (Alt + Enter).
    When I manually copy the content of the cell to the cell in Sharepoint, it works.
    But when I copy the cell to the cell in Sharepoint, it switches to the cell under it every time a line break occurs. Is there any workaround for this?
    I have to copy around 800 items, would be nice not having to do this manually.

    @venkateshkadiri:disqus have you tried using IE? I have the same problem in Chrome, but with IE it works.

    1. I am having the same issue as KawaGreen.
      I have cells in my spreadsheet that have Line breaks (Alt+Enter). I am using IE 11 and Office 2013. When I copy this data to the SharePoint List it creates multiple enters and does not copy the single cell from Excel to a single field/column in SharePoint.

    2. First remove the “enter” characters from data. Excel function to recognize that characters is Char(10)

  2. I have IE8, with that will this feature work. When I open the sharepoint list in datasheet mode, it is a read only vieew and do not have option to add rows or paste the data

  3. I am using IE8 for the same operation. My Datasheet view is opening in read only mode and not allowing me to paste any rows.

  4. Have you found a limit to the number of rows you can copy/paste? I am moving a spreadsheet with 900 items/50 columns. I can move them 50 at a time, not not the whole thing. Wondering what is setting the limitation…SharePoint2013, datasheet mode, excel, IE browser, or the OS (or the company’s internal network security setting?). A d suggestions appreciated. Thx.

  5. I run O365 2016 for Mac with OS X 10.11.1
    Tried to do this in both Safari and Chrome.

    In Safari all is paste into one cell. In Chrome it doesn’t paste at all…

    Any clues?

  6. This is an update to answer some of the questions that appear on this blog post. For other replies, please see the individual questions.
    I re-tested the above steps on 12/14/2015.
    SharePoint: Office 365, E3 tenant
    Operating system: Windows 10 desktop
    Browsers: Internet Explorer 11, Chrome, Firefox, and Edge.
    Results; Only IE11 (and presumably, some previous versions of IE) works as written. None of the other browsers responded to the Cntrol-V (Paste) key press.

    For now, the above process seems limited to Internet Explorer. If anyone has successful tests on the browsers other than IE, please reply to this update and specify your environment.

  7. Hi All,

    I am able to paste data directly from Excel to Sharepoint using IE.
    However, I am only able to copy paste INDIVIDUAL ROWS and not MULTIPLE ROWS.
    Anyone else have this problem? Would you have any advice?

    Thanks!

    1. I just tested the procedure again as per my 12/14/15 post and it worked as written. Not sure what may be happening in your case. Sorry! If anyone else has this issue and finds a fix, please post a reply.

    2. I was trying since I have a lot of data to copy. I found out a few things:
      1) Multiple lines of txt won’t work if it’s Enhanced Rich Text – it’ll said read only field
      2) Choice fields won’t work either – it’ll said read only field
      3) If copy rows end up pasting in a single cell, copying per column worked for me

    3. I am having same issue, tried all steps above without resolution. I’m in IE. Will allow me to paste one row, which means my formatting and columns are ok, but will not allow multiple rows. Error message says “The information cannot be pasted because the paste area is not completely within the grid. Paste into a different cells or try inserting more columns/rows into the grid.” I know I have the correct number of columns, and it will not allow me to add more blank rows. Please help!

  8. Will this launch “Add” triggers for workflows which are set for new items? Our list does calculations and copies to other list on Add New Item.

  9. I need to copy urls from excel to list. But I am getting error like url is not available. Only Text is displayed.How to copy paste excel data with hyperlinks into the list.

  10. I have doing this procedure at work for a while, and suddenly I have run into some issues that have never happened before:
    After clicking “edit this list” and then attempting to paste using the ctrl+V shortcut, it registers the ctrl press as “stop editing this list,” taking me back to the original list and not allowing me to paste. I can’t use the paste function in the edit menu of IE either. This only seems to happen when I select an entire blank row to paste into. If I just select the first column of a blank row, it doesn’t happen when I press ctrl.

  11. Thanks SO MUCH RE:
    :The follow the tip: do not click into the cell where you wish to paste the data. Rather, go to the cell using the keyboard navigation keys.”

    I was pulling my hair out!!

  12. This tip is great! Is there a way on how to adjust row/column sizes of a list for SP user without admin rights?

  13. Ha! That “select first cell in last row with keyboard” was the important tip indeed 🙂 thanks! This is still working like you wrote here 😀

  14. I used to get the pop up box ‘do you want to allow this webpage access your clipboard’ and clicked allow access and everything was fine, now it doesn’t appear so I cannot paste anything into the list. Any recommendations?

  15. Currently when I attempt to paste into a SharePoint list nothing seems to happen. I’m taking extra care to navigate to the cell using the navigational keys and have checked permissions and settiings on the list and still can’t seem to get this to work.

    I’m using SP 2013 and Excel 2010 and have attempted pasting the info into the Excel web app and notepad.

    Does anyone have any suggestions?

  16. used the navigational arrows after about an hour of trying to figure out what the issue was. thank you so much! another tip is that to make sure there is only one blank row at the bottom. I had multiple because of my troubleshooting previously and the navigational trick wouldn’t work until I put data in those rows.

  17. When you want to add a lot of data to a custom list, create a datasheet view which matches the spreadsheet in which you are copying the data from. Then you can copy (cont c) and paste (cont v). The data will populate accordingly.

  18. I found the key was to use IE to do the copy/paste function into SharePoint. It only pastes in one cell when I use it in Chrome.

    Thanks everyone for the tips!

Comments are closed.