Being able to lock cells in Google Sheets may be very useful, particularly if you work in a business setting where confidentiality is paramount. Even though you can always give someone a spreadsheet that they cannot modify, there are times when we want users to be able to edit certain sections of the spreadsheet.
With that in mind, how can you ensure that other cells in your spreadsheets are not altered while sending them out to prospective workers or guests? How do you do it? By locking your cells. Currently, there are several different methods to lock cells in Google Sheets, and we’ll go through each of those variants in this post.
4 Methods for Locking Cells In Google Sheets
- Locking certain cells while allowing other cells to be altered
- Cells may be locked, however other users can modify the cells that are locked.
- When attempting to edit, display a warning, but allow editing.
- Locking the entire sheet
These are the four different methods for locking cells in Google Sheets. Further on, we’ll go through each variant in more detail one by one, so stay tuned. Additionally, be sure to read through to the conclusion to learn how to unlock or modify the permissions of certain cells.
For those of you who are unfamiliar with Google Sheets, there is no need to be frightened by the program. This post was created with novices in mind, so you should have no trouble following along with the instructions.
Let’s get going!
What is the point of locking cells in Google Sheets in the first place?
Numerous factors support the notion of keeping your cells locked at all times. Think about it: you want a spreadsheet that keeps track of your workers’ whereabouts at any given time. You do not want your workers to tamper with each other’s spreadsheets, though, to prevent their data from being compromised.
Especially if you are unable to lock your cells, dealing with this issue may be a frustrating experience. Ideally, the only way to do this would be to offer a different spreadsheet for each person, but the bother of verifying each spreadsheet may be both time-consuming and expensive for the company.
You will now be able to guarantee that none of your employees will be able to change the data of their coworkers by restricting access to certain regions for specific employees.
You’ll be able to keep track of all of their actions on a single spreadsheet while also guaranteeing the security of their inputs in the process.
So, with that in mind, let’s get started on finding out how to lock certain cells inside your spreadsheet. What you need to know is how to accomplish it!
Method 1: Locking Specific Cells in a Database
Locking particular cells enables you to choose individual or cell groups that you wish to be protected from being modified or deleted. This is considerably more particular, and it allows you to make modifications that will not have an impact on the permissions of your whole page. When you wish to secure certain parts of your website, such as headers, titles, and so on, we suggest that you use this approach.
Here are the easy steps to follow for this technique.
Step 1: Open a Google Sheets spreadsheet in a new tab.
First and foremost, we’ll need a spreadsheet so that you can follow along with the lesson on your computer screen. You are welcome to utilize an existing spreadsheet or the document that you are attempting to lock as a starting point.
Those of you who do not already have a spreadsheet may be required to create one from the start. All you have to do is open up Google Sheets and create a new blank spreadsheet.
To follow along with the activity, you may also make a copy of the example spreadsheet that we’ve supplied for your convenience.
Step 2: Populate the cells that you want to protect.
Of course, before anything else, we’ll need to get something inside the cells so that we have something to guard ourselves with. Other than that, locking the cells won’t make much of a difference since they will continue to be blank.
Writing a few words, locking those particular cells, and then passing it over to a buddy to check if they can make any changes to what you put down will be the technique used in this method.
You can see how the bottom image in the example was created by following the steps outlined above. However, you are allowed to design and fill the cells in your spreadsheet in a manner that is uniquely your own.
Step 3: Highlight the specific cells you want to lock.
You’ll need to highlight the cells you want to lock first so that Google Sheets understands which ones you’re talking about. Simply left-clicking with your mouse and dragging your cursor over your selection while holding the left-click button down will do this task quickly and simply. As an alternative, the Shift + arrow keys will function just as well.
Step 4: Locking your cells.
Finally, we’ll begin locking your cells so that only you have the ability to make changes to them. The procedures for doing so are pretty straightforward, so begin by selecting the Data icon located in the top-left corner of your screen. Then choose Protected sheets and ranges.
The columns and rows that you previously marked should be automatically filled in with the data from the range. To continue to the next stage, you may simply click on the Set permissions button.
You should be given the choice to either display a warning or limit who may modify this range; for this step, choose the option that reads ‘Restrict who can edit this range’ from the menu. There, the default choice should be set to Only you; keep it as is and click on Done to close the dialogue box.
Cheers! A cell or cells in your Google Sheets have been successfully locked by you.
spreadsheet. You can see which cell blocks are protected by hovering your cursor over the new permissions you’ve set. Go ahead and share or ask your friends to edit the document to check whether they can make any changes to the fields that are protected.
Method 2: Locking Cells but giving other people the ability to edit the contents
What if you wish to provide the power to modify certain cells to a specific individual, although you have prohibited other people from doing so in the first place?
For example, your workers may no longer be able to change certain cells, but if you have an administrator, he or she should be able to update those cells as needed.
Having the ability to provide individuals editing rights in these types of circumstances may be a very valuable tool. You may accomplish exactly that by following the steps outlined below, which will allow your coworkers to modify restricted cells in your spreadsheet.
Step 1: Open up a spreadsheet.
It is quite OK to utilize an existing spreadsheet that you are already working with. The procedures outlined here will work for any spreadsheet that is hosted on Google Sheets. For those who don’t already have a spreadsheet to work with, we hope that you created one when following the preceding procedure.
If you haven’t already done so, you can always print off a copy of this example document to use as a guide to help you get started.
Step 2: Populate your spreadsheet with sample data.
For those of you who have just created a fresh spreadsheet, you’ll want to begin by populating your document with something. For those of you who already have a spreadsheet, have followed the instructions from the previous approach, or have created a copy using the link above, you may skip this step.
Step 3: Highlight the cells you want to lock and give edit access to.
Select the cells that you wish to keep locked at this point. We’ll be locking them and granting edit access to a buddy or coworker at your place of business. This may be accomplished by simply left-clicking and dragging your cursor over the chosen region on the screen. Alternatively, you may use the Shift + arrow keys on your keyboard to choose the cells you want to highlight.
Step 4: Lock your cells.
When your cells are highlighted, click on the Data button in the top-left corner of your window to lock them. From there, drag your mouse down and choose Protected sheets and ranges from the drop-down menu.
The Range box should be pre-populated with the columns and rows that you’ve previously marked unless you specify otherwise. To go to the next step, click on Set permissions and then choose Restrict who may modify this range from the drop-down menu. Now, choose Only you from the drop-down menu and change the option to Custom.
By entering in their email address or selecting from the list of individuals who are currently shown on your screen, you may add the new editors to your team.
Congratulations! In your spreadsheet, you’ve successfully restricted access to particular cells while granting authorization to certain individuals. These editors may be classified as administrators, coworkers, or supervisors. Make sure to share the editable link with your friends, particularly those who have the authorization to edit, so that you can see whether they are able to make changes to the cells or not!
Method 3: Protecting An Entire Sheet
What happens next now that you know how to safeguard individual cells and provide rights to specific cell blocks is up to you. The following procedure will walk you through the procedures necessary to lock or safeguard a whole page of the paper. This is a valuable talent to have, particularly when you need someone to just look at a spreadsheet and not make any changes to it.
Instead of safeguarding your whole spreadsheet, the simplest method to do this would be to just provide a link that allows others to see it. You may accomplish this by clicking on the Share icon in the top-right corner of your screen, then clicking on Change underneath the Get link and ensuring that the Anyone with the link option is selected from the drop-down menu.
Before you click on the Copy link, double-check that the link is set to Viewer mode in the Options menu.
The ability to modify or even make comments on your spreadsheet will be denied to anybody who has access to this URL, and they will be limited to viewing the only mode.
However, what if you have several sheets and you just want that person to modify one of them, and you don’t want him to have access to the others? In such instances, you must take precautions to safeguard your sheets.
Step 1: Open up a spreadsheet with multiple sheets.
To begin, you’ll need a spreadsheet with several sheets, which you can get here. We’ve already prepared one for you, so feel free to just copy and paste our example spreadsheet into your own to get started. Alternatively, you may add a new sheet to your spreadsheet by clicking on the addition symbol (+) in the bottom-left corner of the spreadsheet.
Step 2: Right-click the sheet you want to protect and click ‘Protect sheet’.
This step is simple; just right-click the sheet you want to protect and choose Protect sheet from the context menu.
Step 3: Set the permissions.
If everything goes as planned, the ‘Protected sheets & ranges’ window should appear, and you should be routed to the Sheets tab. You may proceed to click on Set permissions to set the editing options if you want. Please feel free to customize this to your liking. Only you will be able to make edits using this technique, so please keep that in mind.
Step 4: Repeat the steps for all the necessary sheets.
As soon as that is completed, just repeat the process for all of the sheets that need to be protected. Afterward, you may proceed to issue an invitation to edit, confident in the knowledge that those locked pages will not be tampered with.
Method 4: Allow Editing, but Show a Warning
When this does not lock your cells, it will display a warning to the user if they attempt to modify a particular column or row while using this method. Having this option comes in useful, particularly when you want to create some reminders without having to write them down directly in the spreadsheet.
You may simply display a warning on your spreadsheet using the following method.
Step 1: Open a spreadsheet on your computer.
To test this technique, you will need a spreadsheet, just as you did with the prior ways. You may use the same spreadsheet that was used in technique 2, or you can click on this link to create a duplicate of the spreadsheet that was used in that method instead. Of course, you may also utilize a spreadsheet that you’ve previously created on your computer.
Step 2: Select the cells on which you wish to put a warning and highlight them.
Before you can do anything, you’ll need to choose the cells that you wish to modify and highlight them. This may be accomplished by dragging your mouse while holding down the left-click button over a certain region. Alternatively, you may utilize the Shift + arrow keys on your keyboard to accomplish the same result.
Step 3: Construct the warning message.
Select Protected sheets and ranges from the Data drop-down menu in the top-left corner of your screen once you have highlighted the cells. Instead of limiting access, choose Show a warning while changing this range from the Set permissions menu.
When you’re done, just click on Done, and you should have an entire section of cells that will display a warning when they’re being changed.
Boom! You’ve successfully set cells to issue a warning while they are being modified, and you may now close the window. Send this to your friends and watch as they encounter the warning error while attempting to modify the cells.
Bonus Method: Removing or Editing Permissions.
When working with cells, you can quickly modify or delete any rights that have been assigned to them by using the Protected sheets and ranges pane. Before you continue, double-check that you aren’t selecting a set of cells to highlight first. If you are in the clear, just click on the Data icon in the top-left corner of your screen and choose Protected sheets and ranges from the drop-down menu that appears.
To your right should be a popup displaying the permissions and settings for your account.
Simply choose the permissions you want to modify or delete and click on the appropriate button. To make changes, you may either click on Change permissions or type in the new range and sheet numbers in the text box.
You may even give the permits a name to make it simpler to distinguish between them. To remove all rights from the window, click on the trash symbol located in the top-right corner of the window.
Ending
We hope that by the time you reach the conclusion of this lesson, you will have learned how to lock cells in Google Sheets. With that being stated, here is a concise summary of everything we covered in this post.
There are many methods for locking cells in Google Sheets. Here are the most common. Lock whole sheets, individual cells, grant rights to those specific cells, and show a warning while someone is changing a sensitive cell are all options you have at your command.
We hope you find this post to be of assistance!
