How to Handle More Than 1048576 Rows in Excel

By default, Microsoft Excel does not allow us to work with more than 1048576 rows of data. However, we can analyze more than that using the Data Model feature in Excel. In this article, we will show you 6 quick steps to handle more than 1048576 rows in Excel.

How to Handle More Than 1048576 Rows in Excel: Step-by-Step Procedures

In this section, we will describe the step-by-step process of handling more than 1048576 rows in Excel.

Step 1: Setting up the Source Dataset

In the first step, we prepared the source dataset. We created a few thousand unique rows and then used them repeatedly to create the dataset. You can download this dataset from OneDrive with these features:

• Firstly, the source dataset for this article has three columns: “Name”, “Sales”, and “Zone”.

• Next, we can see there are 2,00,001 lines (or rows) in the dataset including the heading row.

Read More: How to Increase Excel Column Limit

Step 2: Importing Source Dataset

Excel can import data in a variety of ways. We can use the options inside the Get & Transform Data subtab.

• Firstly, from the Data tab → select From Text/CSV.

• So, the Import Data window will appear.
• Then, select the downloaded source dataset from OneDrive.
• Afterward, press Import.

Read More: How to Set the End of an Excel Spreadsheet

Step 3: Adding to the Data Model

In this step, we added the imported dataset to the Data Model.

• After pressing Import at the end of the previous step, another dialog box will appear.
• Then, press “Load To…”

• Next, select “Only Create Connection”.
• Then, select “Add this data to the Data Model”.
• After that, press OK.

• The status will show “2,000,000 rows loaded”.

Read More: How to Limit Number of Rows in Excel

Step 4: Inserting PivotTable from the Data Model

Now, utilizing the information from the Data Model, we added a pivot table.

• To begin with, from the Insert tab → PivotTable → From Data Model.

• Therefore, the PivotTable from the Data Model dialog box will pop up.
• Then select “Existing worksheet” and specify the output. In our case, we have selected cell B4.
• Lastly, press OK.

• So, a blank pivot table will appear.
• Next, put the “Zone” field in the “Row” area and the “Sales” field in the “Values” area.

• Then, select anywhere inside the pivot table, and from the Design tab → Report Layout → select Show in Outline Form. This changes “Row Labels” to “Zone”.

• If you have followed our steps correctly, then this will be the output of the pivot table.

Step 5: Employing Slicers

The Excel Slicer is a great tool to filter pivot tables and we can use this to handle more than 1.05 million rows of data.

• To begin with, select anywhere inside the pivot table.
• Then, from the PivotTable Analyze tab, → select Insert Slicer.

• So, the Insert Slicers dialog box will pop up.
• Next, select “Name” and press OK.

• Thus, the “Name” Slicer will appear.

Step 6: Inserting Charts

In the final step, we will use a Bar Chart to visualize the data.

• Firstly, select anywhere inside the pivot table.
• Secondly, from the PivotTable Analyze tab → select PivotChart.

• Then, the Insert Chart box will pop up.
• Afterward, select “Bar” and press OK.

• Doing so, a graph will appear.

• Lastly, we have added a title and modified the graph a bit and this is what the final step looks like.

Things to Remember

• The Excel Data Model feature is available starting with Excel 2013. The data is kept in the computer’s memory by this feature. Therefore, if you have a slow computer, it will take a lot of time to analyze a large number of rows.

Download Practice Workbook

Conclusion

To handle more than 1048576 rows in Excel, we have demonstrated how to do so in just six simple steps. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, and keep doing well!

Related Articles

• How to Limit Columns in Excel

<< Go Back to Row and Column Limit | Rows and Columns in Excel | Learn Excel