How to Fix Excel Table Not Expanding on a Protected Worksheet
Question details
The user wants to allow an Excel table to automatically expand when adding new data, while keeping the worksheet protected to prevent overwriting of formula cells.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Protecting an Excel worksheet to secure formula cells without losing the auto-expand functionality of Excel tables.
- Observed behavior
- The Excel table expands normally when unprotected, but stops expanding automatically when worksheet protection is enabled.
Before proceeding, ensure you have the password used to protect the worksheet, as you will need to unprotect it temporarily to adjust the cell locking and protection settings.
Adjust Protection Settings to Allow Manual Row Insertion
While automatic table expansion by typing in the next row is disabled on protected sheets by design, you can configure protection settings to allow users to manually insert rows into the table.
Excel restricts automatic table expansion on protected sheets to maintain structural integrity. The most reliable workaround is to adjust your protection settings so users can manually insert rows using right-click, while keeping your formula cells locked.
Navigate to the 'Review' tab on the Excel ribbon and click 'Unprotect Sheet'. Enter your password if prompted.
Highlight the cells or columns in the table where users need to enter data. Right-click the selection, choose 'Format Cells', go to the 'Protection' tab, and uncheck the 'Locked' box. Click 'OK'.
Go back to the 'Review' tab and click 'Protect Sheet'. In the protection dialog box, ensure you check the boxes for 'Insert rows' and 'Select unlocked cells'. Enter a password and click 'OK'.
Users can now right-click a cell in the table and select 'Insert' > 'Table Rows Above' to expand the table, while the formula cells remain securely locked.

Use a VBA Macro to Auto-Expand the Table
Use a VBA macro to automatically unprotect the sheet, add a new table row, and reprotect the sheet when triggered.
Protect Formula Cells and Manage Tables Easily in WPS Office
WPS Spreadsheet offers intuitive worksheet protection features. You can easily lock specific formula cells while leaving the rest of your table unlocked for data entry, ensuring your templates remain both secure and highly functional.
- 1. Select Editable Cells: Open your spreadsheet in WPS Office and highlight the cells you want users to freely edit within the table.
- 2. Unlock the Cells: Right-click the selection, choose 'Format Cells', navigate to the 'Protection' tab, and uncheck the 'Locked' box.
- 3. Protect the Sheet: Go to the 'Review' tab on the top ribbon and click 'Protect Sheet'.
- 4. Set Permissions: Check the permissions for 'Insert rows' and 'Select unlocked cells', set your password, and click 'OK'. Your formulas are now safe while allowing table modification.

Frequently Asked Questions
Why does Excel disable table auto-expansion on protected sheets?
Excel disables automatic table expansion on protected sheets by design to prevent accidental structural changes. This ensures that locked cells or protected areas outside the table are not inadvertently shifted, modified, or broken by automatic row additions.
Can I lock only the formula columns inside an Excel table?
Yes. You can select the entire table, unlock it via Format Cells > Protection, and then select only the formula columns to lock them. After applying 'Protect Sheet', users can edit the unlocked columns, though auto-expansion will still be limited requiring manual row insertion.
Is there a way to allow users to add data without inserting rows manually on a protected sheet?
If worksheet protection is strictly required and manual insertion is too cumbersome, the most seamless workaround is creating a dedicated UserForm via VBA. The form can handle user data entry and append it to the table programmatically while bypassing sheet protection temporarily.




