so I know we can export a single list to an Excel Spreadsheet, but is there a way export multiple lists on that same spreadsheet?
there is no OOT functionality which support this but here is workaround, which may solve you problem.
Go to SharePoint List A and then click on List tab on Ribbon control
Then click on ‘Export to Excel’ button on ribbon control
- Save the file with meaningful name, for example: owssvrA.
Repeat the steps for 1 to 3 for List B and save file with another name:owssvrB.
Now we have 'MS Excel Web Query File' files for both lists.
- Open new excel application.
- Go to Sheet1 and click on any cell.
Click on Data in ribbon
Click on Existing Data Connection
Click on ‘Browse for More’ and go to the location where ‘'MS Excel Web Query File’ files are saved.
Select List A’s Query File and click Open.
- On Import Data pop up select ‘New Worksheet’, (if you know exact column numbers then choose existing worksheet and cell range).
- Now you have data of List in your Excel sheet.
- Rename sheet with name: “Sheet1” and you are done for list A.
- Now repeat same steps for List B and you have two lists data in two separate sheets. You can refresh data as well from the ribbon button. Isn’t it easy??
I have seen this so many times and also always wanted to know if there was a way. Craig Tarr wrote up a way on how to do it using front-end code. Basically the script structures unstructured data using REST API. Once you have the data you can export to excel or another DB. Completely configurable.