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.

Practice

1. What is the main purpose of sheet protection in Excel?

easy

A. To change the font style of cells

B. To delete all data in the sheet

C. To automatically save the file

D. To prevent unwanted changes to the spreadsheet

Solution

1. Step 1: Understand sheet protection purpose

   Sheet protection is used to stop users from making unwanted changes to the sheet.

2. Step 2: Compare options

   Only To prevent unwanted changes to the spreadsheet correctly describes this purpose; others describe unrelated actions.

3. Final Answer:

   To prevent unwanted changes to the spreadsheet -> Option D

4. Quick Check:

   Sheet protection = prevent unwanted changes [OK]

Hint: Sheet protection stops edits except where allowed [OK]

Common Mistakes:

• Thinking it deletes data
• Confusing with autosave
• Assuming it changes formatting automatically

2. Which of the following is the correct way to unlock cells before protecting a sheet?

easy

A. Select cells, go to Data tab, click Sort

B. Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked'

C. Select cells, press Delete key

D. Select cells, change font color to white

Solution

1. Step 1: Identify how to unlock cells

   Unlocking cells is done via Format Cells > Protection tab by unchecking 'Locked'.

2. Step 2: Verify options

   Only Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' describes this correct method; others do unrelated actions.

3. Final Answer:

   Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' -> Option B

4. Quick Check:

   Unlock cells via Format Cells > Protection [OK]

Hint: Unlock cells via Format Cells before protecting sheet [OK]

Common Mistakes:

• Deleting cells instead of unlocking
• Using Data tab options wrongly
• Changing font color to hide data

3. You protect a sheet but forget to unlock cells where users enter data. What happens when users try to type in those cells?

medium

A. The sheet automatically unlocks those cells

B. Users can type normally without restrictions

C. Users get an error and cannot edit those cells

D. Users can only change font color in those cells

Solution

1. Step 1: Understand locked cells effect

   If cells are locked and sheet is protected, users cannot edit those cells.

2. Step 2: Analyze user experience

   Users will get an error message or be blocked from typing in locked cells.

3. Final Answer:

   Users get an error and cannot edit those cells -> Option C

4. Quick Check:

   Locked cells + protected sheet = no editing [OK]

Hint: Locked cells block editing when sheet is protected [OK]

Common Mistakes:

• Assuming sheet unlocks cells automatically
• Thinking users can edit anyway
• Believing only formatting is blocked

4. You protected a sheet but users report they cannot sort data. What is the likely cause and fix?

medium

A. Sheet protection disables sorting; allow sorting in protection options

B. Cells are unlocked; lock them to enable sorting

C. File is corrupted; repair the file

D. Sorting is disabled in Excel options; enable it there

Solution

1. Step 1: Identify sorting restriction in protected sheets

   By default, sheet protection disables sorting unless allowed in options.

2. Step 2: Fix by enabling sorting permission

   When protecting the sheet, check 'Sort' permission to allow users to sort data.

3. Final Answer:

   Sheet protection disables sorting; allow sorting in protection options -> Option A

4. Quick Check:

   Allow sorting in protection settings to fix [OK]

Hint: Enable sorting permission when protecting sheet [OK]

Common Mistakes:

• Locking cells to fix sorting
• Assuming file corruption causes sorting issue
• Looking in Excel options instead of protection settings

5. You want users to enter data only in cells B2:B10 but protect the rest of the sheet. Which steps correctly achieve this?

hard

A. Unlock B2:B10 cells, then protect sheet with default settings

B. Lock B2:B10 cells, then protect sheet allowing editing everywhere

C. Protect sheet first, then unlock B2:B10 cells

D. Delete all other cells except B2:B10 before protecting

Solution

1. Step 1: Unlock only B2:B10 cells

   Select cells B2:B10, open Format Cells > Protection tab, uncheck 'Locked'.

2. Step 2: Protect the sheet with default settings

   Protecting the sheet now locks all other cells except unlocked B2:B10, allowing data entry only there.

3. Final Answer:

   Unlock B2:B10 cells, then protect sheet with default settings -> Option A

4. Quick Check:

   Unlock input cells before protecting sheet [OK]

Hint: Unlock input cells first, then protect sheet [OK]

Common Mistakes:

• Locking input cells instead of unlocking
• Protecting sheet before unlocking cells
• Deleting cells instead of unlocking