Dr. Nitin's KLOG

This problem has been haunting people for a very long time.
The recent increase of the row limit to 1 million+ rows may have solved the problem. But sooner or later you are bound to have data which exceeds this limit as well. So what we need is a better approach which works across Excel versions.

Here I am assuming that you don't want to scroll through thousands of rows but you want to create a Pivot Table to analyze the data.

Here is the summary of steps involved.

1. Split data into multiple sheets.
2. Create a named range for each block of data
3. Save the Excel file
4. Create and ODBC data source based upon the Excel file.
5. Open a new, blank Excel file
6. Create Pivot Table based upon the Excel ODBC data source
7. Edit the query manually and create a UNION based select query which combines all the blocks of data
8. Make a pivot table directly (don't try to get the data into Excel sheet, because it will not fit).
9. Pivot table has no row limit!