Permission & Access Issues

Lock Excel Formulas While Allowing Data Entry and Column Deletion

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Protect formulas in an Excel tracker while allowing users to enter data, move records, and delete eligible columns.

Lock Excel Formulas While Allowing Data Entry and Column Deletion
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.
Before you start

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.

Solution 1Recommended

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.

1
Unlock input cells

Select the cells where users will type or paste data. Right-click, choose Format Cells, open the Protection tab, and clear Locked.

2
Keep formulas locked

Select the formula cells, right-click, and choose Format Cells > Protection. Make sure Locked is selected, then click OK.

3
Prepare deletable columns

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.

4
Set sheet permissions

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.

5
Test the workflow

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 the Excel sheet and unlock input cells
Check formula dependencies and record moves: Deleting a column referenced by a formula can change the formula or produce a #REF! error. Moving or pasting entire ranges may also be blocked if they include locked cells; test the exact workflow before relying on it.
Protect formulas in WPS Spreadsheets

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. 1. Open the tracker: Open WPS Spreadsheets and use Open to select the Excel workbook.
  2. 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. 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. 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.
Protect formula cells while leaving input cells editableOpen and save Microsoft Excel XLSX filesWork with tracker data in a familiar spreadsheet interface
QA img-9

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.