See all How-To Articles

How to Lock Formatting in Excel & Google Sheets

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

 

lock range of cells 6

 

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.

 

lock formatting initial data

 

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).

 

lock range of cells 1a

 

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

 

lock range of cells 2

 

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

 

lock range of cells 3

 

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

 

lock range of cells 4

 

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

 

lock range of cells 5

 

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.

 

lock range of cells 6

 

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.

 

lock range of cells 7

 

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).

 

lock entire sheet 1

 

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

 

lock range of cells 2

 

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

 

lock range of cells 3

 

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

 

lock range of cells 4

 

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

 

lock range of cells 5

 

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.

 

lock entire sheet 2

 

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

 

lock entire sheet 3

 

Lock Formatting in Google Sheets

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