Create, edit and collaborate better with these Google Sheets tips and tricks

Modified 21 March 2017 by Ian Weatherhogg

Protect your Google Spreadsheet data from accidental or malicious changes with these 3 methods.

The ability to share and collaborate on a spreadsheet in Google Apps is an excellent feature, but it may mean you need to look more carefully at protecting your data from unwanted edits. There are 3 key ways to do this.

Protect a Worksheet

Protect individual worksheets in a Google Spreadsheet. To do this, select Tools from the menu and choose Protect sheet. You have the option to set permissions, much like a Google Doc, that will give editing rights to: anyone invited as a collaborator; only you; or a list of collaborators. If you need to stop collaborators from editing certain cells, you could place those cells in a "Results" worksheet and make that read-only. Formulas can reference any worksheet so cells that need data entry can be "opened up" and cells that have a formula can be "locked" in a protected worksheet.

Set Up Notifications

Get notified of any changes in a Google Spreadsheet by setting up notification rules. To do this, select Tools from the menu and choose Notification rules. You have the option to be notified when changes are made to: the spreadsheet; a specific worksheet; a cell or cell range; collaborators; or (if the spreadsheet is joined to a form) when a user submits a form. Notifications can be sent as soon as a change is made or as a daily summary.

The first 2 options are straightforward changes that offer good options to protect most spreadsheets, by stopping editing and notifying you if changes are made. But what if you need to protect a series of cells or an individual cell? Google Spreadsheets currently doesn't have this functionality, so a workaround is required.

Set Data Validation

Stop individual cells, or a range of cells, from being edited by setting a value that the cell must have. Choose to set a Number, Text, Date or Items from a List. Each criteria has further settings, for example: a Number cell can be a range or maximum value; a Text cell can be set to contain or not contain word(s); a Date cell can be set to be a valid date only; and a List cell can only be populated from a list. A further option enables you to either warn the editor that validation rules have not been met but allow the change, or disallow any changes that do not meet the criteria.

Data validation is a very useful method to ensure that cells containing a formula are not mistakenly edited. To do this:

  • select the cell or range you want to be validated
  • select the Text criteria and set the second drop down to "equals"
  • in the blank box add your cell formula
  • untick the Allow invalid data box.

Update - You can now protect individual cells in a Google Spreadsheet.

How do you protect your Google Spreadsheets? Are these tips useful, do you have any more to share? Let us know in the comments.