Compiling Data from SharePoint Export in Excel

New Contributor

I appreciate anyone's thoughts on this problem I am having:


I have a SharePoint list that exceeds 5K items. I have built a number of filtered views to ensure that no 1 view exceeds 5K (the ceiling for a view in our environment is 5K).


There is one spreadsheet that I use to export all of the data from the various views to separate worksheets. I would like to automate a function that would compile the data from the various spreadsheets into 1 collective sheet.


I have formulas that can pull the data from the exported sheets but ideally I would like to place the formulas in successive rows, so that no additional sorting would be needed. When I place the formulas in successive rows I get the spill error.


I am using a PC with Excel 2008. I have some experience with excel formulas but none with VBA.

Any recommendations are appreciated. Thank you!

Export Compile Problem.png

2 Replies
best response confirmed by HopelessGent (New Contributor)

Hi @HopelessGent 


Assuming all your lists have the same structure Get & Tranform aka Power Query is your friend


1/ For each list/table:
- Go to Data (tab) > From Table/Range (this opens the Power Query editor)
- Click on the little arrow down next to Close & Load > Close & Load To... > Check Only Create Connection


2/ Once all your connections are established Append all queries as a single table that you'll load on a new worksheet

@L z.   Thank you so much!  This worked beautifully.