Dear Readers,

I am back with another Excel Trick which is very much common while managing your sheet with certain security (protection). To protect the whole Worksheet can be easily done by using the Protect Sheet option available in Microsoft Excel. But in this article I am going to explain you, How to protect your whole sheet except a cell or cell range.
One of my avid reader Avinash Dubey wanted me to post an article on Disabling/protecting rest all the cells which are unused.

Hey Vishwa,
Background is changed as per the second method but I want unused space disabled. I mean, the cells where user has provided the data can be formatted and rest of the cells (unused) should be white and disabled.
Please post this topic.

Here I am going to put all possible ways of disabling or preventing users from accessing unused cells in your worksheet.

Protect whole Sheet except the range you want to allow user to edit

You might be knowing about Protect Sheet option available in Excel in Review Tab . Using this option you can protect your whole Worksheet by providing a password (Passing a password is not mandatory). It does not work only on selected area rather it protects the whole sheet.
Now the question is how to protect all sheet except the area which you want user to be able to edit Follow the below steps to achieve this:

Step 1.

Select the Cell or Cell Range you want user to be able to edit

Step 2.

Right click on Selected Cell or Cell Range and click on Format Cells
How to Format Cells

Step 3.

Go to Protection Tab and Un-Check the checkbox shown in the below image
Uncheck the Checkbox - Locked

Step 4.

Now go to Review Tab and Click on Protect Sheet
Protect-sheet Option

Step 5.

Now Enter a password to protect the sheet and click Ok

Step 6.

Now re-enter the password and click on Ok

Now your whole worksheet is protected except the areas you selected to change the formatting above

You are done now !! Your whole sheet is protected except the area you wanted users to edit. Now no one can edit the sheet beyond the cells you provided an access to. Then go and Enjoy your freedom πŸ™‚ your sheet is protected now πŸ™‚

If you too have any Excel Tip to share or Want an Article to be published, let me know by putting your tips and suggestions in comment


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!