Row Limit in Excel (Table of Contents)
Row Limit in Excel
Excel has many version right from 2013 to 2016 which is the latest version of Microsoft. Every version of excel has its own number of rows and number of columns. Until excel 2007 the maximum number of rows is 65000 rows. In Excel 2007 and later versions, it has been increased to 1 million rows i.e. more than 10 lakh rows. The maximum number of columns is 16000 columns. Unless you are working for a data science company not very often we use all the rows in the excel spreadsheet. In excel we limit those rows and columns access to the users or control the spreadsheet to show the number of rows and columns. If any user comes and you want to restrict their access to a specific area we can do this as well. There are two methods where we can do this task. Follow this article completely to explore this option.
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.
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 –
