How to Restrict Editing Cells in a Google Docs Spreadsheet

While power usage still might be Microsoft Excel’s strongpoint, Google Docs and its spreadsheet offers you an alternative especially when you are on the go. It is good enough for 20K rows, and also helps you with web based dashboards. The real-time collaboration feature with multiple members of your team is definitely its selling point. If you are working with light analysis and simpler modeling, Google Docs Spreadsheet is a good choice.

The real-time collaborative feature is a boon, but it could also be a bane if team members start ‘trespassing’ on data cells and sheets all at the same time. For instance, there can be complex formulas that shouldn’t be touched by everyone. It could lead to data-chaos. Google Docs recently solved this problem by introducing restrictions on editing cells (and also another layer of protection where you can also protect entire sheets).

Protect Cell Ranges and Lock Them Down

Let’s demonstrate how protected ranges work with this Google Docs Spreadsheet and the sample data:

Step 1. Open the Google Docs Spreadsheet which you are going to collaboratively work on. Select the cell-ranges you want to protect and lock down. On the menu, go to Data –> Named and protected ranges. Alternatively, you can right-click anywhere on the spreadsheet and select the same option from the context menu.

Note: A named range is a feature that allows you to assign a more memorable name to a cell or a group of cells.

Step 2. In the dialog box which opens up on the right, you can give your Named Range a nickname (keep it short so you can use it in a formula). Click on Protect and Done. You can add multiple protected ranges.

Step 3. A dialog box opens that lists all document collaborators and their level of access. To change a collaborator’s access to the range, click on the drop-down menu to the right of their name and choose to grant them View or Edit access.

Team members may have edit access for the remaining cells but not for the protected one. They will receive an error message if they try to modify the protected cells or ranges.

For protected ranges, team members will see them marked out with a checkered background as you can see in the screen below:

If the background pattern makes it difficult to read spreadsheet content, you can hide protected ranges by pointing your mouse to the View menu and unchecking Protected Ranges. It doesn’t remove the protection but just makes the protected range of cells more readable.

The next time, you try out a spreadsheet in Google Docs, remember the feature. I hope you will come back to this tutorial and take the method forward. Tell us if this walkthrough was helpful.