Link Excel to SharePoint 2010 list content

Question

I have an Excel document that is linked to my SharePoint 2010 list. Recently my list climbed over the 5000 limit so I had to add some filtering to my view. This negatively impacted my Excel document because the data got filtered there as well. In Excel I have a report that is built on the data so I need the entire content of the list.

I know that Access links to the content of the list and ignores the view. Is there a way that Excel can do the same thing?

I tried using the Query List Data with Excel with no success. The only thing that I have gotten to work is to have a second view without filters that I link to but I feel that is not the best way to go about this.

Answer 1

Your choices are basically:

• Use Access
• Use the Excel Office Web App
• Only work with this Excel file during the Daily Time for Large Queries Window
• Use your unfiltered view (note that this approach does not work for document libraries, only lists)
• Increase the List View Threshold (not recommended)
• Write specialized code to pull the data into Excel in 5k batches

Which way you go depends on your particular environment and needs.