Is There Any Way to Increase the Excel Row Limit?

Each workbook in Excel has a limit on the number of rows and columns of data it allows.

Is There Any Way to Increase the Row Limit in Excel?

The short answer is no. However, there are ways around it using a Data Model.

How to Increase the Row Limit in Excel Using a Data Model

Assume you have the following 3 datasets. The first includes the student’s class test marks, the second contains the students’ mid-term exam marks, and the third dataset contains the final exam marks.

Our goal is to connect these 3 datasets and then create a relationship among them using a Data Model.

Steps:

• Click anywhere in the first dataset.
• Go to the Insert tab and click on the Table option. A window will pop up.

• Check the data locations of your first dataset and mark the My table has headers checkbox, and then press OK.

• Select a new table, and insert the table’s name in the Table Name under the tools group by selecting the new table.

• The first dataset is converted to a Table object.
• Repeat the above steps for the remaining data.

• Go to the Data tab then Data Tools.
• Click on the Relationships button. A Manage Relationship window will pop up.

• Click New. The Create Relationship Dialog box will pop up.

• Expand the Table and Related Table dropdown and select the relevant tables and columns.
• Click OK.

• The Manage Relationship dialog box will pop up.

• Repeat the steps as necessary for other tables.

All 3 tables are now related.

Using PivotTables to Analyze Table Objects

• Click on Insert tab > PivotTable. A pop-up window will appear.

• Check the Use an external data source > click on Choose Connection. An Existing Connection window will appear.

• Click on Tables > select Tables in Workbook Data Model > click on Open. A window will pop up.

• Select New Worksheet > press OK.

The PivotTable Fields pane will show the table objects that are available to analyze.

Now, if we want to calculate the total CT marks for all students, here it is.

Download Practice Workbook

Download the following Excel file for your practice.

<< Go Back to Excel Limits | Excel Parts | Learn Excel