PROTECT an Excel 2016 workbook

Image of blog post and discussion

You may want to share a Microsoft Excel workbook, but want or need to:

  • Hide or protect data;
  • Hide or protect your workings;
  • Make it easier for users to navigate around the Excel workbook with less clutter;
  • Prevent unwanted changes to your Excel file, to the data and/or the workbook structure.
Top tips
  1. If you want to protect a whole Excel workbook or individual sheet, without another user being able to undo the protection, you’ll need a password.
    • It helps to have the password saved in a text file, so that you can copy and paste when applying and undoing the protection.
    • There’s no risk then of forgetting the password or typing it incorrectly.
    • You can use the same password to protect individual sheets and the entire workbook.
  2. Keep a working copy of a file without protection, just in case the password protection does go wrong.
    • This will give you a backup.
    • Plus if you need to do more work on the file, it avoids having to keep switching on and off the protection.
  3. If there are interactive elements in the worksheet, e.g. pivot table slicers, these elements will need to be unlocked before the worksheet is protected.
Sequence of steps
  1. Unlock any interactive elements such as pivot table slicers.
  2. Hide sheets that you don’t want users to view (e.g. data sheets). You can also optionally protect them beforehand for belt and braces, but if the workbook structure is protected, the hidden sheets can’t be unhidden.
  3. Protect the worksheets that you want to be visible.
  4. (Optional) Deselect the file option to Show sheet tabs. You would need to have some kind of alternative navigation method e.g. using buttons/links if the workbook contains more than one worksheet, as the sheet tabs won’t be visible to users.
  5. Protect the workbook structure.
Hiding sheets
  • Hide an Excel workbook sheet by right-clicking on its tab at the bottom of the window, and select Hide sheet.
  • To unhide, right-click any tab, select Unhide, and select the sheet you want to unhide.
  • Any user can unhide worksheets, unless the workbook is password protected.
Protecting sheets
  • With password protection:
    1. On the Excel worksheet to be protected, from the Home tab on the ribbon, select the Format | Protect worksheet option.
    2. Enter your password; you will be prompted to enter it again.
    3. Click OK.
    4. To remove protection, follow the steps above, selecting Format | Unprotect sheet. You are prompted for the password.
  • Without password protection
    • To protect a worksheet so that it can be unprotected by any user, follow the steps above, but miss out the step to enter a password.
    • The purpose of this would be to prevent unintentional changes to a worksheet, but still allow users to have full access if they required it.
Switch off Show sheet tabs in options
  1. For this option you would need to have a navigation method for users to access all worksheets (e.g. buttons or links) if there is more than one worksheet in the workbook.
  2. Select File | options
  3. Select Advanced
  4. Scroll down to Display options for this workbook, find the option Show sheet tabs, and clear the radio button.
  5. When you return to the document view, you will see that the sheet tabs are no longer displayed along the bottom of the window.
  6. This action can be reversed by any user, even if the workbook structure is password protected. However, hidden sheets cannot be unhidden.
Protecting (and unprotecting) Excel workbook structure
  1. In the File menu, select Protect workbook structure.
  2. Enter your password when prompted.
  3. To unprotect the workbook, again select Protect workbook, and enter the password to unprotect. (This is slightly confusing if one is looking for the option to UNprotect the Workbook!)