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.

Get Guiding Tech articles delivered to your inbox.

We will never share your address. Unsubscribe at any time.

Post a Comment or Discuss at Guiding Tech Forums

Show archived comments (6)

Archived Comments

  1. MRideos says:

    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 ?

  2. Chhoeng Monny says:
    • TT says:

      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”

  3. jeff cox says:

    The protection schematic is horrible in Google Docs. You can’t simply “unlock” certain cells and then protect the whole sheet. You have to proactively work around the cells you want unprotected, which in some cases can create a patchwork.

    That’s just stupid. There’s no other way to say it. Google Docs, you either need to grow up and provide the functionality that Excel does or get the hell out of the way and quit pretending you’re something you’re NOT!

    I am so frustrated that I have to use this freaking thing for a client!

  4. Oliver Francis C. Guzon says:

    i wanted to hide the formula in cells so that others cant copy how it is done.. can google docs do this like in excel? thanks

  5. bluephy says:

    Is there a way to restrict editing to have users be able to highlight fields? When the user, say, us done working on a row, they mark it gray. I dont want people messing with the format. :)