Fix an Excel 365 Table That Will Not Expand on a Protected Sheet
Question details
Restore the ability to add a new input row to an Excel table after a sort macro protects the worksheet.

- 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.
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.
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.
In Excel, open Developer > Visual Basic and locate the procedure that sorts the table and protects the worksheet.
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.
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.

Check unlocked cells and worksheet protection settings
Use this check when users cannot select or edit the intended input cells after protection is applied.
Check the table range, totals row, and AutoCorrect option
Use these checks if the table still does not expand when the worksheet is unprotected.
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.

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.




