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.