MVP #7: How to prevent your client from changing some cell contents/formula in your Excel sheet

, , No Comments
I recently customized a Family Budget template for a client. It has all major expense categories and tracks monthly expense against budget. It, also, has a year trend chart showing Expense month by month. Below is a snapshot -

There is one cool trick I included in this Family Budget Excel dashboard.
It has a lot of formulas and I didn't want the client unintentionally deleting those formula cells. I wanted him to be able to edit only the cells that required his entry/update.

And in this post I will show you just how I did that.

I do CTRL + A twice to select the entire sheet

I rightclick anywhere, and select Format Cells.

I go to Protection Tab, and tick Locked. Leave Hidden unticked. Don't forget to click OK.

Now I select only the Cells I want the Client to edit/update.

Again, I rightclick and select Format Cells.

I go to the Protection tab, and untick Locked.

I do CTRL + A twice to reselect the entire sheet (not necessary, just too lazy to remake the screenshots). And I click on the Review Menu and Select Protect Sheet.

I tick Select locked cellsSelect unlocked cellsInsert Columns and Insert Rows. Ordinarily, the first two should do, but I want the client to be able to add new expense type.

Now it's done.
When the client tries to edit a part he is not meant to, he gets an error notification.

So that's all there is to protecting certain cells in Excel.

If you have anything you'll like me to help you on, feel free to reach me via the comment box. I could also make a Monthly Budget sheet for you.


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.