Learn how to use Excel Get and Transform to exceed the Excel million row limit. Get and Transform uses Power Query to work with an unlimited number of rows and columns in a dataset. Data can be aggregated and summarized to enabled report viewers to work with big data in the form of a Pivot Table.
Power Query is only limited to the amount of resources that you have available on your system, and even allows you to connect to outside data sources, such as SQL databases or Power BI datasets that can scale to multiple gigs of data.
Let’s jump in and take a look!
Table of Contents
Why it’s Important to Exceed the 1 Million Row Limit in Excel
Companies are collecting an exponential rate. Statista estimates total data captured in 2010 at 2 zettabytes and is expected to exceed 181 zettabytes by 2025. This means that business professionals such as accountants, financial analysts, and business intelligence analysts have to work with increasingly large amounts of data.
It’s estimated that Microsoft Office has a user base in excess of 1 billion users worldwide. That makes Excel one of the most popular business intelligence and analysis tools in the world.
When you combine the exponential growth of data, and the wide use of Microsoft Excel, you can see how important it is to be able to work with big datasets directly in Excel.
Let’s define what makes up a large dataset in terms of Microsoft Excel.
What is the Maximum Number of Rows in Excel?
Excel 2007 and later support a maximum of 1,048,576 Rows and 16,384 Columns (XFD). Microsoft Power Query is a data transformation tool built into Excel under Get & Transform tools which has no hard data limit and can manipulate datasets in excess of 1 million rows.
The only limitation of using Power Query is the amount of system resources available.
For the sake of readability, we will refer to it as a 1 million row limit in Excel.
What is the Maximum Number of Rows in Excel Online?
Excel Online is not limited by the number of rows and columns on a sheet and is only limited by the size of the file. Large Excel files up to 250MB in size can be published to the Power BI service. To work with even larger files, Power BI Pro subscribers can work with Power BI files up to 1 GB in size and can exceed 10 GB with Premium Capacity.
Importing Large Datasets into Excel
The first step to working with large datasets in Excel, is to import the data. To bring large files into Excel, navigate to Data > Get Data under the Get & Transform Data Section of the Microsoft Excel Ribbon.
In our example, we will import a large .csv file that we have saved on our desktop.
The screenshot below highlights some of the other data sources that are available that you could bring into Excel. You are not limited to working with a single file, you can consolidate a folder of individual files, connect to SQL Servers, Power BI datasets and many other formats.
Flexibility in the types of connections supported make Get and Transform the top solution when working with large data in Excel.
After selecting From Text/CSV a screen will appear that shows a preview of the First 200 rows of data. This screen helps ensure that you’ve selected the correct file prior to importing it.
At this point, you can either Load the data, or you can Transform Data.
Transform Data – Launches the Power Query Editor. A tool to prep, blend, and transform large datasets that would otherwise not be possible in regular Excel.
Load – Brings the data into Excel. Data will either be loaded as a connection to the large data file, brought into an Excel sheet, or it can be loaded into Power Pivot.
For our example today, we will use Power Pivot. A form of pivot table that handles extremely large datasets and lets you manipulate them in a format that most Excel users are already familiar with.
Select the “Load To” option by clicking on Load.
The following screen will provide you with several options of where to Import data to.
Select Pivot Table Report into a New Sheet
The result is a new Pivot Table on a new sheet that is connected to 3 million rows of data. You can drag and drop fields into columns or rows, and manipulate it similar to how you would work with a regular pivot table.
Excel maintains a connection to the raw data file, or can import it into a data model. This allows you to embed the data into Excel while exceeding the maximum row limit if the full file was sitting on multiple sheets.
Options for Working beyond the Excel Million Row Limit
When loading data into Excel there are several options for how to work with it. You can load data into a table subject to a row maximum. Pivot Tables and Pivot charts have no limit, or you can create a connection to a data source that is also unlimited.
The following table explains the different large data options after you bring the data into Microsoft Excel.
| Import Option | What It Does | Limitations | Suitability for 1+ Million Rows |
|---|---|---|---|
| Table | Imports data into an Excel worksheet as a table. | Max 1,048,576 rows in a worksheet. | Not Suitable |
| Pivot Table Report | Creates a Pivot Table report based on the imported data. | None | Suitable |
| Pivot Chart | Creates a Pivot Chart based on the imported data. | None | Suitable |
| Only Create Connection | Creates a connection to the data source for use later. Does not import data into an Excel worksheet. | None | Suitable |
| Add Data to Data Model | Imports data directly into Excel’s Data Model rather than a worksheet. | None | Suitable |
To work with pivot tables and pivot charts, they must be connected to a data model, or connected to a data source. If using a regular table as a data source, you will be limited to 1 million rows.
How to Fix The Query Returned More Data Than will Fit on a Worksheet Error
If you attempt to import more than 1 million rows of data into an Excel workbook, Excel will return an error that says, The query returned more data than will fit on a worksheet. It means that the dataset being brought into Excel exceeds the maximum row limit.
If you press OK, Excel will only load as much data as it can fit into Excel and leave off everything after the row limit is reached.
To correct the error, press Cancel.
The dataset will still show as available, but will notate an error that Load to Worksheet Failed.
Mouse over the Load to Worksheet Failed error and select “Load to Data Model”
After the data is loaded, you can mouse over the dataset connection, and click on the … dots at the bottom of the page and select Load To…
This gives you the option to insert the data as a pivot table.
You could also right click on the data that is listed, and select Edit to launch the Power Query Editor.
How to Edit a Power Pivot Dataset
After loading data into Power Pivot, a Queries & Connections panel will show on the right side of Microsoft Excel. This lists your datasets and files that have been brought into Excel. Right click on a dataset and select Edit. This will launch the Power Query editor where you can adjust and transform the incoming data.
The Power Query editor will let you work with millions of rows and is not subject to the normal worksheet limit. However, there is a learning curve as the interface is different.
How To Perform Calculations and Transform Large Datasets in Excel
After you Get Data to bring into Excel, you can either choose to Transform Data or you can Load it. If you choose to Transform Data, it will launch the Power Query Editor. Power Query is a data transformation and preparation tool designed for data analysis. It’s used across a wide range of Microsoft products, including Excel, Power BI, and Azure Analysis Services.
The screenshot below is the Power Query Editor.
While there are many features of Power Query, you can right click on column names and perform many common tasks like removing white space, replace values, change to uppercase etc. From the top ribbon, you can add calculated columns to add numbers together, filter and sort rows along with hundreds of other functions.
Every time you perform a calculation in Excel, a new Applied Step will appear on the right Query Settings panel. After completing any required data transformation steps, click Close & Load from the top left to save your changes.
Note: Power Query is the only way to modify millions of rows of data in Power BI. The standard tools and formulas will only work if the data is in a worksheet which is limited to 1 million rows.
Modifying Large Datasets in Excel Power Query
If you’re new to Excel Get & Transform, it’s an incredibly powerful tool that’s worth learning more about. You can modify millions of rows with the click of a button, and you can even transfer queries between Excel and Power BI if people within your organization utilize both platforms.
Check out this overview from Microsoft that highlights a number of features available in the Power Query Editor.
As you can see, Power Query is a different interface from Excel, but is very approachable and easy to use after a little bit of practice!
Conclusion
Excel is one of the most widely used business intelligence and analytics tools used in the world. Companies are generating and collecting data at an exponential rate. Accounting and Finance professionals along with many others need to learn a new way to use Excel to handle large datasets.
Microsoft Excel has a 1 million row limit that can be exceeded with Get and Transform, Power Query, and Power Pivot. By leveraging these features you can maintain data in Excel and apply the skillsets that you already have honed over years of experience to continue delivering great results.