Lock cells in Google Sheet

You’ve spent a lot of time creating your spreadsheet, and now, you want to share it with other employees. You want to make sure that nobody will accidentally change cells that shouldn’t be changed.

Fortunately, Google Sheets offers features that allow you to protect your cells from being modified.

Lock specific cells

In Google Sheets, you can lock selected cells. There are two ways you can do it:

  1. Select cells you want to protect and then right-click and choose Protect range.
  2. Select cells and navigate to Data >> Protected sheets & ranges.

Let’s use the following example:

There is a list of names, split into the first and last names. We want names to stay the same, the only data we allow others to modify is the score because it’s not final.

To start restricting access, select cells from A2 to B6, use right-click, and choose Protect range.

After you do it, a window, on the right side of the worksheet is going to appear.

Click Set permissions.

A new window will appear.

If you want to be the only person who can modify locked values, restrict access to Only you.

Click Done.

Let’s see how other users see the worksheet.

If the user with restricted access tries to modify the first or last name, an error message will appear.

Allowing selected users to modify cells

If you want to give access to these cells to selected users, you can modify protection. Let’s modify the one you’ve already created.

Go to Data >> Protected sheets and ranges and click the only option.

Click the Change permissions button.

Choose custom restriction and check users you want to allow to modify the sheet.

Click Done.

Now, Jared can modify all of the locked cells.

You can have other restrictions for this user. This user has only permission to modify the cells in this very range and other non-restricted cells.

Show warnings, but allow editing

Instead of restricting access for the selected cells, you can warn users (and yourself) not to modify the cells. But if you want, you can always change them.

In Range editing permissions, change select Show a warning when editing this range.

If you double-click or try to modify one of the restricted cells, Google Sheets is going to display this message.

It will be displayed every time you try to modify any cell inside the restricted range. You can disable it for 5 minutes by clicking the checkbox and then OK.

Protect rows and columns

If you want to protect row, right-click the row number and select Protect range. You can also do it for columns by clicking the column name. For columns, you can also click the triangle icon on the right side of the column name.

Inside the range textbox, there is the selected range.

Sheet1!E:E means that permissions will be applied to the entire column.

If you want to protect, for example, the entire 5th row, it will look like this:


Protect the entire sheet

In Google Sheets, there is also a way to protect the entire sheet.

Click Protected sheets and ranges. Inside the window switch from Range to Sheet and select the sheet you want to restrict.

If you want to restrict multiple sheets, you have to repeat the process for each sheet.

For restricted sheets, there is a lock icon on the left side of the sheet tab.

The restricted users can select this sheet and copy its cells, but they can’t do modify the cells or the sheet name.

Add an exception to protected sheet

If you want the whole sheet, except the certain cells to be protected, you can achieve this by checking the Except certain cells option and choosing one or multiple ranges.

In our case, the whole sheet except scores will be protected.

Unlock locked cells

If you can lock cells and sheets, you can also unlock them. Go to the list, and choose one.

Click Delete range or sheet protection.