Error: "The query returned more data than will fit on a worksheet"

Question

When I refresh my workbook, the following message pop-ups : "The query returned more data than will fit on a worksheet"? I have around 30 Sheets which have connections to external queries. How to check from which sheet this error is coming and how to solve it?

THanks!

Answer 1

If the file containing the data began life as an XLS file, it may not hold more than ~65K records in one tab, even after saving as XLSX format. It seems that this limitation carries over. I had to create a new XLSX file to get around this.

Answer 2

In the External Data Properties for each data connection where it says: "If the number of rows in the data range changes upon refresh" try the setting "Insert entire rows for new data, clear unused cells."