August 11

0 comments

How to Protect an Excel file with password (3 quick ways to protect your work)

By Sharyn Baines

August 11, 2021

Password, Protect, Protecting, Unprotect

Are you wanting to password protect an Excel workbook or worksheet that you have been working on so that other users cannot make changes?

Maybe you are wanting to protect cells from being edited and are searching for a way in which to lock them so that they can’t be modified by others.

If this sounds like you, you’re in the right place!

In this blog, I will cover how to

  1. Password protect only specific cells and ranges in a worksheet to protect them from being changed.
  2. Password protect an entire workbook to prevent changes to the workbook structure, e.g. worksheets from being deleted, copied or renamed.
  3. Password protect an Excel file so that a password is required to open or modify the file.

By protecting a selection of cells, the full worksheet or entire workbook you can feel confident you have minimised the risk of unwanted changes happening.

Before we get started let’s just clarify the difference between a worksheet (sheet) and a workbook (file).

In Excel, a worksheet is a single sheet tab, and a workbook is the collection of one or more worksheets. A workbook is also referred to as a spreadsheet file or an Excel file. 

How to Protect an Excel file with password

The following instruction covers worksheets, and workbooks (files).

1. How to password protect a sheet in Excel 

One of the best ways to protect specific cells from being changed in a worksheet in Excel is to Protect the worksheet

Protection allows you to lock cells, therefore preventing your key data and calculations from being over-typed, or worse, deleted.

Protecting a worksheet in Excel is a two-step process. The first step you need to take, before you protect a sheet, is to unlock any cells that you will want to modify once you protect the sheet. The second step is to protect the worksheet.

If the first step is overlooked, and protection is turned on then all cells will be locked (unable to be changed).

It is unlikely that you would need to lock all cells for editing in an Excel workbook, as this would mean no changes could be made to any cell.

Therefore, before you apply protection, you must first format the cells you want to be unlocked (able to be changed) once the worksheet is protected. This step is called ‘unlock cells’.

Note: Before you choose to apply protection to a worksheet or workbook in Excel, please understand that it isn't bullet-proof and that others can access the data if they have a mind to. For more information, please read my blog Don't be fooled - Excel Worksheet Protection isn't bullet-proof.

Step # 1 How to Unlock cells in Excel

Step 1: Select the cells that you want to be able to continue to edit once protection is active. In the example below I have selected cells B5 to F5. These cells will remain editable once protection is turned on.

How to Protect an Excel file with password

Step 2: From the Home tab, in the Cells group, click the Format option.

How to protect an Excel file with password

Step 3: You will see the Lock Cell option near the bottom of the menu. Note that it has a box around the lock icon. This indicates that the selected cells are currently locked. Click Lock Cell to unlock the selection.

How to Protect an Excel file with password

The selection will now be formatted as unlocked. Once the worksheet is protected these cells will remain unprotected.

Step # 2 How to protect a sheet in Excel

Step 1: To password protect a sheet in Excel, do one of the following:

  • Option 1: From the Home tab, in the Cells group, click the Format option and then select Protect Sheet.
How to Protect an Excel file with password
  • Option 2: From the Review tab, in the Protect group select Protect Sheet.
How to Protect an Excel file with password
  • Option 3: Right-click the sheet tab and then select Protect Sheet.
How to Protect an Excel file with password

This will open the Protect Sheet dialogue box.

Step 2: In the Protect Sheet dialog box, type a password to protect the worksheet.

How to Protect an Excel file with password

Note: A password is optional. However, be aware that not using a password can lead to your sheet being easily Unprotected and your data modified. If you do not require a password leave the password text box empty.

Step 3: In the Allow all users of this worksheet to option box select suitable options for your worksheet. For example, if you would like users to be able to insert new rows, place a check on the Insert rows option.

Tip: it is a good idea to remove the check from the Select locked cells option. This removes the ability for users to click on any cell other than unlocked cells.

Step 4: Click OK.

How to Protect an Excel file with password

Step 5: If a password was applied Excel now asks you to confirm your password by re-entering it in the Confirm Password dialog box.

Step 6: Click OK

How to Protect an Excel file with password

Step 7: Once the sheet is protected the Protect Sheet button on the Review tab will now read Unprotect Sheet.

How to Protect an Excel file with password

Your document will now be protected and only the cells that you selected to be unlocked will be able to be changed.

Note: Excel offers a caution not to forget your password, as the password cannot be recovered if forgotten. Please note that passwords are case sensitive; so, if you typed the password in UPPERCASE you will need to always type it in UPPERCASE to unprotect the file.

2. How to Password Protect a Workbook in Excel

Use the Protect Workbook option when you do not want other users to be able to change your workbook structure. 

For example, you may not want users to be able to delete, add or unhide worksheets in the workbook, or you may have the workbook displayed with worksheets arranged in a way that you do not want changed.

In the example below, the Jun, Jul and Aug worksheets have been arranged in order. If we didn’t want anyone moving, deleting, or renaming these sheets, we can use the Protect Workbook tool.

How to Protect an Excel file with password

To apply workbook protection, follow the steps below.

Step 1: From the Review tab, in the Changes group select Protect Workbook.

How to Protect an Excel file with password

The Protect Workbook dialog box will be displayed.

Step 2: Select the option you require:

With the Structure option selected you choose not to allow any changes to the structure of the workbook. Therefore, no worksheets can be inserted or deleted, moved, copied, hidden, unhidden or renamed. 

Note: The Windows option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac. If you select the Windows option, Excel will not allow changes to the display of the windows in the workbook. Therefore the windows cannot be moved or resized. Excel will also remove the minimise and maximise buttons, the document Control box, and the window sizing border from view.

How to Protect an Excel file with password

Step 3: Enter a password to protect the workbook. This is optional.

How to Protect an Excel file with password

Step 4: Click OK.

How to Protect an Excel file with password

Step 5: If a password has been used Excel will now ask for confirmation of the password. Re-enter the password.

Step 6: Click OK.

How to Protect an Excel file with password

Step 7: Once the workbook structure is protected the Protect Workbook button on the Review tab will now be highlighted, indicating that the workbook is currently protected.

How to Protect an Excel file with password

Your workbook will be protected from changes to the windows and structure of the workbook.

Once a workbook is protected you will find all workbook functions (i.e., delete sheet, insert sheet, rename, etc.) will be disabled on the Ribbon and shortcut menus.

How to Protect an Excel file with password

3. How to Protect an Excel file with a Password

An excellent way to protect a workbook in Excel is by changing the settings so that the file can only be opened or modified once a password has been entered.

To do this follow the steps below.

Step 1: Click the File tab and then click Save As to open the Save as dialogue box.

Note: The fastest way to open the Save As dialog box is to press Function key F12.

How to Protect an Excel file with password

Step 2: Select the Tools option then click General Options.

How to Protect an Excel file with password

The General Options dialogue box will open.

How to Protect an Excel file with password

Step 3: Now enter a password into the Password to open and/or Password to modify boxes. Once these are in place the file cannot be opened or modified without the password being entered first.

  • Password to open – this option ensures only users with the password will be able to open the file. 
  • Password to modify - in the example below I am leaving the Password to open box empty and using the Password to modify option. This means users will be given the option to open the file as a Read-only file only. However, they will not be able to modify the actual file without entering the Password to Modify password first.
  • Read-only recommended check this box to add an additional level of protection where you can ask the user to open the file as read-only. Be aware that they can over-ride this and open the original file if they have the password.
How to Protect an Excel file with password

Note: these steps encrypt your file. If your password is forgotten, you will not be able to edit your file so please remember to save it somewhere safe.

Step 4: Click OK.

How to Protect an Excel file with password

Step 5: Excel will ask you to re-enter password.

Step 6: Click OK.

How to Protect an Excel file with password

Step 7: You now need to save these changes to your file. Click Save.

How to Protect an Excel file with password

A warning should pop up asking if you want to replace the file that already exists.

Step 8: Select Yes if you are happy to save over the original file. If not, click No and give the file a new name. 

How to Protect an Excel file with password

When someone tries to open or modify the file now, they will have to enter the correct password.

How to Protect an Excel file with password

How To Unprotect Excel Sheet

To unprotect an Excel sheet:

Step 1: From the Review tab, in the Changes group select Unprotect Sheet.

Step 2: If required, type in your password to unlock the worksheet.

Note: you will need to type in the password in the same case you used when you applied the password.

Step 3: Click OK. The protection will now be removed.

If you need to unprotect an Excel sheet and you don’t have the password, please see our Blog How to Unprotect Excel Sheet and Excel Workbook.

How to Unprotect Excel Workbook

To unprotect a workbook:

Step 1: From the Review tab, in the Changes group click the Protect Workbook button.

Step 2: If required, type in your password to unlock the workbook. 

Note: you will need to type in the password in the same case you used when you applied the password.

Step 3: Click OK. The protection will now be removed.

If you need to unprotect an Excel workbook and you don’t have the password, please see our Blog How to Unprotect Excel Sheet and Excel Workbook.

How to Remove Password from Excel File

To remove a password to open or modify from an Excel file:

Step 1: First open the file. You will need to use the password to do this.

Step 2: Press F12 to open the Save As dialog box.

Step 3: Select the Tools option then click General options.

How to Protect an Excel file with password

Step 4: Delete the password or passwords. You might also like to remove the check from the Read-only recommended option box.

How to Protect Excel file with password

Step 5: Click OK.

Step 6: You now need to save these changes to your file. Click Save

Step 7: A warning should pop up asking if you want to replace the file that already exists. Select Yes if you are happy to save over the original file. If not, click No and give the file a new name. 

How to Protect an Excel file with password

My advice to you

Using a password to protect your worksheets and workbook gives that extra layer of protection so that people will not be able to make unwanted edits to your workbook or worksheets.

However, please be aware password protecting worksheets and workbooks is still not watertight. There is a lot of information on the Internet showing people hacks on how to open password protected worksheets, and how to ‘crack the password’ for an encrypted Excel file.

My advice to you is to use protection to protect other people from making unwanted changes to your files.

Here’s an excerpt from Microsoft’s support page on Protection and security in Excel.

“You should not assume that just because you protect a workbook or worksheet with a password that it is secure - you should always think twice before distributing Excel workbooks that could contain sensitive personal information like credit card numbers, Social Security Number, employee identification, to name a few.”

Please don't think of protection as security. Think of protection as just another way that you can stop people from getting into a file and making changes that you will have to fix later.

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>