How to Limit Sheet Size in Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to limit excel sheet size


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.

Hide Rows and Columns to Limit Sheet Size in Excel

  • 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.

Hide Rows and Columns to Limit Sheet Size in Excel

  • As a result, Excel will hide the columns from G to XFD.
  • Now, in the same way, we have to hide the unexpected rows.

Hide Rows and Columns to Limit Sheet Size in Excel

  • 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.

Hide Rows and Columns to Limit Sheet Size in Excel


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.

Apply Excel VBA to Restrict Sheet Size

  • 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

Apply Excel VBA to Restrict Sheet Size

Here, we are selecting the range of rows and columns we want to hide. As in the datasheet, we have 1048576 rows and XFD columns; we will hide columns from G to XFD and hide rows from 16 to 1048576. In the code, “Entire.Hidden=True” is used to hide the range and “Entire.Hidden = False” is used to unhide the cells.
  • 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.

Apply Excel VBA to Restrict 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.

Utilize Properties Window to Limit Sheet Size in Excel

  • 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.

Utilize Properties Window to Limit 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


<< Go Back to Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo