How To Lock Cells In Microsoft Excel And Google Sheets

Microsoft Excel and Google Sheets are programs that allow users to arrange and make sense of a great deal of data. This information can be ordered into neat, color-coordinated columns and rows (using some convenient shortcuts too) and, crucially, these cells can be adjusted by multiple users who have access to the same spreadsheet.

However, collaborative projects, as helpful as they can be in sharing the workload, also represent certain risks. Accidents happen, after all, but there's a way to ensure that both during a project or once a spreadsheet of data is complete, no further adjustments can be made: locking cells. In some cases, the slightest slip with a setting or mistakenly inputted digit can invalidate the whole project, and it's vital to try and prevent these eventualities wherever possible.

Though full Excel workbooks can be encrypted with a password, the capacity to lock certain cells or elements of a sheet, but not the full piece, allows maximum customization and control of a project. Here's how to do it, in both Microsoft Excel and Google Sheets.

In Microsoft Excel, each cell is locked automatically. In enabling protection of a spreadsheet, it will often be faster to unlock everything and then re-enable the lock on chosen cells. It sounds complicated, but the process is quite simple:

1. Right click the arrow in the very left corner of the spreadsheet, after the first column and first row meet, and select Format Cells.

2. Under the Protection tab, Excel will explain the roundabout way it works with the notice "Locking cells or hiding formulas has no effect until you protect the worksheet." For a blank slate from which to do so, click the Lock button. All cells are now unlocked.

3. Click the specific cell you'd like to lock. If there are multiple, hold the control key and select the next, until they're all highlighted. If there are many, selecting whole columns or rows could be faster.

4. Right click one of those cells, select Format Cells again, and re-toggle the Lock function from the Protection tab.

   Advertisement

5. Open the Review tab, then select Protect Sheet.

6. Input a password to use to remove the protection, if needed.

7. Check or uncheck the functions you wish other users to be able to use/not use (Select Locked Cells and Select Unlocked Cells are enabled as default) and confirm.

Users will now be able to freely edit any unlocked cells, but not select the locked ones. To remove this protection, click Unprotect Sheet from the Review tab and input the password.

Google Sheets users can protect certain cells of a spreadsheet in a very similar way. To do this, here are the steps to follow:

1. Select the cells you want to lock, ensuring each one is highlighted.

2. Open the Data Tab.

   Advertisement

3. The Protect Sheets And Ranges button will create a protected range of cells.

4. In the Enter A Description box, add a note that will remind you why this particular range is selected and why.

5. Selecting Change Permissions will open the Range Editing Permissions menu.

6. The dropdown beneath Restrict Who Can Edit This Range allows you to change the setting from Only You to Custom.

7. If selecting Custom, you can then input the email addresses or names of contacts you'd like to continue to be able to edit these cells.

8. To remove this protected range, use the Delete Range Or Sheet Protection button (the trashcan icon next to the Description you added).

You can repeat this process with different ranges to provide elaborate protection for the spreadsheet, allowing specific users access to certain parts and not others as required. It's also important to note that the original creator of the sheet will also be included in the Only You permission setting, if that isn't you yourself.