Do you ever get frustrated when you reopen a spreadsheet to find that your important data has been changed? Not only can it lead to inaccurate data or loss of critical information, but it can also cause repercussions for the rest of your Google Sheets file. Learning how to lock in Google Sheets is a simple solution to gain complete control over certain data, avoid human error, and protect important formulae vital to your spreadsheet.
This article will first show you how to lock cells, lock rows, lock columns, and even lock sheets in Google Sheets so that only you can edit them. It will then demonstrate how you can give editing permission to other users of your choice or leave a warning. Finally, it will show you how to unlock anything you have locked.
Excel also allows you to lock and unlock your work in spreadsheets, although this
technique is different.
How to lock cells in Google Sheets?
The following steps will show you how to lock/protect a cell in Google Sheets
- 1. Right-click on the cell you want to lock to open the pop-up menu and go to︙View more cell actions > Protect range.
How to lock cells in Google sheets - Protect range
- 2. In the sidebar that appears, click “+ Add a sheet or range”.
How to lock cells in Google sheets - Add sheet or range
- 3. Confirm the cell you want to lock matches the value in the sidebar [B4 in the example] and click “Set permissions”.
How to lock cells in Google sheets - Set permissions
- 4. Confirm that “Only you” is selected from the drop menu in the window and click “Done”.
How to lock cells in Google sheets - Confirm only you
How to Lock Cells in Excel? [Cells, Sheets & Formulas]
Cells in Excel spreadsheets can be locked to prevent accidental edits. Here's how to lock specific cells, worksheets and formulas in Excel
READ MOREREAD MORE
How to lock a row in Google Sheets?
To lock/protect a row in Google Sheets, you follow the same steps laid out above. This time, however, you select a row rather than an individual cell:
- 1. Right-click on the row you want to lock to open the pop-up menu and go to︙View more cell actions > Protect range.
How to lock row in Google sheets - Protect range
- 2. Confirm the row you want to lock matches the value in the sidebar [4:4 in the example] and click “Set permissions”.
How to lock row in Google sheets - Set permissions
3. Confirm that “Only you” is selected from the drop menu in the window and click “Done”.
How to lock row in Google sheets - Confirm only you
How to lock a column in Google Sheets?
The same steps apply if you want to lock/protect a column in Google Sheets:
1. Right-click on the column you want to lock to open the pop-up menu and go to︙View more cell actions > Protect range.
How to lock column in Google sheets - Protect range
- 2. Confirm the column you want to lock matches the value in the sidebar [B:B in the example] and click “Set permissions”.
How to lock column in Google sheets - Set permissions
3. Confirm that “Only you” is selected from the drop menu in the window and click “Done”.
How to lock column in Google sheets - Confirm only you
*Another practical function in Google Sheets is freezing rows/columns. This is useful for large spreadsheets where you want to avoid scrolling up and down constantly to find certain values. Freezing a row/column pins it to your screen so you have locked cells when scrolling.
How to lock a Google Sheet?
When it comes to locking an entire Google sheet, the steps are only slightly different:
- 1. Right-click on the sheet you want to lock to open the pop-up menu and go to "Protect sheet".
How to lock a Google sheet - Protect sheet
2. Confirm the sheet you want to lock matches the value in the sidebar [“Sheet1” in the example] and click “Set permissions”.
How to lock a Google sheet - Set permissions
3. Confirm that “Only you” is selected from the drop menu in the window and click “Done”
How to lock a Google sheet - Confirm Only You
Range editing permissions and warning messages
When you click “Set permissions” while locking a cell, the window that opens is called “Range editing permissions”.
This window allows you to choose who can edit your chosen cell or to leave a warning message for other users. The following steps will show you how to give editing permission to other users:
1. Follow the first three steps from our instructions on "How to lock cells in Google Sheets" section.
2. When the “Range editing permissions” window opens, click the drop menu containing “Only you” and select “Custom”.
Editing permissions - Custom
3. In the box labeled “Add editors:”, insert the names of the users to whom you want to give permission and click “Done”.
Editing permissions - Add editors
These users should now have the ability to edit your chosen cell.
To leave a
warning message for other users when the Range editing permissions menu is open, select “Show a warning when editing this range” and click “Done”.
Editing permissions - Show warning
Although other users can still edit the contents of your chosen cell, the warning informs them that you want to protect this value.
You can also warn or reject users from editing important formulas within your Google Sheet. Often there are several cells that impact a formula used within Google Sheets - one accidental edit can impact the entire function. You can use conditional locking to either warn users or reject edits
entirely on specific cells that are linked to your Google Sheet formulas.
How to unlock cells in Google Sheets?
To unlock a cell that you have locked, follow these four simple steps:
1. Go to Data > Protect sheets and ranges.
Unlock cells in Google Sheets - Protect
2. Click the locked cell in the sidebar at the right side of your screen.
Unlock cells in Google Sheets - Click locked cell
3. Click the trash can symbol beside the description box.
Unlock cells in Google Sheets - Click the trash symbol
4. Confirm the unlocking by clicking “Remove”
Unlock cells in Google Sheets - Remove
How to protect Google Sheets data with Layer?
Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.
Using Layer, you can:
- Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
- Review & Track: Consolidate input, and easily track changes.
- Collaborate: Define, assign, and automate tasks and set deadlines.
Sign up for early access and start automating your Google Sheets workflows with Layer!
Conclusion
Despite Google Sheets’ many useful functions, from IMPORTRANGE to GOOGLEFINANCE, its ability to allow users to digitally collaborate is probably its greatest asset. However, as time has proven, even this incredibly useful facility can cause repercussions when important data is accidentally edited or critically lost.
It’s no surprise that learning how to lock rows, columns or even entire
spreadsheets within Google Sheets is the best way to not only protect important data and avoid human error but to also save those complex formulas you’ve developed that optimize your results. So, whether you’re calculating current liabilities or doing payroll, knowing how to protect cells, rows, columns, and sheets in Google Sheets is a must.