Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 26 of 34

Protect formulas and prepare a clear handover

Leave quantity inputs editable and protect the calculation cells.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Set up sheet protection, test an input change and write a handover note for a colleague.

Example

Let a colleague edit quantities without replacing formulas

Enter Item, Quantity, UnitPrice and Total with Keyboard 2 30 and Monitor 1 180. Total multiplies quantity by price. Your colleague needs to change quantities while leaving the formulas intact.

How it works

Locked cells become restricted when you protect the sheet. Unlock the input cells first. Worksheet protection helps prevent accidental edits; encryption and sharing permissions address confidentiality.

A useful handover names the owner, editable fields and recovery method. For a shared file, also check the recipient’s permissions and access to version history.

Steps

  1. Enter the two records with =B2*C2 in D2 and =B3*C3 in D3. Check total 240 outside the list and label B2:B3 as the quantity inputs.

  2. On desktop, select B2:B3 and choose Format Cells > Protection. Clear Locked and leave the formula cells locked. The settings take effect after you protect the sheet.

  3. Choose Review > Protect Sheet and allow the selection and editing actions needed for the inputs. If you test a password, keep it in your private exercise notes rather than in a public template.

  4. Change Keyboard quantity from 2 to 3. Check line total 90 and overall total 270. Try editing a Total formula to see the protection response.

  5. Unprotect the sheet, check the formulas and restore the quantities. Add a Readme listing editable cells, calculated fields, units and who to ask about formula changes.

  6. For a coauthoring test in your account, share only this practice copy and review the link permissions first. Ask the recipient to check the editable inputs and version history. If you work locally, record those checks for a later sharing test.

Try it yourself

  1. Add Dock quantity 3 at price 40, extend the formula and input-cell permissions, and calculate the revised original-input total. Test whether the new quantity cell can be edited after reprotection.
  2. Write a handover checklist identifying the workbook owner, approved editable range, recovery copy and whether sensitive information would require a separate confidentiality control.
Show expected result
  • The original two-item total is 240; changing Keyboard quantity to 3 produces 270. The protected formula cells resist ordinary replacement while the quantity inputs remain editable.
  • With original quantities restored and Dock added, the three-item total is 360. The new input permission must be tested rather than inferred from cell color.

If something goes wrong

None of the inputs can be edited.
Unprotect the sheet and check that B2:B3 have Locked cleared. Protect it again and test each input cell.
A protected sheet is being treated as a confidential file.
Review file encryption and access permissions separately. Sheet protection restricts editing; it does not secure the file’s contents against disclosure.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What does worksheet protection primarily support here?
2. What is the total after Keyboard quantity changes to three?
3. When is an unlocked input setting enforced as a protected workflow?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Check this plan for editable quantities and protected formulas. Draft a short Readme that tells the recipient what they can change and how to request a formula update.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…