Description
In this video I'll show you how to lock and protect specific cells from being edited in Google Sheets. To demonstrate this, we will use our list of the top 200 individual comic issues to lock our primary data columns while keeping a notes section open for collaboration.
Timestamps:
Introduction: 0:00
Steps to Lock and Protect Specific Cells From Being Edited in Google Sheets: 0:08
Unlock and Remove a Protected Range: 1:50
Lock an Entire Sheet Except Certain Cells: 2:16
Show a Warning Instead of Restricting Edits: 3:52
Conclusion: 4:34
Check out my website for other great tutorials about all kinds of technology: https://maxdalton.how/
Support this channel by becoming a member: https://www.youtube.com/channel/UCBbDWMccTJCL0WmbMHNLZIw/join
#googlesheets #googlesheetstutorial #techtips #tutorial
Video Transcript:
Step 1. Open your spreadsheet in Google Sheets and highlight the exact range of cells you want to protect. For our comic book list, let's say you want to protect columns A through G, which contain the Title, Issue Number, Creator names, Publisher, Year, and Genre, so nobody can alter the main records.
Step 2. Right-click anywhere inside the highlighted data range, scroll down the context menu, click "View more cell actions," and then select "Protect Range."
Step 3. Review the Protected Sheets and Ranges sidebar that opens on the right side of your screen, where you can type a descriptive label for your lock, like Core Comic Data.
Step 4. Confirm that the cell range listed in the box matches your intent, which for this example should show columns A through G, and then click the "Set Permissions" button.
Step 5. Choose your restriction level in the Range Editing Permissions dialog box. Select "Restrict who can edit this range," and then set it to either "Only You" or choose "Custom" to specify exactly which email addresses are allowed to make changes.
Step 6. Click "Done" to save your settings. A small padlock icon will appear next to the named range in your sidebar, confirming that those specific cells are now securely locked for anyone who isn't authorized.
You can remove this lock at any time by right-clicking in your data set, clicking "View More Cell Actions," and clicking "Protect Range." Click the "Trash Can" icon next to the locked cell range you want to unlock. A window pops up asking if you're sure you want to remove this protected range. Click "Remove." Your cells will be unlocked and unprotected immediately.
If you are managing a massive spreadsheet where you want to lock almost everything except for one or two specific data entry columns, it is actually much faster to protect the entire worksheet and create an exception list rather than locking individual cell blocks one by one. You can do this by clicking "View More Cell Actions," and clicking "Protect Range." The Protected Sheets and Ranges sidebar window opens. Click "Add a Sheet or Range," and then click "Sheet." Select the sheet you want to lock from the drop-down menu, and then click to select "Except Certain Cells." Type or highlight the exact column coordinates where your team is allowed to type, such as an empty Column H for personal user notes. This instantly locks the entire sheet from top to bottom while leaving that single column perfectly open for collaborator input. You can click "Add Another Range" to continue adding more individual cells or cell ranges you want to make editable within the sheet. Click "OK" and then click "Set Permissions" when you're done. Select "Restrict who can edit this range," and then set it to either "Only You" or choose "Custom" to specify exactly which email addresses are allowed to make changes. Click Done to save your settings. A small padlock icon will appear next to the named range in your sidebar, confirming that your Google Sheets sheet is locked, with the exception of those defined cells.
It is also highly helpful to know that you don't have to completely lock people out to prevent accidental edits. When you are setting your editing permissions, you can select the option that says Show a warning when editing this range instead of strictly restricting access. This creates a soft lock on your cells. If a collaborator tries to double-click and type over a protected title or formula, Google Sheets will pop up a warning message asking them if they are sure they want to proceed. This is an excellent, less restrictive option when working with trusted team members who just need a safety net to prevent accidental keystrokes from scrambling your data.