We're doing a major update of our SharePoint online site that will add a number of new fields to our existing lists.

I've followed the steps to creating a backup of the lists just in case something goes wrong: https://support.microsoft.com/en-ca/kb/2783484

How then do I use the generated Excel spreadsheet to restore the my list if need be? Alternatively, is their a better way of doing a backup/restore in SPO?


Follow these steps:

  1. Decide on a name for the list, it will be the URL and name of the list.
  2. Go to the ‘cog’/Settings and select Add an app
  3. You will now see a list of all available apps (installed)
  4. Search for Import or scroll down until you find the app called Import Spreadsheet
  5. Click on Import Spreadsheet
  6. Type in the Name of the new list
  7. Type in a optional Description
  8. At the File location field, click Browse…
  9. Browse to your xlsx file on your computer
  10. Click on Import
  11. The Import Wizard starts, by default, the Range type is set to Table Range
  12. Change this to Range of cells
  13. Click in the Select Range field
  14. In the Spreadsheet, select the top left cell and then make sure that you select the entire table of data you want imported
  15. It should now read something similar to: Sheet1!$A$1:$G$400 (top left : bottom right)
  16. Now you click on Import
  17. You may now get a logonprompt from Excel, enter the emailaddress for the SPO account and click next
  18. Enter the account password and click on Sign in
  19. Excel will now create the list and start importing the data, you will see a little progressbar at the bottom of the Excel application.
  20. When the import is done, the new list will open and you will see the columns from the top

From here

  • 1
    Great! This is definitely what I was looking for. Thanks! – Katie Sep 12 '16 at 14:37

I find importing your table into MS Access is the easiest way to then put it back up online if required.

Just open access, add a new table, right click it and "import > SharePoint list". Enter the URL of your site and choose the list you want.

You can then export back to SP in nearly the same way.

P.S. This is also a handy way to spawn the same list columns across to multiple tables on various sites instead of having to create all the columns over and over (when you can't simply use a template) :)

  • 1
    While it does indeed keep the data, this does not keep the type. I've got lookup fields on my list and it became multiline when I exported back. – Katie Sep 12 '16 at 15:46
  • @Katie - aha yes, you're right there :) it should maintain most types though. I'd be interested to hear if the Excel option can keep lookups; not listed as an error in the link Paul provided which is a good start. That'd be pretty neat :D – Karl Major Sep 12 '16 at 16:16
  • I actually got errors using Paul's solution so now I'm resorting to using a third-party tool (Metalogix) to see if it can do the backup/restore job. – Katie Sep 12 '16 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.