Bypass Excel Row Limit and Analyze 1M+ Rows

How Many Rows of Data Can Excel Handle?

Excel spreadsheet can handle 1,048,576 rows and 16,384 columns at a time.

If you are dealing with large datasets, it’s inherent that you will have limitations using Excel Power Query.  If you haven’t heard of Power Query is a tool that is built into Microsoft Excel and other Microsoft data analysis tools like Power BI and SQL Server Analysis Services. It is used to change and clean data. Power Query lets you get data from many different sources, change it, and load it into Excel or other tools for data analysis.

What Happens When You Load More Than 1 Million Rows?

A few things may occur depending on your system’s capabilities. However, you will definitely not be to analyze this data or exceed Excel max row limit.

• Your Excel software will Freeze
• The performance will be Slow
• Data Loss

How to Get Passed Excel Row Limit using Power Query?

We can avoid these issues by using Power Query. Essentially, Excel has a relational data model that allows you to connect and combine data from multiple sources into a single data model that can be analyzed

1. Click Data from the top Ribbon.
2. In Get & Transform Data, Click on Get Data
3. Choose from the option on the type of data you want to ingest.
4. In the Preview Window, choose Load To option at the bottom
5. Option 1: Choose Create a Pivot Table for immediate analysis.
6. Option 2: Choose Only Create Connection and Load to the Data Model.
7. In the Queries and Connections window, double-click your table.
   • Power Query Editor will open and you can start to analyze your data.

You can then make the steps needed to ingest the data that you are looking from choosing from the existing options or from the dropdown.

After being presented with the preview menu, you can select Load to the Data Model. Remember this is going to allow us to manipulate and transform large sets of data. You can also select the Transform options if you are ready to make immediate changes.

For Immediate Analysis of 1M Excel rows Use a Pivot Table

You will choose to create a Pivot Table Report. This will allow you to quickly create a pivot table and start to analyze and bypass the Excel 1 million row limit because you are not displaying all the rows at once. They are in a pivot table.

The option Add to the Data Model gives you the ability to surpass the 1 million Excel row limit. It’s super important that you don’t try to load this onto a new sheet which is the default option. Your PC will hang and possibly freeze the Excel application. So only choose Only Create a Connection. If you are ready to analyze you can create a Pivot Table because all rows will not be displayed.

Once you load the connection it will start to bring in all the rows. you can see this being done in the Queries and Connection window on the far right side of your screen. You can double-click your table in this window to open Power Query Editor.

It’s still important to remember that you can not simply load this table with more than 1 million rows. You must transform it using filters or grouping. This is when you can analyze the data and load the table back in its smaller state. Then you can load it onto an Excel sheet.