Office Settings & Configuration

Fix an Excel 365 Table That Will Not Expand on a Protected Sheet

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Restore the ability to add a new input row to an Excel table after a sort macro protects the worksheet.

Fix an Excel 365 Table That Will Not Expand on a Protected Sheet
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
A sort macro protects a worksheet containing a table that users need to extend with new data.
Observed behavior
The table no longer creates a new input row after the macro protects the worksheet.
Before you start

Confirm that you can unprotect the worksheet and edit the sort macro; you may need the sheet password. Note which cells users should be able to edit and whether the table has a totals row.

Solution 1Recommended

Unprotect the worksheet while the macro sorts or expands the table

Use this approach when the macro protects the sheet before table changes are complete.

Worksheet protection can prevent a table from expanding. Arrange for the sheet to be unprotected while the macro sorts or adds data, then protect it again after those operations finish.

1
Open the macro

In Excel, open Developer > Visual Basic and locate the procedure that sorts the table and protects the worksheet.

2
Adjust the protection sequence

Move or add the worksheet's Unprotect operation before the sort and any table-row additions. Place the Protect operation after those actions, using the existing password and protection settings where applicable.

3
Test the workflow

Run the macro from Developer > Macros, then try the intended data-entry workflow. If users must add rows later, provide a way to unprotect the sheet or a macro that temporarily unprotects it while adding a row.

Unprotect the worksheet while the macro sorts or expands the table
Handle errors and passwords: Make sure the macro restores the intended protection if an operation fails. A password stored in VBA code should not be treated as a secure secret.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Office files. It can help you migrate everyday spreadsheet work, but it does not fix Excel's worksheet-protection behavior; test macros and protected workbooks before relying on them.

Open and edit spreadsheets in a familiar interfaceCompatible with Microsoft Excel formats, including XLSX and XLSWork with table data and worksheet protection settingsUse a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why did my Excel table stop expanding after the sort macro ran?

The macro may have protected the worksheet before users tried to add data. Worksheet protection can block table expansion even when existing input cells are editable.

Will unlocking input cells let users add table rows?

Not necessarily. Unlocking cells and allowing Select unlocked cells lets users access those cells, but table expansion may still require the sheet to be unprotected.

Where is the automatic table expansion setting in Excel?

In Excel for Windows, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type, then enable Include new rows and columns in table.

How can I add a row when the table has a totals row?

Unprotect the sheet if needed, then add the data within the table above the totals row or select a table cell and use Table Design > Resize Table.