Edit an Excel Table on a Protected Worksheet While Locking Formulas
Question details
Allow users to add rows and columns to an Excel table while preventing changes to formula cells.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Users need to expand a table on a protected worksheet and continue entering data without overwriting calculated columns.
- Observed behavior
- The desired configuration keeps formula cells locked while preserving a way to add table rows and columns.
Confirm that the data is an Excel table and identify its input and formula columns. For the VBA approach, use desktop Excel, save the workbook as an .xlsm file, and allow macros only if you trust the workbook.
Use VBA to add table rows and columns and restore protection
Use macros when users need buttons or commands that expand the table without manually unprotecting the worksheet.
Worksheet protection options for inserting rows and columns do not guarantee that users can expand an Excel table directly. These macros temporarily unprotect the sheet, change the table, and protect the sheet again.
The row macro uses the first existing data row as a template for cell locking and formulas. It requires at least one data row and assumes formulas are consistent within each calculated column.
Select the table's input cells, press Ctrl+1, and clear Locked on the Protection tab. Leave formula cells locked, then use Review > Protect Sheet to protect the worksheet.
Open the VBA editor with Alt+F11, choose Insert > Module, and paste the code below. Replace Sheet1, Table1, and YourPassword with your worksheet name, table name, and password. Find the table name under Table Design in Excel. Sub AddTableRow() ChangeTable True End Sub Sub AddTableColumn() ChangeTable False End Sub Private Sub ChangeTable(ByVal addRow As Boolean) Const sheetName As String = "Sheet1" Const tableName As String = "Table1" Const sheetPassword As String = "YourPassword" Dim ws As Worksheet, tbl As ListObject Dim newRow As ListRow, newCol As ListColumn Dim i As Long, errorMessage As String On Error GoTo Failed Set ws = ThisWorkbook.Worksheets(sheetName) Set tbl = ws.ListObjects(tableName) If addRow And tbl.DataBodyRange Is Nothing Then Err.Raise vbObjectError + 1, , "Add a template data row first." End If ws.Unprotect Password:=sheetPassword If addRow Then Set newRow = tbl.ListRows.Add For i = 1 To tbl.ListColumns.Count newRow.Range.Cells(1, i).Locked = tbl.DataBodyRange.Cells(1, i).Locked If tbl.DataBodyRange.Cells(1, i).HasFormula Then newRow.Range.Cells(1, i).FormulaR1C1 = tbl.DataBodyRange.Cells(1, i).FormulaR1C1 End If Next i Else Set newCol = tbl.ListColumns.Add If Not newCol.DataBodyRange Is Nothing Then newCol.DataBodyRange.Locked = False End If End If GoTo ProtectSheet Failed: errorMessage = Err.Description ProtectSheet: On Error GoTo ProtectionFailed If Not ws Is Nothing Then ws.Protect Password:=sheetPassword, AllowFiltering:=True, AllowInsertingRows:=True, AllowInsertingColumns:=True End If If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation Exit Sub ProtectionFailed: MsgBox "Could not restore worksheet protection: " & Err.Description, vbCritical End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Choose Developer > Macros and run AddTableRow or AddTableColumn. Check that new input cells are editable, formulas are present and locked, and Review shows the sheet is protected.

Temporarily unprotect the worksheet to expand the table
Use this method when an authorized person can make occasional structural changes without macros.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday spreadsheet work. Test protected tables and VBA-dependent workflows before migrating, as behavior and macro support can differ from Excel.

Frequently Asked Questions
Why can't I expand my table after allowing row insertion?
The protected-sheet permission to insert worksheet rows does not necessarily permit changes to an Excel table's structure. Unprotect the sheet before adding table rows or columns, or use a macro that performs the change and restores protection.
Does UserInterfaceOnly let users edit locked formula cells?
No. UserInterfaceOnly:=True allows VBA to modify a protected worksheet while keeping interface restrictions for users. It does not guarantee that users can expand a table directly, and the setting must be reapplied when the workbook opens.
How do I make input cells editable on a protected sheet?
Before protecting the sheet, select the input cells, press Ctrl+1, open the Protection tab, and clear Locked. Then choose Review > Protect Sheet. Leave formula cells locked.
Will formulas be protected in a newly added table row?
Check the new row after adding it. The VBA example copies formulas and locking from the first existing data row; it assumes that row represents the intended formula and input pattern.
Can I run these macros in Excel for the web?
Excel for the web cannot run VBA macros. Open the macro-enabled workbook in desktop Excel to run them, and enable macros only for a workbook you trust.




