Cell locking in Excel - Dashboard Guide

Dashboard Mode - Cell locking

Goal

Protect important cells in a sales report so users can only edit allowed cells.

Sample Data

Product	Price	Quantity Sold	Total Sales
Pen	1.50	100	=B2*C2
Notebook	3.00	50	=B3*C3
Eraser	0.75	200	=B4*C4
Marker	2.00	80	=B5*C5
Ruler	1.25	60	=B6*C6

Dashboard Components

• KPI Card: Total Revenue with formula =SUM(D2:D6) shows total sales amount.
• Editable Cells: Quantity Sold column (C2:C6) unlocked for user input.
• Locked Cells: Product and Price columns (A2:B6) and Total Sales column (D2:D6) locked to prevent editing.
• Formula Cells: Total Sales cells (D2:D6) contain formula =B2*C2 copied down, locked to avoid accidental changes.
• Sheet Protection: Enabled with password (optional) to enforce locking.

Dashboard Layout

+-----------------------------+
|        Total Revenue         |
|         (KPI Card)           |
+-----------------------------+
| Product | Price | Quantity Sold | Total Sales |
|  (locked) (locked) (editable) (locked) |
|  Pen    | 1.50  |   100   | 150.00|
|  Notebook| 3.00 |    50   | 150.00|
|  Eraser | 0.75  |   200   | 150.00|
|  Marker | 2.00  |    80   | 160.00|
|  Ruler  | 1.25  |    60   |  75.00|
+-----------------------------+

Interactivity

User can change Quantity Sold values in column C. When changed, Total Sales updates automatically because of the formula. The Total Revenue KPI card updates to show the new sum of all sales.

Locked cells cannot be edited, preventing accidental changes to product names, prices, or formulas.

Self Check

If you try to edit a Price cell, what happens? (Answer: Editing is blocked because the cell is locked and sheet protection is on.)

If you change a Quantity Sold value, which components update? (Answer: The Total Sales cell for that row and the Total Revenue KPI card update automatically.)

Key Result

A sales dashboard where quantity sold is editable, prices and formulas are locked, and total revenue updates automatically.

Practice

1. What does locking a cell in Excel do before you protect the sheet?

easy

A. Prevents the cell from being edited after sheet protection is enabled

B. Deletes the cell content automatically

C. Highlights the cell with a color

D. Copies the cell content to another sheet

Solution

1. Step 1: Understand cell locking purpose

   Locking a cell marks it so it cannot be changed once the sheet is protected.

2. Step 2: Effect of protecting the sheet

   Protecting the sheet activates the locking, preventing edits on locked cells.

3. Final Answer:

   Prevents the cell from being edited after sheet protection is enabled -> Option A

4. Quick Check:

   Locking + Protect Sheet = No edits [OK]

Hint: Lock cells then protect sheet to stop edits [OK]

Common Mistakes:

• Thinking locking deletes or copies cells
• Believing locking highlights cells
• Assuming locking works without protecting sheet

2. Which of these is the correct way to unlock a cell before protecting the sheet?

easy

A. Use Data Validation to unlock the cell

B. Double-click cell and press Delete key

C. Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked'

D. Insert a comment in the cell

Solution

1. Step 1: Locate cell protection settings

   Right-click the cell and open Format Cells dialog, then go to Protection tab.

2. Step 2: Unlock the cell

   Uncheck the 'Locked' checkbox to make the cell editable after sheet protection.

3. Final Answer:

   Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked' -> Option C

4. Quick Check:

   Unlock cells via Format Cells -> Protection [OK]

Hint: Unlock cells via Format Cells -> Protection tab [OK]

Common Mistakes:

• Trying to unlock by deleting content
• Using Data Validation to unlock cells
• Adding comments to unlock cells

3. You locked cells A1:A5 and unlocked B1:B5. After protecting the sheet, which cells can you edit?

medium

A. Only cells A1:A5

B. Only cells B1:B5

C. Both A1:A5 and B1:B5

D. No cells can be edited

Solution

1. Step 1: Understand locked vs unlocked cells

   Locked cells cannot be edited after protection; unlocked cells remain editable.

2. Step 2: Apply to given ranges

   Cells A1:A5 are locked, so protected sheet blocks edits there; B1:B5 unlocked, so editable.

3. Final Answer:

   Only cells B1:B5 -> Option B

4. Quick Check:

   Unlocked cells stay editable after protection [OK]

Hint: Only unlocked cells editable after protection [OK]

Common Mistakes:

• Assuming locked cells are editable
• Thinking protection disables all cells
• Confusing locked and unlocked ranges

4. You locked cells but forgot to protect the sheet. What happens when you try to edit those cells?

medium

A. The sheet becomes read-only

B. Excel shows an error and blocks editing

C. The cells get deleted automatically

D. You can edit the locked cells without restriction

Solution

1. Step 1: Understand locking vs protecting

   Locking marks cells but does not enforce protection until sheet is protected.

2. Step 2: Effect of no protection

   Without protecting the sheet, locked cells behave like normal cells and can be edited freely.

3. Final Answer:

   You can edit the locked cells without restriction -> Option D

4. Quick Check:

   Locking alone does not block edits [OK]

Hint: Protect sheet to activate locking effect [OK]

Common Mistakes:

• Thinking locking alone blocks edits
• Expecting error without protection
• Confusing locking with sheet protection

5. You want to protect a budget sheet so users can only change the 'Notes' column (D) but not the amounts (A:C). How do you set this up?

hard

A. Unlock column D cells, lock columns A:C, then protect the sheet

B. Lock all cells, then protect the sheet without unlocking any cells

C. Unlock columns A:C, lock column D, then protect the sheet

D. Do not lock any cells and protect the sheet

Solution

1. Step 1: Unlock the 'Notes' column (D)

   Make column D cells unlocked so they remain editable after protection.

2. Step 2: Lock columns A:C and protect the sheet

   Lock columns A:C to prevent edits, then enable sheet protection to enforce locking.

3. Final Answer:

   Unlock column D cells, lock columns A:C, then protect the sheet -> Option A

4. Quick Check:

   Unlock editable cells, lock others, then protect [OK]

Hint: Unlock editable cells only, then protect sheet [OK]

Common Mistakes:

• Locking editable column by mistake
• Not protecting sheet after locking
• Unlocking wrong columns