Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to limit number of rows and columns in a worksheet in Excel?

When sharing a range of worksheet with others, you may want to prevent others from viewing other rows and columns. Here I will introduce some tricky ways to limit number of rows and column in a worksheet in Excel easily.

Limit number of rows and columns with disabling scrolling in Excel

Limit number of rows and columns with hiding rows and columns

Limit number of rows and column with Kutools  

One click to hide everything but selected range (hide unused column/rows/cells)

Most of time, we may only use part of worksheet with numeric blank cells/range left. Kutools for Excel’s Set Scroll Area utility can help you one click to hide everything except selected range, or hide all unused cells/rows/column/ranges easily. Full Feature Free Trial 60-day!
ad set scroll area 2

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

arrow blue right bubbleLimit number of rows and columns with disabling scrolling in Excel

In Excel, users can set scrolling area to prevent from scrolling to other rows and columns.

Step 1: Right click the worksheet name in the Sheet tab bar, and then select the View Code from the right-clicking menu.

Step 2: Now you get into the Microsoft Visual Basic for Applications window, you need to open the Properties Window with clicking View > Properties Window.

Note: If the Properties window has been opened already, just skip this step.

Step 3: In the Properties window, find out the ScrollArea in the left bar, and then in the following box enter the range you want to share with others.

Step 4: Close the Microsoft Visual Basic for Applications window.

Then you will see both vertical scroll bar and horizontal scroll bar are freeze, and you cannot scroll to other rows and columns in current worksheet.


arrow blue right bubbleLimit number of rows and columns with hiding rows and columns

Apart from setting scrolling area, we can also limit number of rows and columns by hiding unshared rows and columns in Excel.

Step 1: Besides the shared range, you need to select the left ranges and hide them. Select the first column right to the shared range, and then press the Ctrl + Shift + Rightward arrow keys simultaneously to select all right columns.

Step 2: Right click selected columns, and select the Hide from right-clicking menu.

Step 3: Select the first row below shared range, press the Ctrl + Shift + Downward arrow  keys at the same time to select all rows below shared range, and then hide them.

Step 4: Select the first row up shared range, press Ctrl+ Shift + Up arrow simultaneously to select all rows up shared range, and then hide them.

Step 5: Select the first column left to shared range, press Ctrl + Shift+ Leftward arrow  simultaneously to select all columns left to shared range, and then hide them.

Then you will see all rows and columns are hidden except shared range in the worksheet.


arrow blue right bubbleLimit number of rows and column with KTE's Set Scroll Area utility

The hotkeys seem not easy for many Excel users. Do not worry about it, you can try Kutools for Excel's Set Scroll Area utility, which helps you hide all rows and columns except shared range with only one click in Excel.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Select the shared range you want to share with others, and click the Kutools > Show / Hide > Set Scroll Area.

ad set scroll area 2

Then you will see all rows and columns are hidden except shared range in the worksheet immediately.

 Set Scroll Area is one of Kutools for Excel utility, which is designed to one click to hide all unselected rows and columns in active worksheet easily. Click for 60-day free trial, no limitation!


arrow blue right bubbleDemo: Limit number of rows and column with only one click

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!

Toggle to show/hide columns: one click to show all hidden columns, and one click to restore hidden columns

Kutools for Excel's (Show) Columns utility is a good toggle to show all hidden columns with only one click, and click this Column button for the second time will make these columns hidden again. Full Feature Free Trial 60-day!
ad unhide hidden columns 1


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.