Contents
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
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:
- Open your spreadsheet and select all of its cells with “Ctrl + A”
- Right-click any cell and choose “Format Cells…”
- Open the “Protection” tab, make sure “Locked” is checked, and press “OK”
- Right-click your sheet name and press “Protect Sheet…”
- 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”.
- Re-enter your password and press “OK”
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:
- 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…”.
- Enter the Excel password and press “OK”
With the protection removed, we can make sure our new lock only applies to specific cells.
- Select all cells in your sheet with Ctrl + A
- Right-click any cell and press “Format Cells…”
- Open the “Protection” tab, uncheck “Locked”, and press “OK”
Now every cell on your sheet has no protection applied to it.
- 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.
- 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.
- Right-click the sheet name and press “Protect Sheet…”
- Enter a password, tick the functionality you want all users to have, and press “OK”
- Re-enter the password and press “OK”
Now only the cells you selected in step six will be uneditable.
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:
- In the “Home” tab, press “Editing > Find & Select > Go To Special…”
- Check the “Formulas” section and the types of formula you'd like to select
Press “OK” when you're done.
- Press Ctrl + 1 with your formulas selected
- Open the “Protection” tab, check “Locked”, and press “OK”
- Right-click your sheet name and press “Protect Sheet…”
- 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.
- Re-enter the password and press “OK”
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.
