Row Limit in Excel (Table of Contents)
Row Limit in Excel
Although we have the sufficient number of rows count in excel to handle ample of data, but still, we have some limitation of the number of rows in excel which is limited to 1048576 rows in Excel 2019 and Office 365. Every workbook has limited memory allowed so that Excel can function properly without any crash. If we still have data which exceeds the 1048576 row count then we can insert multiple sheets.
Hide Rows Which You Don’t Want to Show
If you are old to excel in hiding and unhiding, this is not strange for you. The similar technique we can apply here as well. It is very rare we use more than 1 lakh rows of data set. I often don’t want to show unnecessary rows to the users. We can hide both rows and columns in excel. Follow below steps to learn this technique.
Step 1: Select the one row below where you want to display the number of rows. For example, if I want to display 1 lakh rows I will select the row after 1 lakh row. Reaching after the row of 1 lakh is not that easy. Scrolling till the 1 lakh row is not that easy. Place a cursor on the Name Box.
Now type A100001in the Name Box and Hit Enter it will take you to the row after 1 lakh row.
Select this entire row by pressing the shortcut key Shift Space.
Step 2: Now hold the Keys Shift & Ctrl > Press down Arrow it will take you till the end of the last row.
4.9 (4,504 ratings)
View Course
Step 3: Right click on the column header and select hide option.
Pro Tip: We can also press Ctrl + 9 which is the shortcut key to hide selected rows.
Step 4: It will hide all the selected rows and display only 1 lakh rows.
Limit the Scrolling Area
In a general spreadsheet, the user can move around anywhere in the worksheet. However, sometimes we need to restrict the access of the user to specific areas.
We can do this by using the VBA coding technique.
Step 1: Open the Workbook you want to restrict.
Step 2: Press ALT + F11 which is the shortcut key to open the VBA editor. It will show the VBA Editor window.
Step 3: Right-click on the worksheet where you want to restrict the user access. In my case, I am selecting Sheet 1. Right-click and select View Code it will enable the VBA code space.
Step 4: Select the Sheet1 and press F4, which will open the Sheet Properties window.
Step 5: Find the ScrollArea in this toolbox.
Type the area (range) in the worksheet you want to give them access to. I have mentioned A1 to G20 in the below image.
Step 6: Close the VBA Editor window and Save the workbook. Now the user cannot move beyond the selected cells. We have limited the access of rows to the users.
Now test your worksheet. You cannot place your cursor other than the yellow colored area.
Things to remember about Row Limit in Excel
- The shortcut key to hide rows is Ctrl + 9. Use horizontal number keypad.
- We can specify any type of range in the scroll area to limit the access.
- Once the rows are hidden we can unhide at any point in time.
- In the latest version of the excel, maximum of 1,048,576 rows by 16,384 columns are available.
Recommended Articles
This has been a guide to Row Limit in Excel. Here we discuss the Row Limit in Excel and how to use the Row Limit in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
