[Solved] How to extend excel rows over 11 lakh in one excel sheet.

[Jubayer Hasan]

Hello!

I have two sheets in an excel file rows totalling 14 lacs, How can i combine 14 lacs rows in a single excel sheet (tab) without deleting anything. N.B all columns formats of the excel tabs are same of the seperate file.

 

[nafissoumik53]

Hello!

I have two sheets in an excel file rows totalling 14 lacs, How can i combine 14 lacs rows in a single excel sheet (tab) without deleting anything. N.B all columns formats of the excel tabs are same of the seperate file.

Hi Jubayer!
Welcome to the ExcelDemy forum and thanks for sharing your problem with us. The maximum row number is 1,048,576 in a single Excel worksheet. If you require more rows than this limit, then you have to use alternative tools like Power Query.
To combine 2 tables from 2 different sheets into a single table that contains more rows than the Excel maximum row limit follow the steps below:

• Format data in each sheet into Table format. Select any cell inside the data and press Ctrl+T. Then press OK.
• Go to Data > Get Data tool under the Get & Transform Data section from any worksheet.
• Click on the drop-down menu. From the available options, choose From Other Sources > Blank Query.
• The Power Query Editor will open. In the Formula bar, write this formula:
  =Excel.CurrentWorkbook()
  Power Query is case-sensitive. So write the formula as it is.
• Press Enter. You will find the two tables from the two worksheets arranged one by one. Select them and click the small right arrow beside the title Content.
• You will get a small box. Click on Expand and then check (put a tick on) all the boxes.
• Click OK. You will find all the items from two tables brought to a single table in Power Query Editor.

For more detailed procedure please go through these 2 articles:
Excel Limits: Know About Specifications and Limitations
How to Pull Data from Multiple Worksheets in Excel (4 Quick Ways)

Regards,
Md Nafis Soumik
ExcelDemy