Featured - How to lock cells in excel

Throwing a lot of work into a shared Excel sheet can be rewarding, which is why it’s even more soul-crushing to wake up one day to find it destroyed. Unfortunately, whether by accident or malice, formula and numerical cells often end up edited beyond repair and headers somehow come back in Comic Sans. We’re going to show you how to lock cells in Excel so that you can stop a single keypress from ruining your day.

Locking cells in Excel – why you should do it

Advertisement

We joke about clumsy colleagues breaking spreadsheets, but there are several reasons locking an Excel spreadsheet is a good idea. Often, you’ll be sharing your spreadsheet not for collaboration, but to provide other departments or shareholders with the statistics they need. In such a case, the need for those numbers to stay accurate and accountable is important. Locking them means that if anybody has any last-minute additions or corrections, they go through the proper channels to add them.

Even if you aren’t sharing a spreadsheet, learning how to lock specific cells in Excel can save you a lot of trouble. No matter how proficient you are with the software, it’s easy enough for you or a pet to lean on a key and break your formulas.

How locking cells in Excel works

It may surprise you to hear that cells in Excel are usually locked by default. The reason that you can still edit them is that you must protect a spreadsheet or worksheet with a password before the behavior kicks in. Once this occurs, users cannot edit, format, or otherwise change cells you keep locked.

You should know, however, that Excel’s worksheet protection isn’t designed to stop a determined attack. Though Excel versions 2010 and higher use enhanced data encryption mechanisms, a criminal can still bypass them without knowing the password and change payment information or add a malicious link. Even if they can’t, passwords are easy to share intentionally or phish. As a result, you should only lock cells in Excel to prevent accidental changes, not to prevent a determined attack.

With all that said, let’s get into how to lock cells in Excel, ranging from every cell in the spreadsheet to specific cells or just formula cells:

How to Lock All Cells in Excel

In Excel, you can lock all cells from editing in under a minute. All it takes is a few clicks and a secure password:

  1. Open your spreadsheet and select all of its cells with “Ctrl + A”

    Windows 11 - Excel - Select All Cells

  2. Right-click any cell and choose “Format Cells…”

    Windows 11 - Excel - Select All Cells - Context Menu - Format Cells - Copy

  3. Open the “Protection” tab, make sure “Locked” is checked, and press “OK”

    Windows 11 - Excel - Select All Cells - Context Menu - Format Cells - Locked - Accept

  4. Right-click your sheet name and press “Protect Sheet…”

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet

  5. Type a secure password and choose the permissions you’d like users without a password to have

    One key choice is whether you want users without a password to be able to select locked or unlocked cells. Once you’ve ticked everything you want, press “OK”.

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Check Cells - Accept

  6. Re-enter your password and press “OK”

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Confirm Password - Accept

How to Lock Specific Cells in Excel

So, it’s easy enough to protect an entire worksheet in Excel, but what if you need to lock cells in Excel on an individual level? Thankfully, doing so is still quite easy – it just requires a few more clicks. Here’s how to protect certain cells in Excel and not others:

  1. Remove any existing protection from the sheet

    It’s important to know how to unprotect a sheet in Excel so that you can reset it to its base permissions. To do so, right-click on the sheet name and press “Unprotect Sheet…”.

    Windows 11 - Sheet Context Menu - Unprotect Sheet

  2. Enter the Excel password and press “OK”

    With the protection removed, we can make sure our new lock only applies to specific cells.

    Windows 11 - Sheet Context Menu - Unprotect Sheet - Type Password - Accept

  3. Select all cells in your sheet with Ctrl + A

    Windows 11 - Excel - Select All Cells

  4. Right-click any cell and press “Format Cells…”

    Windows 11 - Excel - Select All Cells - Context Menu - Format Cells

  5. Open the “Protection” tab, uncheck “Locked”, and press “OK”

    Now every cell on your sheet has no protection applied to it.

    Windows 11 - Excel - Select All Cells - Context Menu - Format Cells - Protection - Uncheck Locked - Accept

  6. Select the cell(s) you want to lock, right-click them, and press “Format Cells…”

    You can select multiple cells by clicking one, holding Ctrl, then clicking on another. You can also protect entire rows or columns like so:

    How to lock columns in Excel

    To select a column to lock, press its letter at the top of your sheet to select it and continue to the next step.

    How to lock a row in Excel

    If you want to lock an entire row in Excel, simply click its number in the border of your sheet and proceed to the next step. 

    Windows 11 - Excel - Select Cell - Context Menu

  7. Open the “Protection” tab, tick “Locked” and press “OK”

    Now that only the cells you want to protect are locked, you can protect the sheet again.

    Windows 11 - Excel - Select Cell - Context Menu - Protection - Check Locked - Accept

  8. Right-click the sheet name and press “Protect Sheet…” 

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet

  9. Enter a password, tick the functionality you want all users to have, and press “OK”

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Check Cells - Accept

  10. Re-enter the password and press “OK”

    Now only the cells you selected in step six will be uneditable.

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Confirm Password - Accept

How to Lock Formulas in Excel

In many cases, you’ll only want to lock formulas in Excel rather than the entire sheet. This will be the case if you create a budget sheet to be used month after month by the same department, for example. The numbers will inevitably change, but the way of calculating the budget won’t.

Here’s how to lock formulas in Excel for use in such a scenario:

  1. In the “Home” tab, press “Editing > Find & Select > Go To Special…”

    Windows 11 - Excel - Home - Editing - Find & Select - Go to Special

  2. Check the “Formulas” section and the types of formula you’d like to select

    Press “OK” when you’re done.

    Windows 11 - Excel - Home - Editing - Find & Select - Go to Special

  3. Press Ctrl + 1 with your formulas selected

    Windows 11 - Excel - Ctrl+1

  4. Open the “Protection” tab, check “Locked”, and press “OK”

    Windows 11 - Excel - Format Cells - Protection - Check Locked - Accept

  5. Right-click your sheet name and press “Protect Sheet…”

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet

  6. Enter a password and tick any permissions you want all users to have

    Bear in mind users will have these permissions regardless of whether they have a password. Press “OK” to apply the password and permissions.

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Check Cells - Accept

  7. Re-enter the password and press “OK”

    Windows 11 - Excel - Sheet Context Menu - Protect Sheet - Password - Confirm Password - Accept

Extra: How to Password Protect Excel Files

As well as password-protecting a worksheet in Excel, you can protect the file as a whole so nobody can even open it. To do so, follow our how to protect Excel files with Microsoft Office tutorial.

Extra: How to Password Protect a Folder on Windows

If you’d like to go even further and password protect the entire folder an Excel file is stored in, you can follow our how to password protect a folder tutorial. It’s designed for Windows 10, but the process is pretty much the same for Windows 11, too.

Password protection of My Lockbox
Password protection of My Lockbox
Advertisement