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
Many students do Protect Sheet first and then realise the input cells are locked too – because all cells are Locked by default. Remember the order: first select the input cells and untick Locked, then Protect Sheet. Give the input cells a light yellow colour so the user knows where to type.
Ravindra Bagale's Tip – मराठी
बरेच students आधी Protect Sheet करतात आणि मग लक्षात येतं की input cells पण lock झाल्या आहेत – कारण सगळ्या cells default Locked असतात. क्रम लक्षात ठेवा: आधी input cells select करून Locked untick, मग Protect Sheet. Input cells ना हलका पिवळा रंग द्या म्हणजे user ला कळतं कुठे type करायचं.
Ravindra Bagale's Tip – हिंदी
बहुत से students पहले Protect Sheet कर देते हैं और फिर पता चलता है कि input cells भी lock हो गई हैं – क्योंकि सारी cells default में Locked होती हैं. क्रम याद रखो: पहले input cells select करके Locked untick, फिर Protect Sheet. Input cells को हल्का पीला रंग दो ताकि user को पता चले कहाँ type करना है.
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.