How to Prevent Users from Changing Column Widths in Excel

When you distribute a carefully formatted Excel template to your team for data entry, the last thing you want is for someone to accidentally drag the column headers and ruin the visual layout. By utilizing Excel’s built-in worksheet protection features, you can allow users to freely type data into the cells while strictly locking the column widths and row heights so the structural layout remains pristine.

How to Lock Column Widths Using Worksheet Protection

Excel allows you to apply a “Protect Sheet” lock that selectively restricts specific actions. We are going to lock the formatting tools while leaving the actual cells unlocked for typing.

1. Open your Excel spreadsheet and ensure your columns are set to the exact width you desire.
2. First, we must ensure the cells themselves remain editable. Click the small triangle in the very top-left corner of the spreadsheet (between the ‘A’ column and ‘1’ row) to highlight the entire sheet.
3. Right-click anywhere on the highlighted area and select Format Cells.
4. Go to the Protection tab in the dialogue box.
5. Uncheck the box labeled Locked and click OK. (This tells Excel that users are allowed to type in these cells).
6. Now, navigate to the Review tab in the main ribbon menu at the top of the screen.
7. Click the Protect Sheet button.
8. A new dialogue box will appear with a long checklist of permissions. Make sure the boxes for Select locked cells and Select unlocked cells are checked.
9. Crucially, scroll down the list and ensure that the box for Format columns is UNCHECKED. (Leaving it unchecked means users are denied permission to format/resize the columns).
10. (Optional) Enter a password at the top of the box to prevent unauthorized users from removing the protection.
11. Click OK.

Testing the Protection

Once you click OK, the worksheet is actively protected. You can still click on any cell and type data normally. However, if you move your mouse cursor up to the column headers (between columns A and B), the double-arrow resizing cursor will no longer appear. The ability to click and drag the column width is completely disabled, and the “Format” options in the Home ribbon will be grayed out.

If you ever need to resize the columns yourself in the future, simply go back to the Review tab, click Unprotect Sheet (entering your password if you set one), and the column formatting controls will instantly unlock.