Hide Formulas & Contents in Formula Bar

Many a times, I create complex formulas and I don’t normally want my novice users to see them. That’s may be because I am afraid that they may mess up those formulas or I just want to keep the recipe of the formulas to myself (secret?). Whatever is the reason, this is what I do in that case.

If you read our Excel’s protection features post, you are already aware of what I am going to say below.

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected.

Select the cells where formulas / contents to be hidden in formula bar. In the Format cells dialogue box (Ctrl+1), under Protection tab, make sure check box Hidden is checked and click OK. Then go to Tools > Protection > Protect Sheet and hit OK (In Excel 2003) or Review > Protect Sheet and hit OK (Excel 2007 and later).

This makes the result of the formula visible in the cell and formulas itself are not visible in formula bar. Users can un-protect the sheet and see the content in formula bar. You can use this feature to hide contents of the cells too in formula bar.

If you don’t want user to un-protect without a password, you can password protect these sheets. You can read more on the protection features here.