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.
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:
Select the Cell or Cell Range you want user to be able to edit
Right click on Selected Cell or Cell Range and click on Format Cells
Go to Protection Tab and Un-Check the checkbox shown in the below image
Now go to Review Tab and Click on Protect Sheet
Now Enter a password to protect the sheet and click Ok
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 🙂