Technology

How to Lock Column Width and Row Height in Microsoft Excel

Do you have an Excel sheet where you want the columns and rows at fixed sizes? You can lock the column width and row height for your entire spreadsheet in a few steps to prevent accidental resizing.

Maybe you have data that you want to display in a specific way. For instance, you might expand a cell to accommodate a large font or an image. Because it’s so easy to resize columns and rows by dragging them, you or a collaborator might do it unintentionally. Rather than spending time constantly getting those columns or rows back to the sizes you want, just lock them.

Unlock the Cells in the Sheet

Whether you realize it or not, all cells in your Excel sheet are locked by default if you’ve never protected the sheet. This doesn’t prevent changes or edits until you use the sheet protection feature which we’ll describe below.

So, the first step is to unlock all cells in your sheet to allow you to enter data and make changes outside of the locked column and row sizes.

RELATED: How to Lock Cells in Microsoft Excel to Prevent Editing

Select the sheet by clicking the Select All button (triangle) on the top left corner of the sheet.

Select All button for an Excel sheet

Open the Format Cells box by right-clicking and choosing “Format Cells” or by going to the Home tab and clicking the Format Cells launcher (tiny arrow) on the bottom right of the Number section of the ribbon.

Format Cells launcher in the Number section of the ribbon

Go to the Protection tab and uncheck the box for Locked. Then, click “OK” to apply the change.

Locked unchecked on the Protection tab

Protect the Sheet

Now that all cells in your sheet are unlocked, you’ll protect the sheet but only for the columns and row sizes (formatting.)

RELATED: How to Protect Workbooks, Worksheets, and Cells From Editing in Microsoft Excel

Head to the Review tab and click “Protect Sheet” in the Protect section of the ribbon.

Protect Sheet on the Review tab

Enter a password to secure the protection and check the box at the top for Protect Worksheet and Contents of Locked Cells. Then, check every box in the list beneath except for Format Columns and Format Rows depending on which you want to protect, or both.

Protect Sheet settings

This allows you to continue working with your sheet and editing anything you like, except for the column and/or row formatting. Those are the only items locked. Select “OK.”

Note: You can also uncheck the boxes for inserting or deleting columns and rows if you want to protect those as well.

Confirm the password and select “OK.”

Confirm Password box

Now, you can give your change a test. You’ll notice that you no longer see the double-sided arrow between columns or rows.

No double-sided arrow between columns

Additionally, the resizing options in the shortcut menu are grayed out and unavailable.

Column Width grayed out in the menu

Allow Column and Row Resizing Again

To remove the protection from the sheet and allow resizing again, go to the Review tab and pick “Unprotect Sheet.”

Unprotect Sheet on the Review tab

Enter the password and click “OK.”

Enter Password box

You’ll then see the double-sided arrow between columns and rows.

Double-sided arrow between columns

You also have the option to resize them in the shortcut menu.

Column Width in the shortcut menu

Locking the sizes for your columns and rows in Excel takes only a few steps that are worth your time to prevent unwanted changes.

For more, look at how to freeze and unfreeze columns and rows.



File source

Tags
Show More

Related Articles

Back to top button
Close