How to replace all items in a List with data from Excel sheet

Question

For a specific use case, I need to synchronize/update all items in a SharePoint 365 List with fresh data from an excel sheet.

If the sheet contains new items, they should be added to the list. If items are removed, they should be removed from the list.

The Excel sheet has a column with a unique identifier that can serve as primary key.

I'm looking for a way to do this without any additional coding.

My questions:

• Does SharePoint have a built-in way to do this?
• Are there any pitfalls that I should be aware of?

Things I've tried:

• Paste into Quick Edit window. This is tedious and slow, and seems to crash often. Also I'm worried that this will inevitably create inconsistencies due to human error.

Answer 1

try opening it in MS Access. you can paste multiple rows at a time (100+) and sync it back to sharepoint

Answer 2

I had the same issue but out of frustration I tried Internet Explorer over my standard Chrome. Pasted fine :)

Had similar issue with the Connect to Explorer functionality - works in IE but doesn't work in Chrome...

hope it helps !!