There are a couple of ways to restrict persons using your Excel worksheet to a specific range. Why would you want to do this? Well the first thing that comes to mind is that you only want to allow data input in one specific area to prevent unwanted changes in your worksheet. I am sure you can come up with some other reasons as well.
One way to accomplish this is to use the Data Validation feature in MS Excel, setting the entire sheet to accept nothing and then resetting the input range to accept anything. It is quick and it works quite nicely.
Another method is to use the Allow Users To Edit Ranges option.
Yet another method is to use Protection.
The above are all good methods to use and will certainly get the job done for you, but there is another feature that I personally like to use – setting a Scroll Area. This particular method not only restricts data input, it also keeps users from scrolling beyond the specified area in your worksheet.
Follow the steps below to learn how:
- Display the Control Toolbox toolbar by clicking on View | Toolbars | Control Toolbox or simply right-click an empty area on a toolbar and select Control Toolbox.
- Click the Properties button on the Control Toolbox.
- In the ensuing Properties window, enter the input range as the Scroll Area setting.
- Close the Properties Window and close the Control Toolbox toolbar.
Setting the scroll area as shown above, will limit data input to only C3:E12. In addition, users cannot use arrow keys or the scroll bar to move beyond that area on the screen. Changing the Zoom factor will allow users to see a larger area of the sheet, but that will only get them so far.
It is an obscure feature and so most users will not know aobut it or how to undo it. Please take note however, that those users who do know about the feature might undo it and so this is not by any means a security feature.