Lesson 6.2 – Worksheet & Workbook Protection

Lesson Overview

Worksheet and workbook protection in Excel ensures that data, formulas, and structure are secure. This lesson teaches how to lock cells, protect sheets and workbooks, hide formulas, and use passwords for authorized access.

1. Locking Cells

1.1 Purpose

• Prevents editing of important cells while allowing changes in others

• Useful for dashboards, templates, and shared workbooks

1.2 Steps to Lock Cells

1. Select the cells you want to remain editable → Right-click → Format Cells → Protection → Uncheck “Locked”

2. Select all cells you want protected (default is Locked)

2. Protecting a Worksheet

2.1 Steps to Protect

1. Go to Review → Protect Sheet

2. Enter password (optional but recommended)

3. Select actions allowed for users, e.g.,:

   • Select locked cells

   • Select unlocked cells

   • Format cells, columns, rows

4. Click OK

2.2 Example

• Lock formulas in a financial model while allowing input in assumptions cells

3. Protecting a Workbook

3.1 Purpose

• Protects entire workbook structure: prevents adding, deleting, or renaming sheets

3.2 Steps to Protect Workbook

1. Go to Review → Protect Workbook

2. Choose Structure (prevents sheet changes)

3. Enter password → Click OK

3.3 Example

• Protect a company template workbook so users cannot delete or move sheets

4. Hiding Formulas

4.1 Purpose

• Prevents users from seeing formulas while displaying results

• Ensures sensitive calculations remain confidential

4.2 Steps to Hide Formulas

1. Select formula cells → Right-click → Format Cells → Protection → Check “Hidden”

2. Protect the sheet (see step 2)

4.3 Example

• Hide complex loan or financial formulas in a dashboard

5. Best Practices for Protection

• Always backup workbook before adding passwords

• Combine cell locking and hiding for maximum security

• Use strong passwords and store securely

• Test protected workbook as a regular user

6. Real-Life Examples

• Finance: Protect profit/loss calculations in dashboards

• HR: Protect employee salaries and confidential data

• Project Management: Lock timelines while allowing task updates

• Sales: Protect formulas calculating commissions or targets

7. Practice Questions

1. Lock all formula cells and leave input cells editable.

2. Protect a worksheet with a password allowing users to select only unlocked cells.

3. Protect workbook structure to prevent adding or deleting sheets.

4. Hide formulas in a financial calculation sheet.

5. Explain why locking cells alone does not prevent editing without sheet protection.