14. Protection, Sharing and Printing
14.1 Locked Cells and Protect Sheet
Every cell is Locked by default, but Locked has no effect until the sheet is protected. So the method is: unlock the input cells first, then protect the sheet.
Steps in Excel
- Select the input cells users may change (for example the daily target cells
C4:C9of the Targets sheet). - Home › Cells › Format › Format Cells… (Ctrl + 1) › Protection tab › untick Locked › OK. Give these cells a light-yellow fill so users see where to type.
- Review › Protect › Protect Sheet.
- Tick what users may still do: Select unlocked cells (keep), and as needed Format columns, Sort, Use AutoFilter, Use PivotTable & PivotChart, Edit objects.
- (Optional) enter a password › OK › confirm it.
- Test: typing in a locked cell shows a message that the cell is protected. Tab jumps between unlocked cells.
- To remove: Review › Protect › Unprotect Sheet.
Worked example – Targets sheet.
| Cell range | Contents | Locked? | Why |
|---|---|---|---|
| A4:A9 | City names (Pune … Nagpur) | Locked | Should not change |
| C4:C9 | Daily target ₹ | Unlocked | City managers type here |
| D4:D9 | =C4*31 monthly target |
Locked | Formula |
| E4:E9 | =SUMIFS(tblOrders[Amount],tblOrders[City],A4,tblOrders[Status],"Delivered")/D4 |
Locked | Formula |
If Pune's daily target in C4 is ₹60,000, D4 shows ₹18,60,000 and E4 shows 18,42,500 ÷ 18,60,000 = 99.1%.
Ravindra Bagale's Tip
Khup students aadhi Protect Sheet kartat aani mag lakshat yeta ki input cells pan lock zalyet – karan sagle cells by default Locked astat. Kram lakshat theva: aadhi input cells select karun Locked untick, mag Protect Sheet. Input cells la halka pivla rang dya mhanje user la kalta kuthe type karaycha.
Practice task
On a Targets sheet with the six cities, unlock only the daily-target cells, protect the sheet allowing Sort and Use AutoFilter, and check that the formulas cannot be edited but targets can.