How to Lock Formulas in Google Sheets

There are many reasons why people use Google Sheets for their projects, including the fact that it’s free and easy to share with others. Whether you’re sending budgeting information to your team at work, are letting clients know about how a payment schedule breaks down, creating a payment plan for your family, or whatever, Google Sheets has the ability to organize complex data.

If there’s a downside to being able to share Google Sheets, it’s that someone might inadvertently change a critical formula. Should that happen, then all of the data could get corrupted or thrown into chaos. This might have you wondering how to lock formulas in Google Sheets.

Thankfully, you can protect your valuable data. We know how hard you’ve worked on your project (no matter what it is), so we’re here to teach you how to protect cells in Google Sheets.

How to Lock Formulas In Google Sheets

When it comes to ensuring that the formulas you added to your Google Sheet don’t get screwed up by a collaborator, there are a couple of different ways that you can apply Google Sheets lock cells.

The first is to add a warning, which lets you and others know that if the data has certain permissions applied and shouldn’t be changed.

One thing to keep in mind with this process is that if you add a cell that already has protections added, the new permissions you add won’t work. This could leave those cells open to edits by others.

Before going through the steps listed below, make sure to check the cells you want to protect to ensure they don’t already have a Google Sheets lock cell in formula protection added to them.

Once you have determined that the cells aren’t currently protected, here’s how to add this Google Sheets lock cell in formula warning:

Step 1: Log in to Your Google Account

The first thing you’ll need to do when learning how to lock formulas in Google Sheets is to log in to your Google Account.

When asked, make sure to type in your username and password.

Step 2: Open the Google Sheet You Want to Protect

After you’ve gained access to your Google Drive, look for the Google Sheet you want to protect and double-click on it.

Step 3: Select the Cells You Want to Protect

Using your cursor, highlight the cells in your sheet that you want to protect.

You’ll know when they’ve been selected because there will be a blue border around them.

Step 4: Open the Data Menu and Protect Sheets and Ranges

After selecting the cells you want to add protection to, at the top of the sheet in the menu tab, click on Data.

When the drop-down menu appears, look for and click on “Protect sheets and ranges.”

Step 5: Add the Sheet or Range of Cells

Clicking on “Protect sheets and ranges” will open a box on the right-hand side of your screen. In it, you will have the ability to enter a description of what you’re doing, as well as add the range of cells (which should already be filled in since you highlighted the cells in the sheet), or you can add the entire sheet.

At this point, you have to decide what you want to do. If you just want to protect a few cells, then you’ll add the range.

If you’re worried that the entire sheet might get messed up, then pick the entire sheet. Your decision will be based on personal preference.

Step 6: Set the Permissions and Click “Done”

Once you’ve decided whether to protect the cells or the entire sheet, click the “Set permissions” button at the bottom of the menu.

A new dialogue box will appear, giving you the following options:

“Show a warning when editing this range”
“Restrict who can edit this range”

To select the warning option, click on the circle until it is filled in with green.

You’ll then need to click the “Done” button at the bottom of the screen.

If you or anyone attempts to change the data in your selected cells, a dialogue box with a warning will pop up.

You or the other person will have the option of canceling the changes or going ahead with editing the data.

Adding this warning is beneficial if you still need to make changes to the sheet but you don’t want anyone else to manipulate the data.

However, if you find that no one should be able to change the data, then you’ll want to lock the cells in your Google Sheets.

How to Lock Cells in Google Sheets from Editing

When asking how to lock formulas in Google Sheets, instead of adding protection with a warning, the following steps will ensure that no one is able to change the data included in the cells. To add this lock to your sheet, you’ll need to follow these steps:

Step 1: Log in to Your Account

If you aren’t already in your Google Drive, input your username and password to gain access to your documents.

Step 2: Open the Sheet You Want to Lock

Find the file you want to lock and double-click on it to open it.

Step 3: Choose the Cells You Want to Lock

Once you’ve gained access to your Google Sheet, you’ll then need to highlight the cells you want to lock.

Use your cursor to highlight those cells. You’ll know when they’ve been selected because there will be a blue border around them.

Step 4: Click on Data and Protect the Cells

At the top of the sheet, look for Data in the tabs and click on it.

When the drop-down menu appears, click on “Protect sheets and ranges.”

A new menu will appear on the right-hand side of your screen.

In the dialogue box, you can type in a description of what you’re locking, or you can leave this blank.

You also have the ability to lock just the cells you have highlighted or the entire sheet — it’s totally up to you how much protection you need to add to your work.

Step 5: Set the Permissions

After deciding whether to lock just the cells or the entire sheet, you’ll then click the green “Set permissions” button.

This will open another menu box with two options. Since you want to lock the cells, click on the circle next to “Restrict who can edit this range.”

From the drop-down menu beneath this option, you can then choose who can make edits. These choices include:

“Only you”
“Custom”
“Copy permissions from another range”

If you want to be the only one who controls the information on this sheet, then you’ll want to choose the first option. Should you find that there are a few others who can change the data, then you’ll pick the second option, which will bring you to another menu.

Within that menu, you’ll be able to add the email addresses of those individuals who are allowed to make changes to the formulas or data in the cells.

Should you pick the last option, yet another menu will open with a list of options available for protection or locking. Pick which one applies. However those cells were protected, that will be applied to your new range of cells as well.

Step 6: Click “Done”

After deciding how you want to lock these cells, you’ll then click the “Done” button at the bottom of the menu.

If you are the only one who has permission to change the data, then when you share this Google Sheet with others, they’ll be able to look at the numbers but not delete or change them.

One thing to keep in mind is that if another person makes a copy of your sheet, they will then become the “owner,” which means they can change the data within the cells. This won’t change anything on your sheet, so your information will stay intact.

How to Fix a Cell in Google Sheets

In addition to asking how to lock formulas in Google Sheets, you might also be wondering how to fix a cell in Google Sheets.

If you have absolute and relative references in ranges within your Google Sheets in formula, then using the F4 key is in your best interest. It allows you to toggle between those references quickly and easily.

This is the easiest way when it comes to Google Sheets fix cell in formula to save you time and frustration.

Protecting Your Valuable Information

A lot of people like to use Google Sheets because it helps them organize large quantities of data and then share that information with others. However, after spending a lot of time creating whatever project you’re working on, the last thing you want is for someone to inadvertently change your information.

If you’re wondering how to lock formulas in Google Sheets, here’s what you do: