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 |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Neha Shah) are made up for this lesson on locked cells and protect sheet.
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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of locked cells and protect sheet on a blank sheet for BrightLedger Finance.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Priya Deshmukh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Anna Kowalski.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply locked cells and protect sheet to a 5-row made-up BrightLedger Finance table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with locked cells and protect sheet.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as BrightLedger_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Input cells to unlock (Easy): How many cells are in the Monthly target column?
- Total target (Easy): Add up the Monthly target column.
- Total actual (Easy): Add up the Actual column.
- Highest target (Easy): What is the largest value in the Monthly target column?
- Overall achievement (Medium): Divide the total Actual by the total Monthly target. Round to 3 decimals.
- Cities below target (Medium): How many cities have a Gap below 0?
- Manager for a city (Medium): Find the Manager for City "London".
- Pages to print (Medium): If one page holds 4 data rows, how many pages do the rows need?
- Gap for the first city (Medium): For the first row, find Actual minus Monthly target.
- Best performer (Hard): Which City has the largest Actual?
- Status for the first city (Hard): For the first row, return "Hit" if Actual is at least Monthly target, else "Miss".
- Total shortfall (Hard): Add up the Gap column only where it is below 0, and show it as a positive number.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
