Lock and Protect Your Excel Worksheets and Workbooks

by Avantix Learning Team | Updated May 31, 2021

Applies to: Microsoft Excel 2010, 2013, 2016, 2019 and 365 (Windows)

You can lock and protect an Excel workbook by adding a password using your security options. If you want to prevent users from accessing or changing a workbook, password protection can be added to your Excel file in a number of ways. The easiest option  is to use the File tab in the Ribbon. You can use password protection to add security and prevent other users from opening a workbook, changing a worksheet or worksheets or altering workbook structure.

Protect a workbook with a password

To protect a workbook with a password:

  1. Open the Excel workbook you want to protect with a password.
  2. Click the File tab in the Ribbon. The Backstage View appears.
  3. Select Info.
  4. Click Protect Workbook or Encrypt with Password and then Protect Workbook. A menu appears. Choose the desired option from the menu.
  5. Enter a password if required.
  6. Re-enter the password if required.
  7. Click OK.

Below are the options to protect a workbook:

Microsoft Excel protect workbook button training.

Preventing other users from opening a workbook

To prevent other users from opening a workbook by encrypting a password:

  1. Click the File tab in the Ribbon. The Backstage View appears.
  2. Select Info.
  3. Click Protect Workbook. A drop-down menu appears.
  4. Choose Encrypt with Password. A dialog box appears.
  5. Enter a password in the edit box. Passwords are case sensitive. Be sure to keep your password in a safe place.
  6. Click OK. A dialog box appears prompting you to re-enter the password.
  7. Re-enter the password.
  8. Click OK.

Below is the dialog box that prompts for a password:

Microsoft Excel enter password dialog.

Close and save the workbook. When a user tries to open the workbook, they will be prompted to enter the password.

Changing or removing a password to open a workbook

To remove an encrypted password to open a workbook:

  1. Open the Excel workbook with password protection. A dialog box appears.
  2. Enter the password and click OK.
  3. Click the File tab in the Ribbon. The Backstage View appears.
  4. Select Info.
  5. Click Protect Workbook. A drop-down menu appears.
  6. Choose Encrypt with Password. A dialog box appears.
  7. Delete the password in the edit box or enter a new password.
  8. Click OK.
  9. If you have entered a new password, enter the password again.
  10. Save the file.

Protecting workbook structure

If you protect workbook structure, you can prevent users from changing, moving or deleting worksheet tabs.

To prevent users from changing the structure of a workbook:

  1. Open the Excel workbook you want to protect.
  2. Click the File tab in the Ribbon. The Backstage View appears.
  3. Select Info.
  4. Click Protect Workbook or Encrypt with Password and then Protect Workbook. A menu appears. Choose the desired option from the menu.
  5. Choose Protect Workbook Structure. A dialog box appears.
  6. Ensure Structure is checked. Enter a password in the edit box. Passwords are case sensitive. Be sure to keep your password in a safe place.
  7. Click OK. A dialog box appears prompting you to re-enter the password.
  8. Re-enter the password.
  9. Click OK.

Below is the Protect Structure and Windows dialog box:

Microsoft Excel protect structure dialog box.

Save the workbook. Users will not be able to change, delete, move or unhide worksheet tabs.

Removing a password to protect workbook structure

To remove a password to protect workbook structure:

  1. Open the Excel workbook with password protection.
  2. Click the File tab on the Ribbon. The Backstage View appears.
  3. Select Info.
  4. Click Protect Workbook. A menu appears.
  5. Choose Protect Workbook Structure. A dialog box appears.
  6. Delete the password in the edit box. Passwords are case sensitive.
  7. Click OK.
  8. Save the file.

Protecting a worksheet

You can also protect a worksheet. This is a two part process. The first step is to unlock cells where you want to allow users to make changes to the data or formula:

To unlock cells:

  1. Select the cells you want to unlock.
  2. Right-click the selected cell(s) and choose Format Cells or press Ctrl + 1. The Format Cells dialog appears.
  3. Click the Protection tab.
  4. Uncheck Locked.
  5. Click OK. Nothing will appear to occur until you protect the sheet.

The Format Cells dialog box appears as follows:

Microsoft Excel Format Cells dialog box to lock and protect cells in a worksheet.

The second step is to protect the sheet using the Review tab in the Ribbon:

  1. Display the worksheet you want to protect.
  2. Click the Review tab in the Ribbon.
  3. In the Changes group, click Protect Sheet. A dialog box appears.
  4. Check or uncheck the desired options (it’s usually best to leave the first two checked). Be sure to scroll down to see all of the available options.
  5. Enter a password (you will need to set a password if you don’t want others to be able to unprotect the sheet). Passwords are case sensitive and you should keep a copy of your passwords.
  6. Enter the password again.
  7. Click OK. You will be unable to enter or change data or formulas in the locked cells.

Below is the Protect Sheet dialog box:

Protect Sheet dialog box.

Unprotecting a worksheet

To unprotect a worksheet and unlock cells:

  1. Display the desired worksheet.
  2. Click Unprotect Sheet on the Review tab in the Ribbon.
  3. Enter the appropriate password (if necessary).
  4. Click OK.

There are many more strategies for securing workbooks including creating digital signatures or using third party programs. You can also protect a worksheet in a workbook.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

More resources

How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways)

How to Convert Text to Numbers in Excel

How to Delete a Pivot Table in Excel

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to VBA (Visual Basic for Applications)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom onsite training or an online course on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

How to Convert Text to Numbers in Excel (5 Ways)

How to Convert Text to Numbers in Excel (5 Ways)

5 Ways to Convert Numbers Stored as Text to Numbers in Microsoft Excel Worksheets by Avantix Learning Team | Updated November 29, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) There are several ways to convert text to numbers in Microsoft...

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Lock and Protect Excel Worksheets and Workbooks
Article Name
How to Lock and Protect Excel Worksheets and Workbooks
Description
You can lock and protect an Excel workbook by adding a password using your security options. If you want to prevent users from accessing or changing a workbook, password protection can be added to your Excel file in a number of ways.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This