How to Lock Row Height & Column Width in Excel (Easy Trick)

It’s quite easy to change a cell’s height and width in Excel.

The more you work with Excel, the more you would find yourself adjusting the row height and column width.

But in some cases, you may want to lock the cell height and width so that a user cannot make any changes to it.

One scenario where this may be needed could be when you have a fixed template that you share with other people and you don’t want them to mess up the formatting by changing the cell height/width.

In this tutorial, I will show you how to lock the row height and column width in Excel by making a simple change.

How to Lock Row Height and Column Width in Excel (Easy Trick)

When you protect a worksheet in Excel, also locks the row height and column width so that no one can change it.

But with sheet protection, all the cells are also locked and you cannot make any changes to it (i.e, you can not get into the edit mode or enter formulas).

While we want to lock the column width and the row height, we don’t want to lock the cells completely.

And thankfully, you can do that in Excel.

For this to work, you need to do two things:

  1. Disable the ‘Locked’ property for all the cells
  2. Protect the worksheet so that row height and column width for each cell is locked

Disabling the Lock Property for all Cells

Below are the steps to disable the lock property for all the cells, after which we can lock the row height and the column width:

  1. Select all the cells in the worksheet by clicking on the gray triangle at the top-left part of the worksheet.Click on the gray triangle to select all cells
  2. Click the ‘Home’ tabClick Home tab in the ribbon
  3. In the Number group, click on the dialog box launcher icon (the small tilted arrow icon at the bottom right of the group)Click the dialog box launcher in the Number group
  4. In the Format cells dialog box that opens up, click on the ‘Protection’ tabClick the protection tab
  5. Uncheck the Locked optionDisable the locked property
  6. Click OK

By doing this we have ensured that even if we protect the entire worksheet the cells would still remain editable.

When you protect a worksheet in Excel, the cells would be completely locked (i.e., the user won’t be able to edit the cells and enter anything into it). But this happens only when the Locked property is enabled. If you disable the Locked property, the cells would remain editable even after protecting the worksheet.

Now, let’s move to the second step, which is to protect the entire sheet.

Protecting the Entire Worksheet

Now let’s see how to quickly protect the entire worksheet.

  1. Click the ‘Review’ tabClick the Review tab in the ribbon
  2. In the Protect group, click on the ‘Protect Sheet’ optionClick on Protect Sheet icon
  3. In the Protect Sheet dialog box that opens up, enter the password (optional)Enter the password in Protect Sheet dialog box
  4. Check all the options in the Protect Sheet dialog box (except the ‘Format Columns’ and ‘Format Rows’ options).Row height and column options grayed out
  5. Click OK

The above steps would protect the worksheet, but at the same time allow the user to do all the regular stuff such as formatting the cells or deleting/adding rows and columns.

Since we have disabled the format columns and format rows option (in the Protect Sheet dialog box), these will still remain locked for the user.

This means that the user won’t be able to change the row height or column width in this worksheet.

If you go to any a row header or column header and put your cursor at the edge of the header, you would see that your icon would not change (which earlier used to change into a double-pointed arrow and allow you to change the row height or column it by clicking and dragging the cursor)

Similarly, if you select any cell in the worksheet, click the Home tab, and then click on the Format option, you would see that the Row Height and Column Width options are now grayed out.

Row height and column options grayed out

So, this is how you can lock the row height and column width in Excel.

Note that since we have protected the worksheet and only enabled certain options, there are some things that you won’t be able to do when you lock the row height and the column width using the above method. For example, if you want to insert new cells in a column/row or hide the rows and columns, you won’t be able to do that (as we have completely disabled the option to format rows and options in any manner)

I hope you found this tutorial useful.

You may also like the following Excel tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment