Tuesday, January 6, 2009

Hide and Seek with Excel

Sometimes when I work in Excel, I have some formulas which I want to keep away from people viewing the document so that they don't accidentally or intentionally change the values. This protection is only on the cell level and not for the whole workbook.

This is my excel sheet with a simple table that calculates the interest based on the principal, rate, and duration. I have clicked on the highlighted cell so that you can have a look at my formula.
First, we should set the protection for the cell, select the range of cells and right-click. Select Format Cell.
In the Protection tab, check Lock and Hidden.

From the ribbon, click on the Review tab, and in the Changes group, select 'Protect Sheet' so that cell range we selected just now will be locked and the formula hidden. You can also specify a password here, but I have chosen not to for this example.

Now when I click on the highlighted cell, the formula is not visible.

Here's what happened when I tried to edit the cell :

To remove the protection, just turn it off by clicking on 'Unprotect Sheet'. If a password was specified initially, then there will be a prompt first before the protection is turned off.
Next time you have cells with sensitive values or formulas, protect the values by locking and hiding the values.

No comments:

Post a Comment