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 but we can analyze more than that using the Data Model feature in Excel.


Step 1 – Setting up the Source Dataset

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

How to Handle More Than 1048576 Rows in Excel 1

  • 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

 

  • From the Data tab → select From Text/CSV.

How to Handle More Than 1048576 Rows in Excel 3

  • Select the downloaded source dataset from OneDrive.
  • Press Import.

How to Handle More Than 1048576 Rows in Excel 4

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


Step 3 – Adding to the Data Model

 

  • Another dialog box will appear.
  • Select “Load To…

How to Handle More Than 1048576 Rows in Excel 5

  • Select “Only Create Connection”.
  • Select “Add this data to the Data Model”.
  • Press OK.

How to Handle More Than 1048576 Rows in Excel 6

  • The status will state 2,000,000 rows loaded.

How to Handle More Than 1048576 Rows in Excel 7

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.

  • From the Insert tab select PivotTable and from the dropdown menu select From Data Model.

How to Handle More Than 1048576 Rows in Excel 8

  • The PivotTable from the Data Model dialog box will appear.
  • Select Existing worksheet and specify the output.
  • Hit OK.

How to Handle More Than 1048576 Rows in Excel 9

  •  Ablank pivot table will appear.
  • Enter 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

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

How to Handle More Than 1048576 Rows in Excel 11

  • This will be the output of the pivot table.

How to Handle More Than 1048576 Rows in Excel 12


Step 5 – Employing Slicers

 

  • Select anywhere inside the pivot table.
  • From the PivotTable Analyze tab select Insert Slicer.

How to Handle More Than 1048576 Rows in Excel 13

  • The Insert Slicers dialog box will appear.
  • Select Name and press OK.

Slicer

  • The Name Slicer will appear.

Dataset Slicer


Step 6 – Inserting Charts

 

  • Select anywhere inside the pivot table.
  • From the PivotTable Analyze tab select PivotChart.

Pivot Chart

  • The Insert Chart box will appear.
  • Select Bar and press OK.

Insert Chart window

  • A graph has been created.

Bar Chart

  • Add a title and modify the graph to suit the users needs.

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. Therefore, if you have a slow computer, it will take a lot of time to analyze a large number of rows.

Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. Great, and very useful. Thank you!!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo