Hands on: Keep your spreadsheets private

Sheet by sheet
You may not object to anyone modifying some of the worksheets in one of your workbooks, but there may be some over which you wish to limit access. If you have a worksheet in an Excel workbook that you wish to keep to yourself, the first level of confidentiality is simply to hide it ­ – just right-click the worksheet’s tab and choose ‘Hide’. Unfortunately, this adds Unhide to the right-click menu, which probably wouldn’t mean much to a beginner but any serious user would click on it right away.

To move down the levels of protection, you need to become familiar with Excel’s traditional approach. This example uses Excel 2007. All versions have the same methodology –­ it’s just that the latest version makes it easier to work with.

All cells in a worksheet have two protection states ­ – locked or unlocked, and hidden or visible ­ – although Microsoft has its own vocabulary and calls visible cells unhidden. (It also asks you to uncheck boxes at times.) Actually, it’s not the result displayed in a cell that’s hidden but any formula that would normally appear in the Formula Bar or by choosing the cell and pressing F2.

If you highlight a range of cells, press Ctrl & 1, and click the Protection tab in that dialogue box, you can choose to make those cells locked (meaning nobody can change their contents) or unlocked, as well as hidden or visible.

Oddly, perhaps, this has no effect until you protect the worksheet. But equally, before you protect the worksheet you need to go through this procedure. This is why I’ve talked about cell protection before worksheet protection. Now you can click the Review tab and the Protect Sheet button. In any version before Excel 2007, choose Protection, Protect Sheet on the Tools menu.

In the latest versions you have a wide range of options for limiting access to the particular worksheet here. Beyond stopping people altering locked cells or viewing formulas in hidden ones, you can forbid inserting or deleting rows or columns, editing graphics or scenarios, or using PivotTable reports.

The option I particularly like is the one that stops other people sorting your tables. That can save a lot of grief. Just remember that if you want to refuse permission to do any of the listed actions, don’t check the relevant box: checking the box means any user is allowed to do the action.

Finally, we get to protecting the worksheet. At the top of this Protect Sheet dialogue box, you can enter a password only you and your trusted loved ones know. Everyone else can only make the changes you allow them to make.