How to Lock Formatting in Excel & Google Sheets

In this tutorial, you will learn how to lock formatting in Excel and Google Sheets.

 

 

Lock a Range of Cells For Formatting

In Excel, you can lock either the entire worksheet or a range of cells for formatting, while editing is still allowed. Say you have the list of names shown below in Column B.

 

 

Now you want to lock only cells with values (B2:B9) for formatting. However, the user should still be able to edit or delete any cell.

1. (1) Select a range of cells that you want to lock for formatting (B2:B9) and in the Ribbon, (2) go to Home > Font Settings – in the right bottom corner of the Font group (or use the keyboard shortcut CTRL + 1).

 

 

2. In the Format Cells window, (1) go to the Protection tab, (2) uncheck Locked, and (3) click OK.

 

 

3. In the Ribbon, go to Review > Protect Sheet.

 

 

4. In the Protect Sheet window, (1) enter a password (not mandatory), and (2) click OK.

 

 

5. In the pop-up window, (1) re-enter a password to confirm, and (2) click OK.

 

 

As a result, cells B2:B9 are now locked for formatting. If you select any cell in this range and go to the Home tab, you can see that all formatting options are grayed out, which means that you can’t use them.

 

 

On the other hand, if you try to change the content of a cell, it is possible. As you can see in the picture below, the value of cell B2 is changed from Michael to Kevin.

 

 

In this article, you can find out how to unlock the sheet and protected cells.

Lock the Entire Sheet For Formatting

Similar to locking certain cells, you can also lock the entire sheet for formatting in Excel.

1. (1) Select the entire sheet by clicking on the arrow in the left upper corner (or use the keyboard shortcut CTRL + A) and in the Ribbon, (2) go to Home > Font Settings – in the right bottom corner of the Font group (or use the keyboard shortcut CTRL + 1).

 

 

2. In the Format Cells window, (1) go to the Protection tab, (2) uncheck Locked, and (3) click OK.

 

 

3. In the Ribbon, go to Review > Protect Sheet.

 

 

4. In the Protect Sheet window, (1) enter a password (not mandatory), and (2) click OK.

 

 

5. In the pop-up window, (1) re-enter a password to confirm, and (2) click OK.

 

 

As a result, all cells in the sheet are now locked for formatting. If you select any cell in the sheet and go to the Home tab, you can see that all formatting options are grayed out, which means that you can’t use them.

 

 

Again, if you try to edit the content of a cell, it is possible. You successfully entered Michael in cell C2.

 

 

Lock Formatting in Google Sheets

Locking a range or entire sheet in Google Sheets is not possible at the moment.