Office Settings & Configuration

How to Fix Excel Table Not Expanding on a Protected Worksheet

Adam DavisAdam Davis Oct 22, 2026 875 views

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.

How to Fix Excel Table Not Expanding on a Protected Worksheet
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 you start

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.

Solution 1Recommended

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.

1
Unprotect the Worksheet

Navigate to the 'Review' tab on the Excel ribbon and click 'Unprotect Sheet'. Enter your password if prompted.

2
Unlock Data Entry Cells

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'.

3
Reprotect with Insert Row Permissions

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'.

4
Insert Rows Manually

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.

Adjust Protection Settings to Allow Manual Row Insertion
Limitation: This method requires users to manually insert a row before typing, as hitting 'Tab' or typing immediately below the table will still not auto-expand it.
Manage Data Securely with WPS

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. 1. Select Editable Cells: Open your spreadsheet in WPS Office and highlight the cells you want users to freely edit within the table.
  2. 2. Unlock the Cells: Right-click the selection, choose 'Format Cells', navigate to the 'Protection' tab, and uncheck the 'Locked' box.
  3. 3. Protect the Sheet: Go to the 'Review' tab on the top ribbon and click 'Protect Sheet'.
  4. 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.
Highly compatible with Microsoft Excel (.xlsx) file formats and table structures.Granular worksheet protection to lock formulas while allowing data entry.Lightweight software with a user-friendly, tabbed interface.Free to download and use for your daily spreadsheet tasks.
microsoft office alternative - wps office

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.