How to Protect Only 1 Cell in Excel: 3 Methods

This tutorial on Excel will explain different ways to protect only 1 cell in Excel. You must read the blog until the end to find out the common methods to protect specific Excel cells and decide which would suit your current Excel workbook.

Since Excel is the most widely used data entry software, it must provide all basic features so tasks become effortless on the app. One such essential feature for every data entry software is the ability to lock all the cells, a cell range, or a specific cell in the spreadsheet.

On Excel, there are various ways to achieve the above goal but the most commonly used ones are those involving the Review tab, VBA Editor, or the Office Scripts.

Reasons to Protect Only 1 Cell or a Specific Cell in Excel

1. Protect Data Entry Sheets

You created a data-entry format in Excel involving complex Conditional Formatting rules, Format Cells Number styles, Data Validation rules, formulas, and scripts. You don’t want data-entry operators to modify such content of the Excel spreadsheet. So, you protect those specific cells containing modifications or formulas, leaving the rest editable by anyone.

Also read: How To Make Yes Green And No Red In Excel

2. Ensure Data Integrity

When the integrity of the data in your Excel worksheet is of paramount importance, you must protect all the formula cells. This will ensure the collaborators can’t modify the results of the calculated data. Here, protecting one cell or a specific cell range becomes beneficial for you.

3. Reference Cells

Sometimes, you may have a cell that serves as a reference or input for other formulas or calculations in your worksheet. By protecting that cell, you ensure that users cannot modify the referenced value, which could affect the accuracy of the calculations.

4. Confidential Information

Only 1 cell or a few cells in the Excel spreadsheet contains confidential and critical information that you don’t want others to change. In this scenario, use any of the methods mentioned in this article to protect that cell or cell range.

So, now you know the scope of the Excel skill to protect only 1 or a few specific cells when working with data. Let’s figure out the methods in a step-by-step process:

Protect Only 1 cell In Excel Using the Review Tab

Starting a career in the data analysis profession as a novice Excel user? Don’t know how to lock a specific cell using Excel Protect Sheet command? Then, you must try out these steps and learn the skill effortlessly:

Unprotect All Cells

1. Go to the Excel worksheet where you need to protect only 1 cell.
2. Select any blank cell in the worksheet and hit the Ctrl + A keys together to select the whole worksheet.
3. Now, press Ctrl + 1 to open the Format Cells dialog box and go to the Protection tab.
4. There, uncheck the checkbox for the Locked option.
5. Click OK to close the window.

By default, Excel checkmarks the Locked option for all cells. So, when you activate Protect Sheet command, you can lock all the cells instantly. By reversing the process, meaning, unlocking all cells and protecting only one, you can achieve your objective.

Protect Only One Cell

1. Select the cell you want to lock.
2. Hit Ctrl + 1 to bring up the Format Cells box and navigate to Protection.
3. Checkmark the checkbox for the Locked setting.
4. Click OK to close Format Cells.

Activate Protect Sheet

1. Click the Review tab on the Excel ribbon and go to Protect commands block.
2. There, click the Protect Sheet button and the Protect Sheet dialog box appears.
3. If you want to lock the cell using a password, enter a password you can remember.
4. Hit OK to lock the selected cell.

Now, anyone can edit the whole worksheet except for the cell F3 as shown in the tutorial.

Lock Only 1 Cell in Excel Using VBA Scripts

The above method involves too many clicks. If you want to avoid those extra clicks, you should try automating the process using these VBA scripts.

VBA Script For Any Cell

Suppose, you want to lock any cell or a range of cells in just two clicks. Then, you may want to use the following VBA script:

1. Open the target worksheet and call the Excel VBA Editor by pressing Alt + F11 keys together.
2. Click Insert and choose Module to create a blank one.
3. Now, copy and paste the following VBA script into the blank Module:

Sub ProtectSheetAndLockSpecificCell()

'Declare variables
Dim ws As Worksheet
Dim cell As Range

'Set variables
Set ws = ThisWorkbook.Sheets("Sheet1")

'Loop through all cells in the worksheet
For Each Rng In ws.UsedRange

'Set the Locked property of the cell to False
Rng.Locked = False

Next Rng

'Set variables
Set ws = ThisWorkbook.Sheets("Sheet1")
Set cell = ws.Range("F3")

'Lock the cell
cell.Locked = True

'Protect the sheet
ws.Protect Password:="123"

End Sub

4. In the above script, you may modify the following:
   • ws.Range("F3"): change F3 to another cell address like ("A5") or a cell range like ("A5:A10")
   • Password:="123": change the password to something else you prefer but don’t omit the quotes
5. Click the Save button and close the Excel VBA Editor window.

6. Press Alt + F8 to call the Macro dialog box and select the ProtectSheetAndLockSpecificCell macro.
7. Click the Run button to execute the VBA script.

So, you don’t need to manually unlock all cells of the worksheet and lock the target cell.

Also, there’s no need to go to the Review tab and activate Protect Sheet. The above code will do all these steps for you.

VBA Script For A Cell Containing A Formula

Let’s consider that the specific cell or cell range you want to lock contains a formula. Then, it becomes super easy to lock the same using the quick VBA code:

Sub Protect1Cell()
    Dim Rng As Range
    ActiveSheet.Cells.Locked = False ' Unlocks all cells in the worksheet

    For Each Rng In ActiveSheet.Range("F2:F4")
        If Rng.HasFormula Then
            Rng.Locked = True
        Else
            Rng.Locked = False
        End If
    Next Rng
    
    ActiveSheet.Protect "pass"
End Sub

The above code will unlock all the cells first. Then loop through the defined cell range to detect formulas. If it detects any formula, converts the cell properties to Locked and activates the Protect Sheet command.

Thus, if you try to edit the cell ranges F2:F4 you won’t be able to do so. However, you can edit all other cells on the worksheet. Find below the code elements you need to modify before using this VBA script:

• Range("F2:F4"): change ("F2:F4") to a different cell range according to your worksheet dataset, like ("A2:A4")
• Protect "pass": enter the sheet unlocking password within the quotes, like "1234" instead of "pass"

To execute the VBA code, follow the steps mentioned earlier in the VBA Script For Any Cell section from steps 1 through 7. Just, use the above script when entering a code into the blank Module. Also, on the Macro dialog box, click Protect1Cell to run the code.

📝 Note: Before running the VBA scripts, create a backup of your Excel worksheet. Because you can’t step on your Excel workbook by hitting Ctrl + Z buttons after executing a VBA script. Any changes implemented by the VBA code are permanent.

Protect a Specific Cell in Excel Using Office Script

Are you using the web version of Excel and unable to use the VBA scripts mentioned earlier? No issues since you can automate the task in Excel online using Office Scripts. However, you must possess Microsoft 365 subscription to access this feature. If you see the Automate tab on your Excel web app, then you’re good to go. This feature is also available on Excel 365 desktop app.

Let’s look at the steps you need to follow to use Office Scriots to lock a specific cell in Excel:

1. Navigate to the Automate tab and click New Script.
2. Into the Code Editor, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set protection locked to false for all cells on selectedSheet
	selectedSheet.getRange().getFormat().getProtection().setLocked(false);
	// Set formula hidden to false for all cells on selectedSheet
	selectedSheet.getRange().getFormat().getProtection().setFormulaHidden(false);
	// Set protection locked to true for range F3 on selectedSheet
	selectedSheet.getRange("F3").getFormat().getProtection().setLocked(true);
	// Set formula hidden to false for range F2 on selectedSheet
	selectedSheet.getRange("F2").getFormat().getProtection().setFormulaHidden(false);
	// Protect sheet Sheet1. Passwords are not recorded. Add a password below if needed
	selectedSheet.getProtection().protect({ allowAutoFilter: false, allowDeleteColumns: false, allowDeleteRows: false,
            allowEditObjects: true, allowEditScenarios: true, allowFormatCells: false,
            allowFormatColumns: false, allowFormatRows: false, allowInsertColumns: false,
            allowInsertHyperlinks: false, allowInsertRows: false, allowPivotTables: false,
            allowSort: false, selectionMode: ExcelScript.ProtectionSelectionMode.normal }, 
						"123"
						);
}

3. Change F3 to any other cell address of your choice. You could also input a cell range like "F1:F10".
4. Also, don’t forget to modify the password which is "123" in the current code to something else like "password".
5. Click Save script to save the code and hit Run to execute the code.

That’s it! You should see that the F3 cell is now locked while all other cells are open for edits even when the Protect Sheet command is active.

Conclusions

So far, you learned three different ways to protect only 1 cell in Excel, containing values like a formula or text. The first one, which is using the Format Cells and Review tab is suitable for you if you don’t know how to write codes in Excel.

If you do know about VBA or Office Scripts coding in Excel, choose the VBA scripts or Office Scripts-based methods. While the VBA scripting only works on the Excel desktop app, Office Scripts is versatile and works on both the Excel Web and PC apps.