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.


Download Practice Workbook


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

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


Step 1: Setting up 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”.

How to Handle More Than 1048576 Rows in Excel 1

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

How to Handle More Than 1048576 Rows in Excel 2


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.

How to Handle More Than 1048576 Rows in Excel 3

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

How to Handle More Than 1048576 Rows in Excel 4


Step 3: Adding to 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…

How to Handle More Than 1048576 Rows in Excel 5

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

How to Handle More Than 1048576 Rows in Excel 6

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

How to Handle More Than 1048576 Rows in Excel 7


Step 4: Inserting PivotTable from 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.

How to Handle More Than 1048576 Rows in Excel 8

  • 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.

How to Handle More Than 1048576 Rows in Excel 9

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

How to Handle More Than 1048576 Rows in Excel 10

  • 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”.

How to Handle More Than 1048576 Rows in Excel 11

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

How to Handle More Than 1048576 Rows in Excel 12


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.

How to Handle More Than 1048576 Rows in Excel 13

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

Slicer

  • Thus, the “NameSlicer will appear.

Dataset Slicer


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.

Pivot Chart

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

Insert Chart window

  • Doing so, a graph will appear.

Bar Chart

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

Final Output


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.

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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, and keep doing well!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo