Protecting Workbooks in Excel (And Worksheets, and Cells)

The Protecting Workbooks in Excel tutorial is suitable for users of Excel 2010/2013/2016/2019 and Microsoft 365.  

Objective 

Protect Workbooks, Worksheets and Cells in Excel to help maintain the integrity of the spreadsheet and reduce errors. 

Protection Explained 

Imagine the scenario. You have spent weeks putting together a complex spreadsheet ensuring that the formulas work, it’s easy to update and nicely formatted. You share the spreadsheet with your team, and before you know it, mistakes have crept in, formulas have been accidentally edited or deleted, and links are broken. It is incredibly frustrating and creates unnecessary work for you as you attempt to rectify the mistakes.  

This can be avoided by protecting the worksheet. Not only can you protect the worksheet, but the entire workbook if you so desire. If people need to be able to edit certain cells or specific areas, that is no problem. You can define which parts of the workbook, worksheet, or cells you protect.  

Method 

Protecting the Entire Workbook 

You have three options when it comes to protecting the entire workbook, including all worksheets contained within.  

• You can encrypt it with a password to limit who can open it.  
• You can make the file open as read-only by default so that people must opt into editing it.  
• You can protect the structure of a workbook so that anyone can open it, but they need a password to delete, rearrange, rename, or create new worksheets.  

Encrypt with a Password 

The highest level of protection is to encrypt the workbook with a password. On opening the workbook, it will prompt for the password and prevent anyone who does not have the password from accessing the file.

NOTE: Ensure that you make a note of the password. Once set, the password is not held anywhere and cannot be recovered if you forget it. 

1. Click on the File menu 

2. Click on the Info tab 

3. From the Protect Workbook drop-down, select Encrypt with Password 

4. Enter a password to encrypt the contents of the file  

5. Confirm the password when prompted 

Now, when you re-open the spreadsheet, you will be prompted to enter the password.  

To remove the password protection from a workbook, open the workbook (which will require you to enter the password) and take the same steps you took to set the password. However, remove the password from the box, so it is blank and click ‘OK.’  

Open as Read-Only 

Your second option is to make a workbook read-only. The process is the simplest, but be aware that it provides the least amount of protection. Anyone can still open the workbook and just click ‘Enable Editing’ to make changes. It is best to look at this option as more of a warning to the person opening the file.  

1. Click the File menu 

2. Click the Info tab 

3. From the Protect Workbook drop-down, select Always Open Read-Only 

Now, whenever you or anyone else opens the workbook, a warning bar will display across the top advising that the author has set the file to read-only to prevent accidental changes.  

4. Click the Edit Anyway button to proceed with making changes.  

Protect a Workbooks Structure 

The third way you can protect a workbook is by protecting the underlying structure. For example, without the password, a user cannot move, rename, delete, rearrange, or create new worksheets in the workbook. However, they can still make changes to the data in the workbook.  

1. Click the File menu 

2. Click the Info tab 

3. From the Protect Workbook drop-down, select Protect Workbook Structure 

4. Type in a Password and click OK .

Now, if you right-click on the worksheet tab, the options that allow you to change the structure of the workbook such as rename, move, or copy, delete, insert are greyed out.  

If someone knows the password, they can go to the Review tab, click the Protect Workbook button, and enter the password to make those options accessible again.  

Protecting a Worksheet 

You can also protect an individual worksheet from editing. When you protect a sheet, Excel locks all cells and prevents them from being edited.  

1. Click the Review tab 

2. Click Protect Sheet 

3. Type a password 

At this stage, you can select actions that you want to allow. In this example, I am going to allow users to make select cells and format rows and columns.  

4. Click OK 

5. Re-enter the password to confirm 

To unprotect a worksheet, click the Review tab and select Unprotect Sheet.  

Protect Specific Cells from Editing 

Sometimes you might want to protect only specific cells from editing. For example, you might have a worksheet containing a mixture of input cells and formulas. You might wish for others to input figures into the input cells but lock the cells containing the formulas.  

In this example, I want users to be able to change the cell containing the VAT rate, but not the formulas contained in the other cells.  

1. Select all the cells you DO NOT want to be locked  

2. Right-click and select Format Cells 

3. Click the Protection tab 

By default, ALL cells in Excel are locked. The reason they do not appear to be locked when working in a spreadsheet is that the locking does not take effect until the spreadsheet is protected. Therefore, you will see a checkmark in the ‘Locked’ box.  

4. Uncheck the Locked box 

5. Click OK 

Now, the selected cells are essentially unlocked and all other cells in the spreadsheet are locked. To enforce this, you need to protect the worksheet.  

6. Click the Review tab 

7. Click Protect Sheet 

Like what you see? Check out these links for more examples relating to protecting workbooks, worksheets, and cells.  

Microsoft – Protect a Worksheet 

Envatotuts+ – How to Protect Cells, Sheets and Workbooks in Excel 

Video Tutorial 

To see Cell, Sheet and Workbook Protection in action, please watch the following video tutorial.  

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

Other Excel classes you might like:

• Logical Functions in Excel
• Designing Better Spreadsheets in Excel
• Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.