Lock Excel Formulas While Allowing Data Entry and Column Deletion
Question details
Protect formulas in an Excel tracker while allowing users to enter data, move records, and delete eligible columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A tracker contains formulas, pasted data, and manually entered records that users need to maintain.
- Observed behavior
- Formula cells need protection without blocking necessary edits to input cells or deletion of permitted columns.
Identify the formula cells, input ranges, and columns users may need to delete. Check whether formulas refer to those columns, because deleting a referenced column can affect the formulas.
Protect the Excel sheet and unlock input cells
Use cell locking and sheet protection to prevent formula edits while permitting data entry and eligible column deletion.
The Locked setting takes effect only after the sheet is protected. Excel generally allows a column to be deleted on a protected sheet only if every cell in that column is unlocked and Delete columns is permitted.
Select the cells where users will type or paste data. Right-click, choose Format Cells, open the Protection tab, and clear Locked.
Select the formula cells, right-click, and choose Format Cells > Protection. Make sure Locked is selected, then click OK.
Select each column users may delete, open Format Cells > Protection, and clear Locked for the entire column. Do not unlock columns containing formulas that must remain protected.
Go to Review > Protect Sheet. Choose a password if appropriate, select Select unlocked cells and Delete columns, and enable other needed actions, such as Use AutoFilter. Click OK and confirm the password if prompted.
On the protected sheet, try entering and pasting data into unlocked cells, copying records to their destination, and deleting an eligible column. Adjust the unlocked ranges or protection permissions if a required action is blocked.

Protect tracker formulas in WPS Spreadsheets
Open the tracker in WPS Spreadsheets, unlock the cells intended for input, and protect the sheet to prevent changes to locked formulas. Check the available protection permissions and test column deletion in your version.
- 1. Open the tracker: Open WPS Spreadsheets and use Open to select the Excel workbook.
- 2. Unlock editable cells: Select the input cells, right-click, and choose Format Cells > Protection. Clear Locked and confirm. If users need to delete a column, unlock the entire column only if it contains no formulas that need protection.
- 3. Protect the sheet: Select the formula cells and confirm that Locked is enabled under Format Cells > Protection. Then choose Review > Protect Sheet, set a password if appropriate, and enable the required actions, including column deletion if that option is available.
- 4. Test and save: Try entering data and deleting an eligible column on the protected sheet. Use File > Save As to save the workbook in XLSX format, then check that the protection and formulas behave as expected.

Frequently Asked Questions
Why can users still edit cells marked Locked?
The Locked setting does not restrict editing until you turn on sheet protection. In Excel, go to Review > Protect Sheet and choose the permissions users need.
Why can't I delete a column on a protected Excel sheet?
Check that Delete columns is selected under Review > Protect Sheet and that every cell in the column is unlocked. To change cell locking, first unprotect the sheet through Review > Unprotect Sheet, then use Format Cells > Protection.
Can deleting an unlocked column affect protected formulas?
Yes. If a formula refers to the deleted column, Excel may change the reference or show a #REF! error. Check formula dependencies and test the deletion before allowing it.
Can users paste or move records on a protected sheet?
Users can generally paste into unlocked cells, but an operation may be blocked if the source or destination includes locked cells. Test the exact copy, paste, or move action on the protected sheet.




