How to Fix VLOOKUP Formula Cells Not Locked in Excel
Question details
Users are unable to secure their VLOOKUP formulas, as the cells remain editable even after worksheet protection is turned on.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Securing a worksheet to prevent other users from accidentally modifying, overwriting, or deleting complex VLOOKUP formulas.
- Observed behavior
- Specific cells containing VLOOKUP formulas remain unlocked and accept user input despite the overall worksheet being protected.
Before proceeding, determine exactly which cells in your worksheet need to remain editable for data entry, as the most effective fix requires resetting the lock status for the entire sheet first.
Reset Cell Formatting and Reapply Worksheet Protection
This method ensures all cells are locked by default before you manually unlock the specific data entry cells, guaranteeing your VLOOKUP formulas are protected.
In Excel, cell locking is a two-step process. If cells were previously formatted as unlocked, simply turning on sheet protection will not secure them. You must verify the formatting of the cells themselves.
Click the triangle icon in the top-left corner of your worksheet (where the row and column headers meet) or press Ctrl+A on your keyboard to select all cells.
Right-click anywhere on the selected cells and choose 'Format Cells', or press Ctrl+1. Navigate to the 'Protection' tab, check the 'Locked' box, and click OK.
Highlight only the cells that users are allowed to edit. Open the 'Format Cells' dialog again, go to the 'Protection' tab, uncheck the 'Locked' box, and click OK.
Navigate to the 'Review' tab on the top ribbon and click 'Protect Sheet'. Enter a password if desired, ensure 'Select locked cells' and 'Select unlocked cells' are checked, and click OK.

Lock VLOOKUP Formulas Easily in WPS Office
WPS Spreadsheet offers a highly intuitive interface to lock your specific formula cells and protect your worksheets. It operates just like Excel, ensuring your data remains secure while maintaining full format compatibility.
- 1. Select All Cells: Open your file in WPS Spreadsheet and press Ctrl+A to select the entire worksheet.
- 2. Format as Locked: Right-click, select 'Format Cells', go to the Protection tab, ensure 'Locked' is checked, and apply.
- 3. Unlock Data Entry Fields: Select the specific cells you want others to edit, go to Format Cells > Protection, and uncheck 'Locked'.
- 4. Protect the Sheet: Go to the Review tab, click 'Protect Sheet', set your permissions and password, and click OK.

Frequently Asked Questions
Why does checking the 'Locked' box in Format Cells do nothing?
The 'Locked' option is simply a cell property. It remains inactive and does not restrict any editing until you explicitly turn on the 'Protect Sheet' feature located in the Review tab.
Can I lock only the cells containing formulas without selecting them manually?
Yes. You can automatically select all formula cells by pressing Ctrl+G to open the Go To dialog, clicking 'Special', and selecting 'Formulas'. Once they are highlighted, you can format them as Locked before protecting the sheet.
How do I completely hide my VLOOKUP formulas from being viewed by other users?
In addition to checking the 'Locked' box in the Format Cells Protection tab, you must also check the 'Hidden' box. Once you protect the worksheet, the formula will no longer be visible in the formula bar when the cell is clicked.




