Practice 15: Custom Number Formats & Cell Protection

Key Topics & Core Concepts

Custom Format Codes: Applying multi-argument number format masks (Positive; Negative; Zero; Text) with currency alignment.
Cell Locking Mechanics: Clearing the Locked property on input cells while maintaining default locks on formula cells.
Worksheet Protection: Enabling worksheet protection with password control to prevent unauthorized formula modification.
Workbook Structure Security: Protecting the entire workbook structure to prevent tab insertion, deletion, or renaming.

Assignment Overview & Deliverables

In this practice exercise, you will format consolidated financial figures with custom display codes, unlock student input ranges, and secure worksheet formulas and workbook tabs against unintentional edits.

Starter Workbook: Download PerfectRentals-05-1.xlsx (or continue with your completed file from Practice 14)
Completed File Name: [your initials] Practice15_Protection.xlsx
Active Worksheets: Consolidation and Quarter 1

Step-by-Step Instructions

Step 1: Open Starter & Review Consolidation Sheet

Download and open PerfectRentals-05-1.xlsx in Excel (or continue from Practice 14).
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice15_Protection.xlsx.
Click the Consolidation worksheet tab.

Step 2: Apply Custom Accounting Format Masks

Select the consolidated figures across B5:F9.
Press Ctrl + 1 (or Cmd + 1) to open the Format Cells dialog.
Under Category, choose Custom.
In the Type field, enter the standard 4-section mask: $#,##0;($#,##0);"-";@.
Click OK. Verify that zero values render cleanly as centered hyphens.

Step 3: Unlock User Data-Entry Input Cells

Click the Quarter 1 worksheet tab.
Select the raw rental nights and revenue input cells: B5:E8.
Open Format Cells (Ctrl + 1) and go to the Protection tab.
Uncheck the Locked checkbox. Click OK.
Notice that formula cells in row 9 and column F remain Locked by default.

Step 4: Enable Worksheet Protection

On the Ribbon, go to the Review tab and click Protect Sheet.
In the dialog, leave the password blank (or use course test convention), ensuring Select locked cells and Select unlocked cells are both checked.
Click OK.
Test your protection: attempt to type in cell B9 (Total formula) to confirm Excel prevents editing. Next, test cell B5 to confirm that unlocked input values can still be modified.

Step 5: Enforce Workbook Structure Protection

On the Review tab, click Protect Workbook.
Ensure Structure is checked. Leave the password blank and click OK.
Right-click any worksheet tab and confirm that options to Insert, Delete, and Rename are grayed out.
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Custom Formats	4-part format mask (`$#,##0;($#,##0);"-";@`) correctly implemented.
Cell Locking	Input cell range `B5:E8` unlocked while formulas remain locked.
Sheet Protection	Worksheet protection active, shielding formula cells from unauthorized edits.
Workbook Security	Workbook structure protected against sheet addition, deletion, and renaming.