Permission & Access Issues

How to Fix VLOOKUP Formula Cells Not Locked in Excel

Maira MehtabMaira Mehtab Oct 29, 2026 870 views

Question details

Users are unable to secure their VLOOKUP formulas, as the cells remain editable even after worksheet protection is turned on.

How to Fix VLOOKUP Formula Cells Not Locked in Excel
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 you start

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.

Solution 1Recommended

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.

1
Select the entire worksheet

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.

2
Lock 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.

3
Unlock specific editable cells

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.

4
Enable Worksheet Protection

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.

Reset Cell Formatting and Reapply Worksheet Protection
Protection Dependency: The 'Locked' status in the Format Cells dialog has no effect until you actually enforce it by clicking 'Protect Sheet' in the Review tab.
Secure Your Data with WPS Spreadsheet

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. 1. Select All Cells: Open your file in WPS Spreadsheet and press Ctrl+A to select the entire worksheet.
  2. 2. Format as Locked: Right-click, select 'Format Cells', go to the Protection tab, ensure 'Locked' is checked, and apply.
  3. 3. Unlock Data Entry Fields: Select the specific cells you want others to edit, go to Format Cells > Protection, and uncheck 'Locked'.
  4. 4. Protect the Sheet: Go to the Review tab, click 'Protect Sheet', set your permissions and password, and click OK.
Easily lock and protect VLOOKUP formula cells in a few clicksFully compatible with Microsoft Excel (.xlsx) formats and protection settingsLightweight, fast, and free alternative for complex data processingFamiliar user interface requiring no learning curve
microsoft office alternative - wps office

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.