0
0
Google Sheetsspreadsheet~8 mins

Protected sheets and ranges in Google Sheets - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Protected sheets and ranges
Goal

Protect sensitive data in a sales report by restricting editing on certain sheets and ranges while allowing users to update only specific cells.

Sample Data
RegionSalespersonSales Q1Sales Q2Notes
EastAlice10001200Good growth
WestBob9001100Needs follow-up
NorthCharlie11001300Exceeded target
SouthDiana9501050Stable
EastEva12001250Top performer
Dashboard Components
  • KPI Card: Total Sales Q1 with formula =SUBTOTAL(9,C2:C6) showing 5150
  • KPI Card: Total Sales Q2 with formula =SUBTOTAL(9,D2:D6) showing 5800
  • Table: Sales Data showing all columns from the sample data
  • Protected Sheet: Summary sheet is protected to prevent editing by most users
  • Protected Range: Notes column (E2:E6) is editable only by managers; other users cannot edit these cells
Dashboard Layout
+----------------------+----------------------+
|   Total Sales Q1     |    Total Sales Q2    |
|      (KPI Card)      |      (KPI Card)      |
+----------------------+----------------------+
|                                              |
|               Sales Data Table                |
|                                              |
+----------------------------------------------+

Note: 'Summary' sheet is protected; 'Notes' column is protected range editable only by managers.
Interactivity

Users can update the Notes column only if they have permission. Other cells and the Summary sheet are locked to prevent accidental changes. This ensures data integrity while allowing managers to add comments.

Self Check

If you add a filter to show only the East region, which components update?

  • The Sales Data Table updates to show only East region rows.
  • The Total Sales Q1 and Total Sales Q2 KPI cards update to sum only the filtered rows.
  • The Protected Sheet and Protected Range settings remain unchanged.
Key Result
A sales dashboard with protected sheets and ranges allowing managers to edit notes while keeping sales data secure.