Why is Google spreadsheet security important? Whether the changes are on purpose or accidental, the best way to prevent them is to implement hard controls in the spreadsheet that leaves your data locked to changes.
In this tutorial, you'll learn how to lock cells in Google Sheets. You'll also learn sheet protection to ensure that critical data is never removed from your spreadsheets. Let's jump in.
Why Protect Your Sheets?
If you want to control who canaccessthe spreadsheet, you need to think about Google spreadsheet permissions. For Sheets, that means that you can control who has a link to view or edit the workbook.
But you can also protect the Sheets and workbooks to control the changes that can be made to the spreadsheet. That's the focus of this tutorial, and here are some reasons why you might want to do that instead of just preventing access to the spreadsheet altogether:
- Many spreadsheets are structured with "input" cells where you set key variables, and other calculation cells that are based on those inputs. In those cases, keeping the inputs protected makes sense.
- If you're going to share your spreadsheet with a large audience, it helps if you can "user-proof" the Sheet by locking key cells and ensuring that they aren't accidentally modified.
- In general, spreadsheets often contain important calculations that you'll want to ensure are protected from unwanted edits and preserve the integrity of the calculations.
Any of these are reason enough to learn Sheets cell protections. Let's see how those features work.
How to Protect Google Sheets (Watch & Learn)
Let's walk through the key techniques to protect cells in a Google spreadsheet in this screencast. You'll see me apply some of the protection tools to ensure that no unintended changes are made to your spreadsheets.
In this tutorial, you're going to see that there are really two unique approaches to adding spreadsheet protections:
- You can protectcertaincells and ranges,which is useful when you want to let the user make changes to most of the spreadsheet but lock in certain cells.
- You can protect the entirespreadsheet,with the option to setspecific cells that you will let the user edit.
Read on for a guide (including screenshots) to applying all of these techniques and more.
How to Lock Cells in Google Sheets
Sometimes, it's only a certain set of cells that you want to lock up in a spreadsheet. In those cases, here's how to lock (specific) cells in Google Sheets.
First up, start off by highlighting a cell or range of cells that you want to protect. Then, go to theData > Protected sheets and rangesmenu to start protecting these cells. See the example below for the menu option.
Use theData > Protected sheets and rangesmenu option to start protecting specific cells in a Google Sheet.
I always recommend giving this protected range a name in the sidebar. For my example, I'll simply refer to it asTax Rate.Then, click onSet permissionsas you can see below.
The sidebar allows you to set a name for this range to help remind you of what cells you're protecting.
Now, choose the options you want to apply on the next menu I show below. I'll set it to"Restrict who can edit this range", and leave it set to"Only you"for this initial example.
Choose who can edit the protected range by choosing "Restrict who can edit this range..."
Another option is to change the dropdown option toCustomto add more collaborators who can edit this data. Most will be locked out of editing the cells, but anyone you specify can edit the selected range.
Add specific users that can edit your protected ranges to allow collaborators to update the spreadsheet.
One of the advantages of Sheets is the ability toallowsomecells to be modified by users that you invite to collaborate. Applying these settings ensures that trusted collaborators can apply updates to the spreadsheet.
Soft Warnings for Editing Cells
What if you don't want tototallyrestrict the editing of certain cells? You can show a warning before specific cells are edited to call out that specific cells are important and shouldn't be changed.
To put a warning on editing cells, follow the same steps covered above to get started. But when you reach theRange editing permissionswindow, tick theShow a warning...option before you press Done to add the warning.
On the Range editing permissions window, choose Show a warning when editing this range to place a "soft" protection on editing cells.
Each time that these cells are edited, the user will see a warning like the one below, warning that the cells aren't meant to be edited.
The warning message will let a user know that the cell isn't intended to be changed, but still allows them to do so.
This approach is different from placing hard restrictions on editing a spreadsheet's cells. It's more like a warning than a real restriction, and is a good idea when you want to let advanced users make tweaks.
Google Sheet Protection
Now that we've learned how to protect individual cells in a Google spreadsheet, let's tackle the practice of protecting entire sheets. In this approach, the entire sheet is protected by default and you'll need to specify cells that can be edited.
To protect an entire sheet, browse back to theData > Protected sheets and rangesmenu. This time, you don't have to select any cells before you start. Just click onSheet,and then choose the spreadsheet to protect from the dropdown menu.
Protect an entire sheet from theData > Protected sheets and rangesmenu, and choose theSheetoption to choose a spreadsheet.
Now, you've protected the entire sheet. Changes won't be possible unless you allow certain cells (more on that in a minute.) See the screenshot below for an example of what will happen if someone tries to make changes to the sheet.
Protected cells or sheets will throw this error any time that you try to make changes to it.
Want to leave a few cells or ranges open for changes? Let's learn more about it.
Exceptions to Sheet Protections
When you have an entire spreadsheet protected, you can still allow specific, selected cells or ranges to be edited. If you want most of the spreadsheet protected, I recommend starting off by protecting the entire sheet, and then allowing a few cells to be editable.
Return to theData > Protected Sheets & Rangesmenu to open up the options. With the Sheet option selected, this time make sure that you tick theExcept certain cellsoption, then click
You can add multiple ranges by clicking onAdd another range. Keep adding more selections of cells to allow edits and changes, and clickSet Permissionswhen done.
You can add exceptions to protected sheets to allow changes by clicking on theExcept certain cellsmenu option and picking out certain cells to leave open to edits.
It's easy to add these exceptions if you want to allow for some changes in your spreadsheet.
You can manage all of these by returning to thatData > Protected Sheets & Rangesmenu. You could add to, remove, or adjust any of the sheet protections that you've set up to fine-tune those spreadsheet protections.
Recap & Keep Learning
Spreadsheets are so important that it sometimes becomes crucial that the data in them is protected carefully. When that's the case, think about the key tools that you can use to ensure that your spreadsheet is protected.
For more tutorials on getting the most from Google Sheets, give some of the tutorials below a look:
How to Use (Timesaving) Google Sheets Keyboard Shortcuts
Andrew Childress07 Mar 2018
Google Sheets to Excel: How to Move Back & Forth Between Spreadsheets
Andrew Childress16 Feb 2018
How to Use Pivot Tables in Google Sheets (Ultimate Guide)
Andrew Childress15 Jun 2017
How to Quickly Convert Excel Spreadsheets to Google Sheets
Andrew Childress10 Feb 2018
How do you protect your Google spreadsheets? Do you restrict users from entering the spreadsheet at all, or are you implementing cell-by-cell or sheet-by-sheet controls? Let me know in the comments section below.