Sudipta Chandra Sarker
In this article, we will learn to limit sheet size in Excel. Assume we’ve already created a data set. We may want to limit the size of the Excel sheet to keep the focus on the data set or to reduce the size of the file. Limiting sheet size may also assist in removing unwanted data and focusing only on the desired data set. Today, we will show 3 suitable ways. Using these methods, you can easily limit sheet size. So, without any delay, let’s start the discussion.
How to Limit Sheet Size in Excel: 3 Suitable Ways
In the following dataset, we have student information for a class. Here, we have information about the roll numbers, names, and marks in Mathematics, Physics, and Chemistry. We will show 3 methods using this dataset to limit sheet size in Excel.
1. Hide Rows and Columns to Limit Sheet Size in Excel
We can easily hide our desired rows and columns, as well as specific cells, by using the Format option in Excel. Let’s follow the steps below to learn the method.
STEPS:
- First of all, you need to make the dataset ready.
- To do so, insert the data in an Excel sheet.
- Here, we have used the range B4:F14 to insert the student information.
- Secondly, we have to select from where we want to hide our column.
- We have information from Column A to Column F.
- Select any cell of Column G and press the Ctrl + Shift + Right Arrow (→) keys simultaneously to select all right columns.
- We have successfully selected Columns G to XFD.
- Thirdly, go to the Home tab and select the Format option.
- A drop-down menu will appear.
- Now, select Hide & Unhide to open another drop-down menu.
- Click on the Hide Columns option from there.
- As a result, Excel will hide the columns from G to XFD.
- Now, in the same way, we have to hide the unexpected rows.
- As our dataset ends in the 15th row, we need to hide the other rows in the Excel sheet.
- So, select any cell of the 16th row, and press the Ctrl + Shift + Down Arrow (↓) keys simultaneously to select all down rows.
- We have successfully selected rows 16 to 1048556.
- In the following step, move to the Home tab and select Format.
- After that, select Hide & Unhide >> Hide Rows.
- We can see that the rows outside of the 15th row become invisible.
- Finally, we will be able to limit sheet size like the picture below.
2. Apply Excel VBA to Restrict Sheet Size
We can easily hide our desired rows and columns, as well as specific cells, by using the VBA code in Excel. Let’s follow the steps below to learn the method.
STEPS:
- First of all, you need to make the dataset ready.
- To do so, insert the data in an Excel sheet.
- Here, we have used the range B4:F14 to insert the student information.
- Secondly, from the Developer tab, select Visual Basic.
- A new window will open up and we have to select Module from the Insert tab.
- This will open the module where we will write our code.
- Now we need to use a VBA code.
- In the Module window, type the code below:
Sub Limit_Sheet_Size()
Range("G:XFD").EntireColumn.Hidden = True
Range("16:1048576").EntireRow.Hidden = True
End Sub
- Now, we need to save the code first and then, run it.
- To save the code, simply hit Ctrl + S on the keyboard.
- After that, press the F5 key to run the code.
- Otherwise, we can simply click the “run” button shown below after saving.
- Finally, after running the code, we will be able to limit the Excel sheet size.
3. Utilize Properties Window to Limit Sheet Size in Excel
In this method, we can use the Properties Window option to limit the sheet size in Excel. Let’s follow the steps below to learn the method.
STEPS:
- First of all, you need to make the dataset ready.
- To do so, insert the data in an Excel sheet.
- Here, we have used the range B4:F14 to insert the student information.
- In the second step, go to the Sheet Tab and right-click on the Sheet Name to open the menu.
- Select View Code from there.
- As a result, a window named Microsoft Visual Basic will open up.
- In the Visual Basic window, go to the View tab and select Properties Window.
- Alternatively, we can simply press the F4 key on the keyboard to open the properties menu.
- In the properties menu, find out ScrollArea and type the range you want to keep.
- As our dataset is in the range A1:F15, we will type $A$1:$F$15.
- Now, press Enter.
- We can see the rows outside of the 15th row and the columns outside F are still visible.
- But we can not put our cursor here and we do not have access over that range.
- Finally, using the above steps we can limit the sheet size in Excel.
Download Practice Workbook
To practice by yourself, download the following workbook.
Conclusion
In this article, we have demonstrated how to limit Excel sheet size in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
Related Articles
- How to Open New Sheet in Excel
- How to Copy a Sheet in Excel
- How to Group Worksheets in Excel
- How to Delete a Sheet in Excel
- How to Unhide Multiple Sheets in Excel
- How to Change Worksheet Tab Color in Excel
- How to Change Excel Sheet from Right to Left
- How to Flip Excel Sheet from Left to Right
- How to Split Screen in Excel
