logo

Excel Protection

Excel
Protecting Excel from changes 02

General Protection

Before explaining how Excel file protection works, it’s important to note that these passwords can be cracked.

Therefore, use Excel’s protection primarily to prevent accidental changes. For example, if Excel is used to create a quotation with hidden and protected cells for calculations, make sure to send the final quotation as a PDF. 

In Excel, protection can be applied to individual worksheets or the entire workbook (Excel file).

Protecting a Worksheet in Excel

To protect a worksheet (a tab in Excel), follow these steps:

  • Select the worksheet by clicking any cell on it
  • Go to the Review tab
    Excel Review tab
  • In the Changes group, click Protect Sheet
    Protect Sheet from changes
  • Excel will show a dialog box where you can define the level of protection
  • If a user should not be allowed to do anything, uncheck all the boxes. If users should only be able to view the file, consider sending it as a PDF instead
  • Enter a password to prevent users from removing the protection. You can also leave the password field blank

    Protecting Excel from changes 02

  • You’ll be asked to confirm the password

Using Excel as a Form

Excel can also be used as a form. In that case, you don’t want to protect all cells—only the ones containing formulas or calculations. In Excel, you can specify for each cell whether it should be locked when the worksheet is protected.

An Example to Illustrate This

  • Download the sample file
  • In this example, only the exchange rate can be changed. The Euro prices are then calculated automatically

    VB 10112 Excel Protection Example
  • So when protecting the worksheet:
    Select the cell that should remain editable
  • Right-click the cell and choose Format Cells (CTRL+1)
  • Go to the Protection tab

    VB 10112 Excel Protection Example 02

  • Uncheck Locked so this cell will not be protected when the sheet is protected
  • When protecting the worksheet, make sure Select unlocked cells is checked
  • Now protect the worksheet and verify that only the specified cell (exchange rate) remains editable

Protecting a Workbook in Excel

Follow the steps below:

  • To protect an entire Excel workbook
  • Go to the Review tab
  • In the Changes group, select Protect Workbook
  • Select Protect Structure and Windows
  • Choose to protect the workbook for: Structure and Windows
  • Enter a password (optional)
  • If a password is entered, you’ll be prompted to confirm it
  • Click OK to confirm