Hiding Sensitive Worksheet Data

In addition to protecting locked cells from editing changes when the worksheet is protected, you can also ensure that their contents do not show up on the Formula bar when their cells contain the cell pointer. You might add this, for example, to cell ranges that contain long, complex formulas so that they don't clutter the Formula bar by adding to the busyness of the screen when you move the cell pointer through their cells. (Let's face it. Because they're protected from changes, there's really no reason to look at them anyway.)

To suppress the display of a cell range from the Formula bar, select the range, open the Protection tab of the Format Cells dialog box (Ctrl+1), select the Hidden check box, and click OK. Then turn on the worksheet protection (Tools → Protection → Protect Sheet).

Don't ever select the Hidden check box on the Protection tab to hide a range of formulas when the Locked check box is not also selected. If you do, you're enabling users to replace or delete a cell's contents right at the time that its contents no longer appear on the Formula bar.

Sometimes your spreadsheet may contain sensitive data that isn't proper for certain data-entry operators to see, either on the Formula bar when the cell pointer is in their cells or in the worksheet itself. For those cases, you not only need to assign the Hidden protection format to these cell ranges (as described above) but also need to apply a custom number format that hides the cells in the worksheet itself.

Be sure to lock any cell ranges that you render completely invisible in the worksheet by applying the Hidden protection format that hides their contents on the Formula bar and a custom number format that hides their display in the worksheet. This prevents users from making changes to these cells that now appear for all intents and purposes to be blank.