Some time ago I was working with an Excel-loving crowd who had to share their data in a SharePoint list.
I wanted to make the transition as smoothly as possible, so I created a special “Data Import View” (a Datasheet View) to make it as easy as possible to copy their Excel data into the list.
There were too many people involved with too many different PC configurations, to trust a Data Connection. So a one-time copy-paste action it had to be!
After some tests I gave them the following instructions:
1. Sort the columns in your Excel sheet in exactly the same order (left-to-right) as the Data Import View (I listed all columns in the correct order)
2. Use the exact same words, spelling and capitalization as the values in the dropdown fields in the SharePoint list.
I listed all the words – we used “color” and not “colour”, for instance.
If you use different capitalization in a Choice column (e.g. “Preferred Supplier” instead of “Preferred supplier”) the Choice column will accept your value without error message, but when you edit the item, the value will change to the first of the Choice values.
3. Check your cells have exactly the same formatting as the SharePoint list.
Sometimes text fields gave errors while copying – copying the text from the dropdown field and paste that into the Excel file often solved that.
4. Use the correct date-and-time format. (I had to test a few different notations to know what worked and what not in our multinational organization)
5. Select the first (utter left) cell before you start pasting. (The Data Import View contained many columns, and needed horizontal scrolling to see all columns, and sometimes people forgot to scroll back).
6. Start pasting only when the first cell is ready. It shows a bold line around it.
7. Do a test-run with one line. Adjust your data and check again when you encounter issues.
Next time your users say that “they can not copy -paste their Excel data into a Datasheet View”, these tips may point you towards a solution.
I could have made things slightly easier by using a single-line-of-text for each column. Single-line-of-text is like bloodtype AB: it can receive anything :-). But because I had originally designed the list for adding new items via “New Item” in the Standard View we had to create these instructions. And single-line-of-text columns lead to more deviations in spelling.
David Young has also found that you need to refresh your screen after deleting data and trying again.
Do you have any other tips or experienced other errors? Please share!
You may also like:
Design Fault, Scope Creep, Moving Insight or Unexpected Success? (The project that inspired this post)
10 tips for Choice or Lookup Columns
Ellen's Digital Workplace 
Hi,
When am trying to copy data from excel to share point via Datasheet view, Unable to perform , Also getting a popup as “Selected cells are read only”,
Kindly help me from this issue.
Hi Vinod, Thank you for the question. There are several reasons for “selected cells are read-only”. You may not have permissions to add or edit, you can not edit “system fields” such as Created/Modified and Created/Modified By, and having content approval also makes your cells read-only. I am actually collecting data for a new post on this topic. Do you have any specific ädvanced settings” on your list or do you know which type of column you are trying to edit?
I am trying to paste dates from SharePoint back into Excel. I cannot get the date format to work as a date. I have tried formatting, text-to-column conversion – but cannot get that date in SharePoint to be recognized as a date in Excel. Any ideas?
Hi Johlene, dates are always tricky since there are different notations and source and target may not always be aligned. You may want to check your site’s localization settings to see what date notation it uses. Or tou can enter a date in your list, check what it looks like, copy it into your Excel and see if it copies back into your list. That may give you a clue. Good luck!