I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new versions she overwritten the formulas.

Any idea how to approach that from technical point of view?

I searched the net and it seems to me that it is not doable right now in Google Apps.

8 Answers 8


It is not currently possible to lock a given cell. However, you can create new sheets in the spreadsheet and lock those. If you really want to prevent her from changing the formulas, you could have all of the results/formulas be in a second sheet, referring to the first sheet's data, then lock the second sheet so it would be read-only to her.

In order to reference data from one sheet in another, use the syntax sheetname!range. For example, to take the average of sheet1's column A in rows 2 through 10, you would use Average(sheet1!A2:A10).

To add sheets, use the + sign on the bottom right side of the spreadsheet. You can switch between and rename sheets using the tabs and dropdown arrows on the lower left side of the spreadsheet.

google docs screenshot

  • very very nice explanation. Thank you. Not sure if it is usable in case you have simple table and want to calculate sums of colums and rows. User usually wants to see the sums and data on one screen. Well,if she keeps deleting formulas I have a solution for that :-)
    – Radek
    Nov 9, 2010 at 4:53
  • Happy to be of help. There is also a function called importrange which can import a whole section of one sheet into another one. Using that, you could import the entire first sheet's data into the second sheet, and then display all the equations on there near the data they correspond to. Google has documentation for it, which you may find useful. It still wouldn't be a perfect solution, but would be closer since it would display all the results on one page with the original data at least.
    – nhinkle
    Nov 9, 2010 at 5:54
  • looks like this is the only solution at this time .... thank you
    – Radek
    Dec 13, 2010 at 12:08
  • No problem. If you do eventually find a better solution, please update your post; I'd be interested to hear about it.
    – nhinkle
    Dec 13, 2010 at 19:06

I found out that if you create a comment attached the cells that should not be edited, you can have it warn someone when they hover the mouse over the cell to edit it. It, of course, doesn't actually lock the cell, but hopefully, enough times seeing the note pop up and maybe your mom will stop manually changing those numbers.

I think you can only add comments one cell at a time, so depending on how many cells are formulas, this solution may not be viable.

  • you're right that I cannot use it in this case as the formulas are in the whole column but it will be great solution in some cases...
    – Radek
    Feb 17, 2011 at 22:33

The answer as per today is "Named and protected ranges" under Data menu. Works smoothly.

  • 3
    The limitation of this solution is that the owner of the spreadsheet cannot lock cells against herself. To solve the OP's problem, the OP must also own the spreadsheet and share it with mom.
    – MetaEd
    Dec 7, 2012 at 16:32
  • I cannot see any limitation to this solution. Could you elaborate? Owner change protect range of cells against himself too.
    – Radek
    Mar 24, 2013 at 23:06

You can use google apps script to write your own locking mechanism in javascript.

For example:

Lets say you have a formula in column B and it is equal to the value in column A + 1. If we look at cell (B3) it should have a formula in it of "=A3+1"

To write a script to protect this from modification you would write something like this using Script Editor:

function onEdit(event)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var CellRow = SpreadsheetApp.getActiveRange().getRow();
  var CellColumn = SpreadsheetApp.getActiveRange().getColumn();

  if (CellColumn == 2){
    sheet.getRange(CellRow, CellColumn).setFormula("=A"+CellRow+"+1");
    Browser.msgBox("DON'T MESS WITH THE FORMULAS!");

If you get it working every time someone edits any cell in column B the script will automatically put your formula back in and give your a warning message.


A workaround this is to use data validation.

Go to Data menu, click validation..., then define your range of cells that you need to "protect". Ex: SheetA!F2:F9999. Then select Criteria: Text, equals. Then enter into the box something like: "=*" without the quotation marks. Uncheck the "allow invalid data..." box. Save.

This should only let user enter a value that begins with = sign (which is the beginning of your formula to protect). If they enter the = sign then they can modify that cell.

The asterisk allows anything after the = to be valid. You could specify it further to protect it more: Ex: Text, equals, =sum(*), or =AVG(B*:D*), etc...


Try making the boxes where she is supposed to be entering the text a different colour and tell her to write only in the coloured boxes.


Simple solution from back when Excel had the same problem:

Another way you can do this is through Data Validation. After you have a cell populated with the desired contents,

Choose Data Validation and use the Custom option. For the formula enter =""

Then select "Reject input".


In Microsoft Excel I can lock the entire sheet and then selectively unlock individual cells or ranges.

In Google Spreadsheets, I lock all cells in one or more separate ranges except the cells that are to be available for input by other users. See this example of a Google sheet I made today: sample

One variation from Excel is that all in the Google sheet all locked cells have grey diagonal stripes as background.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.