Sheet protection in Excel - Dashboard Guide

Dashboard Mode - Sheet protection

Dashboard Goal

Protect the sales data sheet to prevent accidental changes while allowing users to enter sales in specific cells.

Sample Data

Product	Jan Sales	Feb Sales	Mar Sales	Total Sales
Apples	100	120	130	=SUM(B2:D2)
Bananas	90	110	115	=SUM(B3:D3)
Cherries	80	95	100	=SUM(B4:D4)
Dates	70	85	90	=SUM(B5:D5)
Elderberries	60	75	80	=SUM(B6:D6)

Dashboard Components

• Data Table: Shows product sales for three months and total sales. Formulas in column E calculate total sales per product using =SUM(B2:D2) and similar for other rows.
• Sheet Protection: The sheet is protected with a password to prevent editing of formulas and product names. Only cells in columns B, C, and D (monthly sales) are unlocked for user input.
• Unlocked Cells: Cells B2:D6 are unlocked to allow users to enter or update sales numbers.
• Protected Cells: Cells A2:A6 and E2:E6 are locked to prevent changes to product names and total sales formulas.

Dashboard Layout

+-------------------------------+
|        Sales Data Table        |
|  (Products, Monthly Sales,     |
|   Total Sales with formulas)   |
+-------------------------------+
|        Sheet Protection        |
|  (Locked cells and password)  |
+-------------------------------+

Interactivity

Users can enter or update sales numbers only in the unlocked cells (Jan, Feb, Mar sales columns). The sheet protection prevents editing of product names and total sales formulas. This keeps data safe while allowing updates.

Self Check

If you try to edit a total sales cell (column E), what happens?
Answer: Excel will prevent editing because the sheet is protected and those cells are locked.

If you enter new sales numbers in January sales (column B), what updates?
Answer: The total sales in column E will automatically update because of the SUM formula.

Key Result

A protected sales data sheet allowing input only in monthly sales cells while safeguarding formulas and product names.