Protected sheets and ranges in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator managing a shared Google Sheet for the sales team.

📋 Request: Your manager wants you to protect certain parts of the sheet so only specific team members can edit them, while others can view but not change.

📊 Data: You have a Google Sheet with sales data including columns for Salesperson, Region, Sales Amount, and Notes. The Notes column should be editable only by the manager, while other columns can be edited by the team.

🎯 Deliverable: Set up protection on the Notes column so only the manager can edit it, and share the sheet with the team with view-only access to that column.

Sample Data

Salesperson	Region	Sales Amount	Notes
Alice	East	5000	Follow up next week
Bob	West	7000	Client requested discount
Charlie	North	6000	New lead
Diana	South	5500	Pending approval
Eva	East	7200	Confirmed order

1

Step 1: Open the Google Sheet and select the entire Notes column (column D).

Expected Result

The Notes column is highlighted.

2

Step 2: Click on Data in the menu, then select Protect sheets and ranges.

Expected Result

The Protect sheets and ranges sidebar opens.

3

Step 3: In the sidebar, enter a description like 'Protect Notes column' and confirm the range is D2:D6 (the Notes cells).

Expected Result

The range D2:D6 is set for protection.

4

Step 4: Click Set permissions, then choose 'Only you' or specify the manager's email to allow only them to edit.

Expected Result

Only the manager can edit the Notes column; others cannot.

5

Step 5: Share the sheet with the sales team with Editor access but inform them the Notes column is protected.

Expected Result

Team members can edit other columns but cannot change the Notes column.

6

Step 6: Test by logging in as a team member and try to edit a cell in the Notes column.

Expected Result

Editing is blocked with a message saying the cell is protected.

Final Result

Salesperson | Region | Sales Amount | Notes (Protected)
-------------------------------------------------------
Alice       | East   | 5000         | Follow up next week
Bob         | West   | 7000         | Client requested discount
Charlie     | North  | 6000         | New lead
Diana       | South  | 5500         | Pending approval
Eva         | East   | 7200         | Confirmed order

Note: Only the manager can edit the Notes column.

✓The Notes column is protected so only the manager can edit it.

✓Sales team members can edit other columns freely.

✓Protection prevents accidental or unauthorized changes to important notes.

Bonus Challenge

Set up a protected sheet for the entire sales data except for a single column where the team can enter their monthly targets.

Show Hint

Use the Protect sheets and ranges feature to protect the whole sheet, then add an exception range for the targets column.