How to Protect Formulas in Excel (and Still Let People Enter Data)

Short answer: in Excel, every cell is “locked” by default, but locking does nothing until the sheet is protected. To protect only your formulas: select all cells and unlock them, use Go To Special → Formulas to select every formula and lock (and optionally hide) those cells, then turn on Review → Protect Sheet. People can still type in the input cells, but cannot change or delete a formula.

This is the single most useful step before you share a template, a budget or a timesheet with someone else. Below are the exact steps, the options that keep a protected sheet usable, the limits of Excel protection, and how to do the same in Google Sheets.

How locking and protection work together

That is why “I locked the cells but people can still edit them” is such a common complaint: the cells were locked, but the sheet was never protected.

Step by step: protect only the formula cells

1. Unlock everything. Click the corner button to select the whole sheet (or press Ctrl+A), open Format Cells (Ctrl+1), go to the Protection tab and clear Locked.
2. Select all formulas. Press F5 (or Ctrl+G), click Special, choose Formulas and click OK. Excel selects every cell that contains a formula on the sheet.
3. Lock them. With the formulas still selected, open Format Cells → Protection and tick Locked. Tick Hidden as well if you do not want people to see how the formulas work.
4. Lock labels too (optional). Headings and fixed text can be locked the same way so the layout cannot be changed.
5. Protect the sheet. Go to Review → Protect Sheet. Keep Select unlocked cells ticked, decide whether to allow Select locked cells, and add the other permissions you need (see below). Enter a password if you want one, and confirm.

Test it immediately: try typing in a formula cell (Excel should refuse) and in an input cell (it should work). Pressing Tab on a protected sheet jumps between unlocked cells only, which makes data entry faster.

Keep a protected sheet usable

The Protect Sheet dialog lists actions users may still perform. The useful ones:

• Format cells, columns, rows — allows widening columns or applying colours.
• Insert rows / delete rows — lets users add lines to a list. Note that inserted rows inherit the locked status of the row above.
• Sort and Use AutoFilter — lets people filter lists; sorting only works if the cells being sorted are unlocked.
• Edit objects — needed if users must interact with charts or form controls.

Two limitations to plan for:

• Excel tables do not grow automatically on a protected sheet. If users need to add rows to a table, either pre-create enough empty rows with formulas in place, or unprotect, add rows and re-protect.
• Some features are unavailable while protected, such as inserting PivotTables from the protected range or editing data validation lists. Build and test everything before you protect.

Let specific people edit specific ranges

In Excel for Windows, Review → Allow Edit Ranges lets you define ranges that can be edited on a protected sheet, each with its own password. For example, a manager can have a password for the “Approved rate” column while everyone else only edits hours. Set up the ranges first, then protect the sheet.

Protect the workbook structure

Sheet protection does not stop someone from deleting or renaming a whole sheet. If your formulas refer to other sheets — a lookup table, a list of categories — use Review → Protect Workbook to lock the structure. Hide supporting sheets (right-click → Hide) before protecting the workbook, so they cannot be unhidden without the password.

Make inputs obvious

Protection works best when people can see where to type. Two habits help:

• Colour input cells consistently, for example a light yellow fill, and explain the colour in a note at the top of the sheet.
• Add data validation to inputs (Data → Data Validation) — drop-down lists, whole numbers only, dates within a range. Protection stops formulas from being broken; validation stops inputs from being wrong.

Our timesheet template and monthly budget template guides use exactly this pattern: coloured input cells, validated lists and locked formulas.

What sheet protection does not do

Sheet and workbook protection prevent accidental changes; they are not strong security. Anyone with the file and enough determination can get around a sheet password, and hidden formulas can be revealed by copying data elsewhere. For confidential data, use File → Info → Protect Workbook → Encrypt with Password, which encrypts the whole file, and share it only with people who should see it. Never put information in a “hidden” sheet that the recipient must not see.

Keep a record of passwords in a password manager. Microsoft cannot recover a forgotten sheet or file password for you.

The same in Google Sheets

1. Select the range with formulas (or the whole sheet).
2. Go to Data → Protect sheets and ranges and click Add a sheet or range.
3. For a sheet, you can choose Except certain cells and list the input ranges that stay editable.
4. Under Set permissions, restrict who can edit, or choose Show a warning when editing this range for a softer reminder.

Google Sheets protection is based on accounts rather than passwords: you decide which collaborators may edit each protected range. Viewers and commenters cannot edit anything regardless.

Common problems

• “The cell or chart you’re trying to change is on a protected sheet.” Either the cell is locked as intended, or you forgot to unlock that input cell before protecting. Unprotect, unlock it, protect again.
• New rows have no formulas. On a protected sheet, users cannot fill formulas down. Pre-fill formulas in enough rows, or use whole-column formulas in a summary sheet instead.
• Sorting fails. Sorting needs the sorted range unlocked; keep formulas outside the range users sort, or calculate them in a separate summary.
• Forgotten password. Keep an unprotected master copy somewhere safe so you can always rebuild.

Frequently asked questions

Can I protect formulas without a password?

Yes. Leave the password blank in the Protect Sheet dialog. The sheet is still protected against accidental edits, and anyone can unprotect it from the Review tab when needed — often the most practical choice inside a small team.

How do I hide formulas but show the results?

Tick Hidden in Format Cells → Protection for the formula cells, then protect the sheet. The cell shows the result; the formula bar stays empty.

Does protection work in Excel for Mac and Excel on the web?

Yes, the Locked and Hidden settings and sheet protection work on Mac and on the web, although some options such as Allow Edit Ranges are only available in Excel for Windows.

Can I protect just one cell?

Yes. Unlock all cells, lock only the one you want to protect, then protect the sheet.

More guides on building reliable spreadsheets are collected in Spreadsheet & Dashboard Design.