How to Restrict Editing Cells in a Google Docs Spreadsheet

Ads by Google

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).

Google Docs Protected Ranges

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.

Google Docs Protected Ranges

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.

Ads by Google

Google Docs 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.

Google Docs Protected Ranges

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.

Google Docs Protected Ranges

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

Google Docs Protected Ranges

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.

Don't Miss This Essential Guide On Chrome

The Ultimate Chrome Productivity Guide Ebook

Did you know that by employing just one small tip, you could make Chrome significantly faster and avoid crashes? Do you know the easiest way to manage and restore lost tabs? And the quickest way to locate a tab?


Chrome is a powerful browser but unless you know how to customize it properly and make use of its hidden features, you’d probably get frustrated with it soon. This ebook is your one-stop resource for using it productively.

Download the Chrome ebookorange-left-arrow




  • MRideos

    But how lock only row or collum only other can edit other people and what about annonymous they can edit only not locked rows how to do it ?

  • Chhoeng Monny

    Dear Friend,

    When I do follow your tutorial, after tick the protect box and click on Done, in the permission setting I can see only Can edit & Can comment, Can view don’t have.

    What is wrong?

    Sincerely Yours,
    Monny
    chhoeng.monny@gmail.com

    • TT

      I’m having the same issue. My document can be edited by anyone with the link so I am trying to lock down certain cells with formulae. However, like Monny, I only see “Can edit” and “Can comment” not “Can view”