Ravindra BagaleCourses & study guides Track your progress

Guides

Lock Cells and Protect a Sheet: Let People Fill Only the Input Cells

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Ruhi Bagale shares a fee calculator with five counsellors. Within a week someone types a number over the discount formula and every quote after that is wrong. She unlocks the four yellow input cells, protects the sheet, and now the formulas cannot be overwritten.

Every cell is Locked by default, but locking only works after Review > Protect Sheet. So: unlock the cells people should type in (Format Cells > Protection), then protect the sheet. A password is optional and protects against accidents, not against a determined person.

Download the practice file (protect-sheet-lock-cells.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Select the input cells (hold Ctrl to pick several).
  2. Press Ctrl + 1, open the Protection tab, untick Locked, click OK.
  3. Click Review > Protect Sheet.
  4. Keep Select locked cells and Select unlocked cells ticked; add a password if you want.
  5. Click OK. Typing in a formula cell now shows a message; input cells still work.

Step by step on the fee calculator

CellWhatLocked?
B3Course feeNo (input)
B4Number of studentsNo (input)
B5Discount %No (input)
B6Student nameNo (input)
B8Gross =B3*B4Yes
B9Discount =ROUND(B8*B5,0)Yes
B10Net =B8-B9Yes
  1. Colour the input cells yellow so people know where to type.
  2. Select B3:B6, press Ctrl + 1 > Protection, untick Locked.
  3. Review > Protect Sheet, click OK (no password in the practice file).
  4. Try typing in B10: Excel refuses. Type in B4: it works.
  5. To change formulas later: Review > Unprotect Sheet.

More options

  • Hide formulas: before protecting, select formula cells, Ctrl + 1 > Protection, tick Hidden. The formula bar stays empty for those cells.
  • Allow Edit Ranges (Review tab): let certain ranges be edited with their own password while the rest stays protected.
  • In the Protect Sheet box tick Use AutoFilter or Format columns if users need them; they are off by default.
  • Review > Protect Workbook locks the structure: no one can add, delete, rename or unhide sheets.

Protection is not security

Sheet and workbook protection stop accidents. They are not encryption, and a password-protected sheet can still be opened and read. To keep a file private, use File > Info > Protect Workbook > Encrypt with Password, and store the password safely: if you lose it, the file cannot be recovered.

Ravindra Bagale's Tip

Use protection without a password for team templates. It stops accidental typing just as well, and you will never be stuck with a locked template because someone left and took the password with them.

Practice task

  1. Open the Fee calculator sheet. It is protected without a password.
  2. Type a new number of students in B4 and see B10 change.
  3. Try to type in B10 and read the message.
  4. Unprotect the sheet (Review > Unprotect Sheet), hide the formulas in B8:B10, and protect it again.
  5. Protect the workbook structure and try to delete the sheet.

Learn it properly

Course lessons:

Related guides: Data Validation in Excel: Allow Only Valid Numbers, Dates and Phone Numbers · Named Ranges in Excel: Name a Cell or List and Use It in Formulas · Education Excel Template (Fees, Attendance, Marks and Fee Receipt)

Frequently asked questions

Why can I still edit a locked cell?

Locking only works after Review > Protect Sheet. Until then, Locked has no effect.

How do I unprotect a sheet?

Review > Unprotect Sheet, and type the password if one was set.

Can users still sort or filter a protected sheet?

Only if you tick Sort and Use AutoFilter in the Protect Sheet box, and the range is not locked for sorting.

What if I forget the sheet password?

Keep an unprotected master copy. There is no official way to recover a forgotten password.