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.
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”.
- Next, we can see there are 2,00,001 lines (or rows) in the dataset including the heading row.
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.
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…”
- 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”.
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.
- 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.
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!