How to Protect Workbooks, Worksheets and Cells in Excel: 3 Ways to Restrict Access to Important Data

AUTHOR:  JASON MORRELL 

Jason Morrell

Excel offers 3 levels of protection. You can protect workbooks with 2 different passwords. You can protect worksheets by restricting permissions and access to particular sections. Finally you can protect cells (also known as locking cells).

Locking down important data is a sensible, and often necessary precaution that you should always consider to avoid malicious destruction or accidental loss of your data.

In this post you'll see how these 3 jigsaw pieces work seamlessly together. 

1. What protection is available?

Having designed the ultimate workbook and set it up ready for people to use, the last thing you want is for your people to delete important cells or reformat things. Excel provides protection for the author/designer which restricts what users can do.

There are 3 levels of protection available. You can:

  • Lock / Protect Cells
  • Protect Worksheets
  • Protect Workbooks

2. Protect cells

There are two steps that are required to protect a cell.

1.  Lock the cell.

2.  Protect the worksheet to activate the cell locks.

To lock the cell:

1.  Highlight the cells to be locked.

2.  Press Ctrl 1 to display the Format Cells dialog box.

3.  Click the Protection tab.

4.  Tick the Locked box. Take a note of the information message provided in the dialog box.

Protect cells by locking them

3. Protect worksheets

To protect worksheets:

1.  Select the Review ribbon.

2.  Click the Protect Sheet icon.

3.  Select what users are allowed to do with protected (locked) cells. For example, you may want to allow users to select a locked cell but NOT format it, or insert extra rows or columns). If you are not sure, the top 2 options (select locked and unlocked cells) is a good default choice.

4.  Enter an optional password. This will be used to unprotect the worksheet in the future. You will be prompted to confirm your password.

Protect worksheets and set permissions to specify what users are allowed to do with protected sheets

4. Unprotecting worksheets

To un-protect worksheets:

1.  Select the appropriate worksheet.

2.  Select the Review ribbon if necessary.

3.  Click the Unprotect Sheet icon.

4.  Enter the password, if one was set.

Unprotect worksheets on the Review ribbon

5. Protect workbooks

1.  Click the File tab then Save As then Browse to display the Save dialog box.

2.  Click the Tools menu next to the Save button.

3.  Choose General Options.

4.  Here, you can enter 2 passwords – one to open the file and one to modify the file – depending on what you want users to be able to do.

Password protect workbooks - one password to open and another password to modify

6. Key Takeaways

  • There are three levels of protection - you can protect workbooks, protect worksheets or protect cells.
  • Cell protection does not become active until the sheet is protected.
  • To protect cells (called locking), select the cell(s), display the Format Cells dialog box and go to the Protection tab. Tick or untick the Locked box. Lock cells that you want to protect. Unlock cells that you want users to have access to.
  • To protect the sheet, click the Format icon on the right side of the Home ribbon, then choose Protect Sheet. Assign a password if you wish and choose what user can do on the protected sheet.
  • To unprotect the sheet, click the Format icon on the right side of the Home ribbon, then choose Unprotect Sheet
  • To password-protect a workbook, click the File tab, choose Save As, then click the Tools button at the bottom and select General Options. Assign passwords to open or modify.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
How many rows and columns does Excel have?
Read More
Creating New Custom Excel Templates
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>