Row number exceeds maximum number allowed

The easiest way is probably to use PowerQuery.  Load to the "data model". The data model stores the data independently of the row limit, it also compresses the data where possible.

.

Data in the data model is available to be access from PivotTables and the excel tables.

.

What do you want to do with this data?

.

Can you share a SMALL example file with a representative subset of rows so we can help you do your final calculation(s)?

Here are a few general articles that relate to loading data into the data model and extracting it. If you provide more specific details about what you want to do, we can provide more specific suggestions.

!  The Complete Guide to Power Query           2018 02 11
https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/
Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed.
It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA.
The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder with VBA.
.  *  What Can Power Query Do?            .  *  Where is Power Query?
.  *  Importing Your Data with Power Query                  .  *  Simple Example of Importing Data in a File
.  *  The Query Editor                    .  *  The Query List
.  *  The Data Preview                  .  *  The Applied Steps
.  *  The Formula Bar                    .  *  The File Tab
.  *  Data Loading Options            .  *  The Queries & Connections Window
.  *  The Home Tab            .  *  Difference Between Transform & Add Column Tabs
.  *  The Transform Tab                .  *  The Add Column Tab
.  *  The View Tab
.

Static Tables in Power Query, Power Pivot and Power BI                 2021 03 31   Mynda Treacy
https://www.myonlinetraininghub.com/static-tables-in-power-query-power-pivot-and-power-bi
https://www.youtube.com/watch?v=M2K6IeEkrGI       9min38
Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source.
But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it is created directly inside Power Query or the Data Model.
In this post I'm going to show you 5 ways to create tables in Power Query, Power Pivot and Power BI without loading any data.
.  *  #table in Power Query
.  *  Power Pivot
.  *  Power BI - Enter Data
.  *  DAX Table Constructor
.  *  DATATABLE Function in DAX
.

Getting Started with DAX Studio                    2016 02 23  Matt Allington
https://exceleratorbi.com.au/getting-started-dax-studio/
DAX Studio is a fabulous free tool that allows you to directly query your Power Pivot/Power BI data models.  It is not immediately obvious (particularly to beginners) exactly how using DAX Studio can add value.  I often refer people on various forums to use DAX Studio but then lack a suitable reference to refer them to so they can get started.  This blog post today is a simple reference to help anyone that uses Power Pivot get started with DAX Studio. .  *  What Can I Do With DAX Studio?
.  *  Connecting DAX Studio to Excel Power Pivot
.  *  Connecting DAX Studio to Power BI Desktop
.  *  Export to CSV.  *  DAX Studio Window – Getting Started
.  *  Extract a list of Measures
.  *  Extracting Tables from your Data Model
.  *  Creating an Extract for a “New” Table
.  *  Move your Query to Excel
.  *  Evaluate a Single Measure
.  *  Testing Performance of Measures
.  *  Use your Power BI Desktop Model as a SSAS Server
.

Hello,

Thanks for your reply . I need all the data in Excel to run different formulas and sort the data. All I want to do is import my rows from my text file and at the 1,048,577th row (one row beyond the maximum), Excel will automatically start a new sheet and continue  loading the data.

Can Power Query do this?

Thanks

Re:  import very large text file

The final Excel file size might be so large that it is slow and balky.
I have a file with ~190 sheets, but the max sheet size is a few hundred rows.

Depending,  you could use vba code written by Chip Pearson (rip) found at...

http://www.cpearson.com/excel/ImportBigFiles.aspxorhttp://www.cpearson.com/excel/ImportBigFiles.aspx-or-UseUse Power Query, for which you will need instructions.
I can't provide them, but there seem to be plenty here...

https://duckduckgo.com/?t=ffsb&q=use+power+query+to+import+large+files&ia=web

https://duckduckgo.com/?t=ffsb&q=use+power+query+to+import+large+files&ia=webAlso,Also, there are an number of very knowledgeable people in this forum with PQ experience.
One of them could be along any time.

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

(free excel programs)

Hi NLtL,

Sorry, yes I meant I have a text file that is 174 million rows that I wish to convert to a text file  with multiple sheets.

Thanks

Re:  174,746,621 rows

How did you get 174 million rows into an Excel file?

or
Do you mean you have an excel file with 175 columns each with a million rows?

or
Do you mean you have a non-excel file that you want to convert to Excel?

or
Or what?

'---
NLtL