How to protect a sheet in Excel and lock cells
You want colleagues to type quantities and prices into your sheet without overwriting the formulas by accident. Sheet protection keeps exactly the input cells open and locks everything else.
In short: protection takes two steps. First select the input cells, press Ctrl+1 to open Format Cells and clear Locked on the Protection tab. Then choose Review > Protect Sheet. From then on, only the unlocked cells can be changed.
Try it now
A small quote calculator: three inputs at the top, three formulas below. Say which cells you change before protecting the sheet, and set the check boxes.
Task In the quote calculator, colleagues should only enter quantity, unit price and discount. The formulas below should stay locked and visible once the sheet is protected. In Excel every cell starts out locked. Say which cells you select, and set the check boxes on the Protection tab the way they must end up for those cells.
| A | B | C | |
|---|---|---|---|
| 1 | Quote calculator | ||
| 2 | Quantity | 25 | input |
| 3 | Unit price | 48 | input |
| 4 | Discount % | 5 | input |
| 5 | |||
| 6 | Subtotal | 1200 | formula |
| 7 | Discount | 60 | formula |
| 8 | Total | 1140 | formula |
Why protection is a two-step job
Every cell in Excel starts out marked as locked. You never notice, because the lock does nothing until the sheet is protected. Protect the sheet straight away and everything is locked, including the cells people are supposed to fill in. So you unlock the input cells first and protect the sheet second.
Step 1: unlock the input cells
- Select the cells that should stay editable. Hold Ctrl to add cells that are not next to each other.
- Right-click and choose Format Cells, or press Ctrl+1 (Command+1 on a Mac).
- On the Protection tab, clear Locked and click OK.
Tip: give the input cells a fill color of their own, so everyone can see at a glance where to type.
Step 2: protect the sheet
- Go to Review > Protect Sheet.
- Keep Protect worksheet and contents of locked cells checked.
- Under Allow all users of this worksheet to, check whatever people may still do.
- If you like, type a Password to unprotect sheet and confirm it.
By default, users may select both locked and unlocked cells. The options you will use most:
| Option | What it allows |
|---|---|
| Select locked cells | clicking locked cells, for example to read a formula |
| Select unlocked cells | clicking the input cells |
| Format cells, Format columns, Format rows | changing fonts, colors, column widths and row heights |
| Insert rows, Delete rows | making the list longer or shorter |
| Sort | using the sort commands |
| Use AutoFilter | using the filter arrows, if the filter was turned on before |
Task During the stock count, colleagues enter the counted quantities in D2:D7. D2:D7 are already unlocked and row 1 has an AutoFilter. Protect the sheet with the password Inventur27. Only two things should be allowed: selecting unlocked cells and filtering. Locked cells should not even be clickable.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item no. | Item | Expected | Counted | Difference |
| 2 | L-201 | Copy paper (box) | 120 | 118 | -2 |
| 3 | L-202 | Black toner | 45 | 45 | 0 |
| 4 | L-203 | Ballpoint pens | 300 | 296 | -4 |
| 5 | L-204 | Hole punch | 18 | 18 | 0 |
| 6 | L-205 | Sticky notes | 75 | 77 | 2 |
| 7 | L-206 | Binders | 60 | 60 | 0 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
Hide formulas
Nobody can change a locked formula, but anyone can still read it in the formula bar. If only the result should show, select the formula cells, open Format Cells > Protection and also check Hidden. Like Locked, it only takes effect once the sheet is protected.
Task D2:D7 hold the commission formulas and are selected. Once the sheet is protected, they must not be editable, and anyone who clicks one of them should see no formula in the formula bar. Set the check boxes on the Protection tab the way they must end up for D2:D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Sales | Rate % | Commission |
| 2 | Chloe Bennett | 84000 | 3 | 2520 |
| 3 | Ryan Cooper | 61500 | 3 | 1845 |
| 4 | Maya Singh | 97200 | 4 | 3888 |
| 5 | Jack Ellis | 45800 | 2 | 916 |
| 6 | Zoe Harper | 73000 | 3 | 2190 |
| 7 | Leo Grant | 58900 | 4 | 2356 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
Protect the workbook structure
Sheet protection guards the cells on one sheet. Whether people can add, move, delete, hide or rename sheets is a matter of workbook structure. Go to Review > Protect Workbook, enter a Password if you want one, and confirm it. Hidden sheets can then no longer be unhidden either. Without a password, anyone can switch the protection off again.
Unprotect a sheet
Choose Review > Unprotect Sheet and enter the password if there is one. You can also go through File > Info > Protect > Unprotect Sheet. That button is also how you tell a sheet is protected: the ribbon shows Unprotect Sheet instead of Protect Sheet. Workbook protection comes off through Review > Protect Workbook and the password.
Microsoft cannot recover a forgotten password, so keep it somewhere safe.
Common mistakes
The input cells are locked too
Step 1 was skipped. Unprotect the sheet, unlock the input cells and protect it again.
Locked cells can still be changed
The Locked box on its own does nothing. Only Protect Sheet turns the lock on.
Filtering does not work on the protected sheet
Either Use AutoFilter was not checked, or the filter was not on yet when you protected the sheet. Unprotect, turn the filter on and protect again.
Treating protection as security
Microsoft states that worksheet protection is not meant as a security feature. It prevents accidents, not curiosity. Confidential data does not belong in a file you pass around, not even on a hidden sheet.
Sheet protection in German Excel
| English | German |
|---|---|
| Format Cells > Protection > Locked | Zellen formatieren > Schutz > Gesperrt |
| Hidden | Ausgeblendet |
| Review > Protect Sheet | Überprüfen > Blatt schützen |
| Protect worksheet and contents of locked cells | Arbeitsblatt und Inhalt gesperrter Zellen schützen |
| Password to unprotect sheet | Kennwort zum Aufheben des Blattschutzes |
| Allow all users of this worksheet to | Alle Benutzer dieses Arbeitsblattes dürfen |
| Unprotect Sheet | Blattschutz aufheben |
| Protect Workbook | Arbeitsmappe schützen |
Which Excel versions have sheet protection?
Sheet and workbook protection are in Excel 2016, 2019, 2021, 2024 and Microsoft 365. On a Mac, Command+1 opens Format Cells and Review > Protect Workbook protects the structure. Passwords in Excel for Mac are limited to 15 characters.
Excel for the web works differently: under Review > Manage Protection you turn on Protect sheet. The whole sheet starts out locked, you add unlocked ranges, and you pick the allowed actions under Options. Review > Pause Protection suspends protection for your own session only. Workbook passwords cannot be added, changed or removed in Excel for the web.
FAQ
How do I lock only a few cells and leave the rest open?
Work the other way round: select the whole sheet, clear Locked under Format Cells > Protection, then select only the cells that should be locked, check Locked again, and protect the sheet.
How do I protect formulas but allow data entry?
That is exactly the two-step routine above: unlock the input cells, keep the formula cells locked, protect the sheet. To also limit what people can type, add a drop-down list to the input cells.
Can different people edit different ranges?
In Excel for Windows, Review > Allow Edit Ranges lets you define ranges with their own password. It is only available while the sheet is unprotected.
Do I need a password?
No. Without one, a click on Unprotect Sheet is enough to switch it off. That is often fine when the goal is only to prevent accidental overwrites.
Why can't I insert rows?
On a protected sheet you can only do that if Insert rows was checked when the sheet was protected. Unprotect it, insert the rows and protect it again.