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.
When building complex Excel sheets, it’s common to spend time adjusting column widths and row heights to achieve clean formatting. But it can be frustrating when widths and heights change accidentally later on.
The solution is to lock the column widths and row heights in Excel to maintain a consistent structure.
In this comprehensive guide, we’ll cover
- What column width and row height controls in Excel do
- How to lock column width and row height to prevent changes
- Additional tips to manage widths and heights
- How to unlock columns and rows when needed
- Alternative solutions for locking formatting
Follow along to master controlling and locking your Excel sheet’s layout permanently or temporarily
What Column Width and Row Height Controls Do
Column width and row height determine the dimensions of cells in Excel Wider columns and taller rows make cells bigger.
The default column width in Excel is 8.43 characters. This often leads to data wrapping unnecessarily over multiple lines. Widening columns makes text fit better.
Row height defaults to 15 points. Increasing row height helps spread out vertically oriented data.
Manually adjusting these values is necessary to optimize the look and readability of your sheet. But it’s also easy to modify them accidentally later on.
How to Lock Column Width to Keep It Constant
Here are the steps to lock column width in Excel:
-
Select the column(s) you want to lock.
-
Right-click and choose Format Cells. Or go to the Home tab and click the Format drop-down.
-
Within the Format Cells dialog, go to the Size tab.
-
Under Column width, enter the desired width value in units of characters.
-
Check the Lock cell box.
-
Click OK to apply.
This will lock the current column width, preventing any resizing.
![Lock column width in Excel][]
You can also automatically optimize column width based on contents with the ribbon button in the Home tab. But this width won’t remain locked.
How to Lock Row Height in Excel Sheets
To lock Excel row height:
-
Select the row(s) you want to lock.
-
Right-click and open the Format Cells menu. Or go to Home > Format.
-
In the Size tab, enter the row height in points under Row height.
-
Check the Lock cell box.
-
Click OK.
This locks the row height so it stays fixed at the defined size.
![Lock row height in Excel][]
For tables, you can check the box to default to row height fitting the contents on insert. But this height won’t stick if you adjust it manually.
Tips for Managing Column Width and Row Height
Here are some additional pointers for working with widths and heights in Excel:
-
Use Ctrl+A to select the entire sheet quickly then adjust column width or row height to apply universally.
-
To resize an entire column or row at once, click the column letter or row number instead of selecting cells.
-
Double-click between column letters or row numbers to automatically fit the data.
-
The ribbon buttons on the Home tab provide quick resizing options but won’t lock.
-
Reset default width and height values under Format > Default Width or Height.
How to Unlock Rows or Columns When Needed
To make locked rows and columns adjustable again:
-
Select the locked cells you want to unlock
-
Open the Format Cells dialog
-
Uncheck the Lock cell box
-
Click OK
This removes the lock allowing widths and heights to be changed again.
Alternative Ways to Lock Formatting in Excel
A few other options for locking parts of an Excel sheet include:
-
Freeze panes – Lock rows and columns in place so they don’t scroll
-
Split panes – Lock a particular column or row at a static horizontal or vertical position
-
Protect sheet – Prevent all changes to cells with password protection
-
Protect ranges – Disable editing for specific cell ranges without a full sheet lock
But these don’t prevent adjusting column widths and row heights like a direct cell lock does.
Limitations of Column Width and Row Height Locks
Locking cells has some limitations to be aware of:
-
Other users of a shared workbook may still unlock and modify widths and heights unless the sheet is fully protected.
-
Rows and columns may resize when opening files across different versions of Excel or operating systems.
-
Font size, style changes, text additions, and other edits can cause locked columns to resize wider to fit contents.
-
Locking doesn’t prevent inserting or deleting columns and rows.
Despite these restrictions, cell locking remains the most reliable way to fix column and row dimensions in Excel permanently.
Now you’re equipped with a solid understanding of how to lock column width and row height in Excel workbooks.
Sticking to a consistent worksheet structure ensures your data remains easy to analyze after formatting meticulously. By preventing accidental tweaks, locking columns and rows keeps your sheet looking sharp.
Just remember to document the purpose behind your locked spreadsheet columns and rows for other users. This avoids frustrating attempts to modify the seemingly fixed layout.
With these skills, you can take your Excel formatting finesse to the next level and craft professional, polished spreadsheets.
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:
- Select all the cells in the worksheet by clicking on the gray triangle at the top-left part of the worksheet.
- Click the ‘Home’ tab
- In the Number group, click on the dialog box launcher icon (the small tilted arrow icon at the bottom right of the group)
- In the Format cells dialog box that opens up, click on the ‘Protection’ tab
- Uncheck the Locked option
- 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.
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:
- Disable the ‘Locked’ property for all the cells
- Protect the worksheet so that row height and column width for each cell is locked
How to lock column width in Excel
How to lock column width & height in Excel?
The following are steps to lock width and height in excel: To set a column width, follow these steps: 1. Click on one or more columns 2. Go to the cells section of the Home tab 3. Select the format button 4. Click on ‘Cell size.’ 5. Click on ‘column width.’ 6. Then type how wide you want the column to be, then click ok To set row height: 1.
How do I lock columns and rows in Excel?
In the Ribbon, go to Review > Protect Sheet. Type in a password, and then check all options except Format columns and Format rows. Click OK. Note: If you want to lock column width only, leave only Format columns unchecked. On the other hand, if you want to lock row height only, only uncheck Format rows.
How do I set a column width in Excel?
To set a column width, follow these steps: 1. Click on one or more columns 2. Go to the cells section of the Home tab 3. Select the format button 4. Click on ‘Cell size.’ 5. Click on ‘column width.’ 6. Then type how wide you want the column to be, then click ok To set row height: 1. Click on the rows you want to adjust 2.
Why do I need to lock the width of a column?
The purpose of locking the width of a column or height of a row in a spreadsheet is to limit changes to the format. If you set up a spreadsheet template and want each section to be the same, locking the width and height can help you achieve this goal.
