A pricing model that took three weeks to build can break in one keystroke: a colleague types a number into D14, and the formula that lived there is gone. Nobody notices until the quotes come out wrong. That is the first reason to protect formulas in Excel. The second is quieter: anyone you send the file to can click on a cell and read your margin logic in the formula bar. Excel has a built-in answer to both, and it takes about two minutes to set up. It also has a limit that Microsoft itself is careful to spell out, and the limit decides where the built-in protection stops being enough.
How to lock and protect formulas in Excel
Excel's protection model has a quirk that trips people up: every cell is marked "Locked" by default, but the lock does nothing until you protect the sheet. Protect the sheet without preparation and you freeze everything, including the input cells people are supposed to fill in. So the job is done in two rounds: first unlock everything, then re-lock only the formulas.
- Select the whole sheet (Ctrl+A, or the triangle above row 1), open Format Cells (Ctrl+1), and on the Protection tab clear the Locked check box. Now nothing is locked.
- Select every formula cell in one step: Home > Find & Select > Go To Special, choose Formulas, and click OK. Excel highlights each cell containing a formula, however scattered they are.
- With those cells still selected, open Format Cells again and on the Protection tab check both Locked and Hidden.
- Turn protection on: Review > Protect Sheet. Leave "Protect worksheet and contents of locked cells" ticked, choose what users may still do (selecting cells and formatting are common allowances), and set a password if you want one.
The result is a sheet that behaves the way a shared model should. Input cells accept typing. Formula cells refuse edits with a polite error message, and when someone clicks one, the formula bar stays empty; they see the result, not the recipe. To make changes yourself, go to Review > Unprotect Sheet, edit, and protect again.
What Locked and Hidden actually do
The two check boxes on the Protection tab are independent, and both wait for sheet protection to take effect:
| Setting | Effect while the sheet is protected | Effect while unprotected |
|---|---|---|
| Locked | The cell cannot be edited, moved, or deleted. | None |
| Hidden | The cell's formula does not appear in the formula bar; only its value shows. | None |
Most people who ask how to hide formulas in Excel want both boxes: Locked stops the accidental overwrite, Hidden stops the casual read. Checking Hidden alone is legitimate too, for instance when reviewers should be able to adjust a formula range's neighbours but you'd rather not display the logic on every click.
The honest limit: protection is not security
Microsoft's own documentation positions worksheet protection as a guard against accidental or unwanted changes, and explicitly not as a security feature. That framing is accurate. The sheet password does not encrypt anything; the formulas still sit in the file in readable form, and tools that strip sheet protection are a search away. Anyone determined enough, or anyone who simply copies your cells into a fresh workbook, walks off with the logic.
The same boundary applies to the related tricks. A hidden sheet travels with every copy of the file, as covered in the post on hiding sheets in Excel. A file-open password genuinely encrypts the workbook, but the moment you share the password along with the file, the recipient has everything; the post on password protecting an Excel file walks through where each password helps.
So the fair summary of Excel's built-in protection: it keeps honest colleagues from breaking your model, and it keeps casual eyes off your formulas. It does not keep your logic out of the hands of whoever holds the file. For a sheet circulating inside your team, that is usually exactly the right amount of protection. For a pricing model going to a prospect, a commission scheme going to a partner, or a quote tool a competitor might one day open, it is not.
When the formulas are the asset: don't send the file at all
The structural problem is that Excel can only share your model by shipping the whole engine. If the recipient needs the calculations but has no business seeing them, the fix is to change what you deliver: keep the workbook, and hand out an interface instead.
That is the model behind granular sharing in airrange. You pick the input cells and result ranges from your workbook and publish them as a small web app that recipients open by link in a browser. When the app is shared, airrange packs only the formulas and data that app actually needs into an encrypted calculation package; the workbook itself is never delivered, and neither are the formulas, which never reach the recipient's browser. Someone using your quote calculator sees inputs and results. There is no formula bar to peek into and no file to copy.
The master file stays where it is. Local Excel files are never uploaded; airrange reads them in your browser and stores only metadata about the app, so the numbers in your cells never touch its servers. And the controls that sheet protection only imitates exist here as real access control: links can be read-only or editable, can carry their own password and an expiry date, and can be restricted to specific people or email domains, verified with a 6-digit passcode. Revoking access means changing a list, not chasing down file copies. The compliance and security page covers the encryption and EU hosting in detail.
Which protection fits which situation
| Situation | Right tool |
|---|---|
| Colleagues work in the sheet and might type over formulas | Lock and hide formula cells, then Protect Sheet |
| A template should not be overwritten, contents aren't secret | Sheet protection, plus a password-to-modify on the file |
| External recipients need the calculations, not the logic | Share an app built on the workbook, keep the file |
| The model itself is proprietary IP | Never send the file; share inputs and results only |
For the everyday case, run the four steps above the next time you pass a model around: unlock all, select formulas via Go To Special, set Locked and Hidden, protect the sheet. It costs two minutes and saves the Monday morning where the quotes are wrong and nobody knows why. And when the spreadsheet holds logic you'd rather no one reads, turn the Excel model into a web app and send a link instead of the engine.