How To Password Protect An Excel File:
Lock Cells and Formulas (Easily)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In Excel, there are different ways that allow you to protect data and the integrity of your files.

It’s super important to know your way around protection Excel files.

The most common use-case is where an Excel file is used by many stakeholders for some ongoing process.

Here, the integrity of the file must be preserved.

Excel offers different protection options. These options provide a great balance between security and usability. For instance, some options allow users ability to add, edit, and remove data as needed.

Kasper Langmann, Co-founder of Spreadsheeto

Excel works with different levels of protection.

These include:

  • Workbook level
  • Worksheet level
  • And cell level

(There is also a file-level protection. This offers the most extreme restriction and we will touch on that a bit later as well)

Depending on your needs, these can all come in handy.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to Lock (protect) Formulas
and Allow Data Entry

Let’s start off by looking at how to lock or protect certain cells but still allowing for data entry into others.

This is often used when a worksheet contains formulas that make calculations based on some data entry.

Here, those formulas need to be protected from changes whether unintentional or otherwise.

1: Unlock all cells in the worksheet

Before protecting any cells in Excel, you must follow a simple procedure.

This procedure unlocks all the cells of your worksheet.

This might seem odd, as you haven’t (yet) locked any cells. But, Excel locks all the cells by default. So, this will always be your first step to protecting cells with formulas.

Kasper Langmann, Co-founder of Spreadsheeto

Simply press Ctrl + A to highlight your entire worksheet. Then right click and chose Find Format Cells in the resulting context menu.

Find the Protection tab on the resulting Format Cells dialog box and uncheck the box next to Locked.

Now click OK.

Format Cells Protection Tab

2: Select the formulas you want to lock

Now you have unlocked the worksheet.

Go to the Editing group on the Home tab and click the down arrow next to Find & Select.

Next, click on Go To Special.

Find and Select Go To Special

With the Go To Special dialog box visible, select the radio button next to Formulas.

Make sure all the boxes underneath it are checked.

Go to Special Formulas all selected

Click OK and then notice that all cells with formulas will be highlighted.

All formulas are selected

Now right click on the highlighted cells and click on Format Cells in the context menu.

In the resulting Format Cells dialog box that appears, click on the Protection tab.

Check the Locked box that we unchecked in the previous step (where we unlocked all the cells on the worksheet). Now click OK.

Kasper Langmann, Co-founder of Spreadsheeto
Protection is Locked

One last thing left to do is to go to the Review tab and click on Protect Sheet in the Changes group.

Here you can set a password. For now, we will just leave it blank and click OK.

Protect sheet box

Check the protection of your cells with formulas.

Try to type into those cells or press Delete… You can’t! You will get a message box that tells you the cell you are trying to change is protected 🙂

Kasper Langmann, Co-founder of Spreadsheeto

Note that any data entry cell that was not a part of the selected range of cells with formulas can still be changed.

Protected sheet warning

Need to add more formulas? And do they need to be protected too?

Simply unprotect your worksheet and then proceed to add the formulas. Then reselect the range of cells with the formulas you want to protect. Make sure to include the new cells and follow the same procedure.

Now you have protected the cells in your worksheet with formulas. No users will be able to delete, overwrite, or move those cells without unprotecting the sheet.

Kasper Langmann, Co-founder of Spreadsheeto

How to hide formulas

You might find it adequate to protect cells with formulas from changes.

However, any user can still view the formula contents of those cells in the formula bar.

Formula is still visible

Do you want to hide the formulas from other users for proprietary reasons?

This is simple to do. It only requires a few more steps built into the process of protecting cells. For this example, I’ll assume you have already made sure the worksheet is unprotected.

Kasper Langmann, Co-founder of Spreadsheeto

Now select the range of cells with formulas that you want to not only protect but also hide the formulas in.

Then right click and select Format Cells to bring up the Format Cells dialog box.

This time, make sure there that not only the box next to Locked is checked, but also the box next to Hidden.

Format Cells Protection Hidden

Click OK and then select Protect Sheet in the Changes group on the Review tab.

On the Protect Sheet dialog box, make sure that the box next to ‘Protect worksheet and contents of locked cells’ is checked.

Click OK.

Protect worksheet and contents of locked cells

Now when you click on a protected cell, the formula is no longer shown in the formula bar.

Formula bar is hidden

And there you have it:

Your formula cells are not only protected from moves or changes, they are also private.

Turn off/Remove protection (unlock)
and unhide formulas

Let’s say the other users have had time to view and do a little data entry.

Now you want to do some additional work to your worksheet. For instance, you want to edit your formulas or add some new ones.

Simply, turn off or remove the protection and unhide your formulas.

This is a very simple procedure and we have already covered it somewhat.

Kasper Langmann, Co-founder of Spreadsheeto

As you might remember, the first step is to press Ctrl + A and then click Unprotect Sheet on the Review tab.

Now select the range of cells that you want to unlock and unhide. Right click and select Format Cells.

On the Protection tab of the Format Cells dialog box, uncheck the Protect and Hidden check boxes. Click OK.

That’s it!

Protect a Workbook

When we say workbook, what we are referring to is the Excel file itself.

A workbook is an entire file containing all the worksheets (different tabs in the file).

So, remember that ‘workbook’ and ‘Excel file’ (or ‘file’) are synonymous terms as far as Excel is concerned.

You should note that Excel offers file-level protection as well as workbook-level protection.

These are two very different levels of protection but do not let this confuse you. This is for good reason even though ‘workbook’ is synonymous with ‘file’.

File-level protection for your workbook

The highest level of protection that Excel offers is file-level protection.

If you want to allow only certain users to even open the file, this is the level of protection you want to use.

Locking access to your Excel file with a password encrypts the file. So, let’s look at how to do that!

Kasper Langmann, Co-founder of Spreadsheeto

With the file open, go to the File tab and click on Protect Workbook in the Info section.

Select ‘Encrypt with Password’ from the drop-down.

On the Encrypt Document dialog box, type in a password and click OK. You will be prompted to re-enter the password and click OK.

Encrypt Document

When you save the file and anyone attempts to open it, they will be prompted for a password.

Anyone without the password is restricted from opening the file.

To reverse this encryption, simply repeat the previous process. Delete the password from the input box for password and click OK.

Kasper Langmann, Co-founder of Spreadsheeto

How to Password Protect
Your Excel Files

You just learned about high-level protection for your Excel workbook files.

This was achieved by encrypting and restricting access to the file.

Now we will turn to workbook-level protection with a password.

This level of protection does not restrict users from opening the file. It provides an entirely different level of protection. Here users cannot delete, move, hide or rename any of the worksheets in your workbook file.

Kasper Langmann, Co-founder of Spreadsheeto

Here’re the following steps to password protect your workbook:

The first thing you need to do is click on Protect Workbook in the Changes group on the Review tab.

Protect workbook button

Now a “Protect Structure and Windows” dialog box appears. Check the box next to Structure. Note that the password text box indicates that a password is optional. Click OK.

Protect workbook for structure

Once you have clicked OK, try to add, delete, move, or rename a worksheet.

That can’t be done!

You’ll see a message box indicating that the “Workbook is protected and cannot be changed.”

Now, your workbook structure has been protected.

Workbook is protected and cannot be changed

This is not to be confused with file-level or worksheet level protection. Why? Because users could still open the file and make changes to the contents of the worksheets themselves.

Kasper Langmann, Co-founder of Spreadsheeto

Bear in mind that you can protect your workbook structure with a password or without.

Password protection provides the workbook owner (you) more control over who can make changes to the structure of your workbook.

If you don’t use a password, it serves more as a simple reminder to other users to not make changes. Remember, they can simply unprotect the workbook if they wanted to.

So, what exactly is workbook-level protection?

In the previous section on how to password protect your workbook, we mentioned workbook structure. This refers to the composition of the worksheets within the Excel file.

This type of protection restricts the “rules” of other users. Other users won’t be able to add, delete, move, or change the names of any of the existing worksheets within your workbook file.

They can view and even change contents within the worksheets. But, they cannot change anything at the worksheet (or workbook structure) level.

Unprotect an Excel workbook

All it takes to unprotect a workbook not protected with a password is to click on Protect Workbook.

This button is in the Changes group on the Review tab.

The dark gray background indicates that the workbook is currently protected.

Workbook is protected

By doing this, you will unselect or deactivate the workbook protection. This is also displayed when Protect Workbook is no longer highlighted on the Ribbon.

To unprotect a workbook that is password protected, you follow the same procedure. But, now you will be prompted for the password.

Kasper Langmann, Co-founder of Spreadsheeto
Unprotect workbook password prompt

Once you supply the correct password, Excel will remove the workbook protection.

How to retrieve forgotten password
for a protected sheet?

What if you forget or lose your password for a protected file, workbook, or worksheet?

Always make sure to write your passwords down in some secure fashion in case you were to forget them.

Microsoft doesn’t allow retrieving forgotten passwords related to file or workbook level protection… However, Excel password protection is not the most secure system there is.

You will be out of luck if you lose or forget your password to open your file. But, if we are talking about workbook or worksheet level of protection, there is hope!

Kasper Langmann, Co-founder of Spreadsheeto

There is a well-known VBA solution to the unknown/lost/forgotten password challenge.

The following VBA code can be found with a simple Google search. However, we will go ahead and cover the details of how to put it to use here.

***USE IT AT YOUR OWN DISCRETION***
We cannot be held reliable for any corrupt files and/or misuse

Okay, back to the tutorial…

Even if you have no experience with VBA or macros in Excel, don’t worry. Follow these directions step-by-step. Then, you will be unlocking your password protected sheets and workbooks in no time!

Kasper Langmann, Co-founder of Spreadsheeto

The first thing you need to do is open the VBA editor by pressing Alt + F11.

In the Project window on the left, find the worksheet listed that you have forgotten your password for.

Double click on it.

VBA Editor

Copy and paste the following code into the General Declarations window that now appears:

Sub PasswordBreaker()

‘Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox “One usable password is ” & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Your page should now look like the following figure:

Code inserted in VBA Editor

Now go to the top of the VBA editor and find the Run tab and click on ‘Run Sub/UserForm’ to run the code (or press F5).

Run Sub/UserForm

Once the macro runs, a message box with a password should appear.

Box with password appears

And there you have it!

Simply use this password to unprotect your sheets or workbooks.

Wrapping up

Clearly, Excel offers a vast array of flexible options when it comes to protecting your data.

As shown in this tutorial there are plenty of options to protect your data – with and without passwords.

Whether you need to protect your data, formulas, worksheet or workbook, Excel has you covered.

2019-03-28T13:35:54+00:00