How to Lock a Table Size in Excel


Excel spreadsheets often require a little tweaking to get the data to display or print correctly on a page. If the data is a tight fit, any little change in size can throw off the entire layout. To prevent these changes, you can lock the worksheet so that other users can't add or delete rows or columns, or make any other changes to the formatting. If you're an advanced user, and your problem stems from Excel automatically adjusting your pivot tables, you can fix the problem by locking the pivot table size.

Entire Worksheet

  • Highlight the area of your spreadsheet containing your data. For example, if you use a spreadsheet to track the quantities of 30 separate items, and you want to prevent other users from adding extra items, select only the data in those 30 rows.

  • Right-click the highlighted area, and then select "Format Cells."

  • Select the "Protection" tab in the Format Cells dialog box, and then clear the check mark next to "Locked." Click "OK" to close the dialog box.

  • Select the "Review" tab on the Excel toolbar, and then click "Protect Sheet" in the Changes group.

  • Enter a password if you want to prevent others from removing the worksheet's protection; otherwise, leave the password field blank.

  • Click "OK" to protect the worksheet. Users are now unable to insert new columns or rows, and they can't alter the widths of the columns and rows. They can, however, edit or insert new data into any of the cells you unlocked in the earlier steps.

Pivot Table

  • Right-click any cell in a pivot table and select "PivotTable Options."

  • Select the "Layout & Format" tab in the dialog box.

  • Clear the check mark next to "Autofit Column Widths on Update."

  • Click "OK" to apply the change. Excel no longer adjusts the column width for this pivot table when the data changes.

Tips & Warnings

  • Information in this article applies to Excel 2013 and Excel 2010. It may vary slightly or significantly with other versions.

Related Searches


Promoted By Zergnet


Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!