Updating SharePoint List from Excel

[Gilbert Okello]

@John White, @jcgonzalezmartin  is there a way to update a sharepoint list from Excel? (other than through 3rd party tools like SoftFluent's Sharepoint list synchronizer? SharePoint List Synchronizer)

[#2]

Hello

There is a very simple solution and it can help you.

1. Open the SharePoint Online site in Internet Explorer and add in to trusted sites

2. Open the excel file and look at the columns and remember thу sequence of them.

3. Open the list you need in classic mode and edit the default view, or add a new view. You need to show the fields of the list that you need to map with excel columns. So as a result, you have to make the SharePoint table view the same like it looks in Excel

4. Click the Edit button in the table view, and the table view changes to the edit mode. 

5. Open the excel table and select the area you need. Press ctrl+c on tha keyboard

6. Open the SharePoint view, select the first cell and press ctrl+v on the keyboard.

Tha data is imported. 

[#3]

Hi Gilberto,

It depends on the usage scenario (one-time run or periodical, update by recreating all items, adding new, update existing by the key, should the list structure be the same or dynamic, amount of data, all of these make sense).

For basic cases one can use Excel's export to SharePoint list feature or inline editing feature and copy pasting for small data amount.

For complex periodic and rollup data from numerous excel documents scenarios a customization probably is the best and only option. It can be the technology of choise to read data from Excel (let's say ClosedXML .Net library or XLSX.js Node.js or Excel COM automation) and technology to write and update data in SharePoint (CSOM, SSOM or REST APIs) and settings layer extention.

Or 3rd parties, as mentioned above.

[#4]

Nothing to share from my side, Andrew and Sergei have explained very well the options you have 🙂

[#5]

Thanks @Andrew Koltyakov.  Thanks @Sergei Snitko.

[#6]

The copy and paste in Sharepoint 265 is limited to 50 rows at a time. If you copy more than 50 rows you may get errors.

[#7]

I've used Flow to do this before as on selected item in a doc library and scheduled. You can use Get Rows from Excel if you store the file in a doc library or OneDrive and then add a create item in SharePoint. The Excel get rows has a 500 row limit so I used a loop to get batches of 500 and change the start row in the Get Row action based on a variable. You'll need to use a date or flag column in the spreadsheet to track which ones have been done and update it in the loop

[#8]

@Alan Marshall Is it possible to provide instruction on how to accomplish such tasks, please? I am a beginner user of SharePoint. I tried to recreate the SharePoint list with updated Excel file in document library using MS-Flow but somehow failed. Thanks in advance.

[#9]

@Alan MarshallDo you have a Flow process for updating existing items in a list from Excel?

[#10]

@Ted-28 

Assuming you are using Sharepoint Online (SPO) or '19, Here might be the place to start for that task: https://support.office.com/en-us/article/edit-list-items-in-sharepoint-online-dac1a1c3-a80b-4082-ba5...

If you are using earlier versions of Sharepoint, the process is quite similar with the exception "Quick View" is called "Edit in Datasheet"

You will likely get a much speedier answer to queries posted here if you first ensure the answer is not easily available within the Microsoft Help/Support/Training pages.  https://support.office.com/en-us/sharepoint

[#11]

@bronniecplaceThank you for looking for answers on this topic. I've been watching it. Your reply doesn't address how to make these edits in Excel. Meaning, if I have an Excel spreadsheet linked to SharePoint, can I have it set to update particular columns for items already added and synced. For example, if I have an inventory list in excel that is set to export to SharePoint, can I edit prior rows (maybe like a quantity column that has how many of the item I have on hand) and have Excel recognize that the change needs to update an existing row and not create a new row. The goal is to be able to automate the update to existing rows on SharePoint using a master list in Excel.

[#12]

the answer to this question is now well detailed on the original thread.

[#13]

I happen to know that January 10, 2020 .. Fusion Free-Edition Is launching, and it does that and 100 other tasks on steroids!
Stay tuned 🙂

[#14]

It is FINALLY here!!!

While we are all waiting this freeware has turned up and does the job.

download: https://www.nocodesolution.com/

feature list: https://docs.nocodesolution.com/fusion/1/en/topic/fusion-feature-list

// Steen

[#15]

@Gilbert Okello 

Create Office365 SharePoint List from a CSV file and get the data transferred

Here is a 4 minute video that shows you - and it is ALL FREE!

https://www.linkedin.com/posts/steen-jakobsen-4462b914_create-office365-sharepoint-list-from-a-csv-a...

[#16]

@Gilbert Okello 

try thuis .. it is FREE and x10 better than any suggested solution 🙂

https://www.linkedin.com/posts/steen-jakobsen-4462b914_office365-sharepoint-excel-activity-663261665...

[#17]

@bronniecplace 

Hi,

I am using SharePoint Online and the steps mentioned above in 2016 are not working for me. Is there a solution available for SharePoint Online?

[#18]

@MedhAmrita1525 

Hi

The solution I've been using for years is the following :

• from the SharePoint List, define a specific view containing the fields you need and also the "ID" field (I generally call this view "ExportToExcel"). Click "Export to Excel" and accept all pop-ups.
• This creates an Excel table. Give it a name, of course (I prefix such tables with "sp" so let's name it spMyList)
• Back to the SharePoint list, build the urls for "New item" and "Edit item"
  • <base url>/NewForm.aspx
  • <base url >/EditForm.aspx?ID=
• Copy these urls as text in 2 cells, in a separate sheet in the Excel file (prefix them with a quote)
• Give names to these cells : urlNew and urlEdit
• In the table spMyList, insert a column on the left, so that this will be the first column of the table. Insert it IN the table, not before the table.
• Add a formula in the column: =HYPERLINK(urlEdit&[@ID],"Edit")
• This will create an hyperlink on each row, allowing to open in edit mode each item. Once an item has been modified and saved, go back to Excel and refresh spMyList
• Add a row above the spMyList table. In cell A1, enter this formula: =HYPERLINK(urlNew;"New"). This hyperlink will allow you to cretae a new item in the list. Just refresh Excel when it's done to see it in the list. (You can also create a button associated with the url to do that).
• Freeze rows 1 and 2.

There are many other tips to facilitate this way of working. I must make a video one day to share that!

[#19]

@Sylvie_in_France I've been searching for something like however, I have encountered a syntax error when adding the formula in the excel sheet. Any chance you have a video of this? 

Also just to clarify, update the SharePoint List will be done via the Excel Button Sheet? 

Thank you in advance for any help!

[#20]

@Ryuojiin 

Hi Ryuojiin,

Could you copy paste the formula and the error message so that I can help you?

To update the SharePoint list from Excel, you have to click on the "Edit" hyperlink on any row/item to modify. This hyperlink open the "edit item" page on SharePoint. Modify the item and Save it. Go back to Excel and refresh the table. Your data is updated in Excel.

You can of course modify serval items one by one, using their respective "Edit" hyperlink, and update the Excel file from times to times.

No video yet, sorry!

Keep me updated 🙂