How to Lock Cell Value Once Calculated in Excel (3 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, users execute sensitive calculations which need to be unaltered once calculated. As a result, the Excel lock cell value once calculated is a handy option to maintain the authenticity of a document. Protect Sheet and VBA Macro code can lock the cell values once calculated.

Dataset-Excel Lock Cell Value Once Calculated

Let’s say we have Employee Working Time data from which we want to calculate the Working Hours. Also, we want no further alteration of these calculated values once they are calculated.

How to Lock Cell Value Once Calculated in Excel: 3 Easy Ways

There is a different outcome between lock cells and protect sheet executions. Lock Cells; Font Setting > Format Cells > Protection > Locked, lock selected cells. However, users are still able to modify or change the calculated values within the selection. To ensure cell locking once the value is calculated, users have to execute Protect Sheet feature after enabling the Cell Protection.

Method 1: Lock Value Cells Using Format Cells Feature in Excel

Though locking cells don’t really save cells from getting altered, we have to enable it before applying the Protect Sheet feature.

Step 1: Select desired cells within a Worksheet after the calculation. Then click on the Font Dialog Box Icon to bring out the Font Setting dialog box. Alternatively, you can use CTRL+1 to do so.

Format Cells-Excel Lock Cell Value Once Calculated

Step 2: The Format Cells window appears. In the window, Choose Protection > Tick the Locked option. Click OK.

Format Cells

What is done here is that Excel is asked to lock those cells but it can’t do it without the Sheet Protection ON. So, you have to protect the sheet to ensure no further alteration of value.

Step 3: Now, go to the Review tab > Click on Protect Sheet (from Protect section).

Protect Sheet

Step 4: Excel fetches the Protect Sheet dialog box. Provide a suitable password (i.e., 1234) then click OK.

Select Options

You can choose from multiple options what you want to allow other users to do with your worksheet without a password.

Step 5: A confirmation window pops up. Enter the same password to proceed.

Password Confirmation

🔼 Afterward, in the Worksheet try to edit or alter calculated values. You see Excel displays a warning saying the Sheet is a Protected Sheet and you may need the password to unprotect it as depicted in the following image.

Warning: Protected Sheet-Excel Lock Cell Value Once Calculated

So, none can alter any calculated values without a password after you execute the sheet protection.

Read More: How to Protect Excel Cells with Formulas

Method 2: Using VBA Macro to Enable Protection Option

Alternative to the Method 1, the cell Protection option (i.e., Locked) can be enabled by a Macro code execution. Running this macro will lock the cells provided in the Macro.

Step 1: Use ALT+F11 or go to the Developer tab > Visual Basic (in the Code section) to display the Microsoft Visual Basic window. Double Click on any Sheet (i.e., Sheet2). It displays a Sheet’s Code window to insert macro codes.

Enabling Protection option-Excel Lock Cell Value Once Calculated

Step 2: Paste the below Macro code in the Sheet’s Code window.

Private Sub Worksheet_Change(ByVal Rng As Range)
If Range("C4") = "Undone" Then
Range("E6:E16").Locked = False
ElseIf Range("C4") = "Done" Then
Range("E6:E16").Locked = True
End If
End Sub


The macro runs a VBA IF formula to execute a statement that holds cells (i.e., E6:E16) to enable the Protection Lock.

Step 3: After the Macro insertion, back to the worksheet and find whether the macro locks the cell or not. Go to Font Setting Icon > Format Cells > Protection or CTRL+1 > Protection. You see the macro enables the Locked option in the Protection section.

Format Cells

🔼 Repeat Steps 3 to 5 of Method 1 to ensure locking of the cell values after calculation.

Warning: Protected Sheet-Excel Lock Cell Value Once Calculated

Read More: How to Protect Excel Cells from Deletion

Method 3: VBA Macro to Lock Cell Value Once Calculated in Excel

We can also write a couple of line macro codes to lock cell values once calculated. This requires entering a password every time users want to perform any change or modification to the sheet.

Step 1: Execute Steps 1 and 2 of Method 2 then Paste the below macro in the Sheet’s Code window.

Option Explicit
Sub Worksheet_Change(ByVal Rng As Range)
ActiveSheet.Unprotect Password:="1234"
Rng.Locked = True
ActiveSheet.Protect Password:="1234"
End Sub


The macro performs ActiveSheet.Protect/Unprotect statement using the password (i.e., 1234) every time you perform a calculation or edit.

Step 2: Now, get back to the worksheet and try to calculate the Time Worked in range E8:E17. Excel displays a warning that the sheet is a protected sheet, you need a password to be able to edit.


Step 3: Go to Review > Unprotect Sheet (in the Protect option).


Step 4: The Unprotect Sheet command box appears. Provide the Password (i.e., 1234) to unlock the sheet for a single execution of any command. Afterward, Excel locks the entire sheet again.

Password Insertion

Step 5: Execute only one command (i.e., Subtraction). Then Hit ENTER.

Formula Insertion

Step 6: As you know from the previous arguments that the Sheet gets locked after each execution of the command. You need to Unprotect the Sheet using Steps 3 and 4 to be able to perform further operations each time. The final outcome will look similar to the picture shown below.

Final outcome

Read More: How to Lock Certain Cells in Excel

Download Excel Workbook


Protect Sheet and VBA Macro Code lock cell value once calculated in Excel. Hope these described methods fulfill your requirements. Comment if you have further inquiries or have anything to add.

Related Articles

<< Go Back to Protect Excel Cells | Excel Protect | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

  1. Maruf, These are very nice solutions when one user needs to control access. I need to have a calculated value remain static while allowing full access to the rest of the worksheet. Specifically, on a sales order form with a formula to create a form number based on the date or a random number generator. Once this value is calculated in the cell, it needs to remain unchanged no matter how many times the worksheet is opened or recalculated. Any ideas?

    • Greetings Jack Kuehne,

      Thank you for your appreciation.
      To make static only one (01) calculated cell value or range values, follow the below steps:

      1. Unlock all cells within the worksheet using the Format Cells window (CTRL+1 or Home > Click Font Setting Icon to open Format Cells window > Protection > Untick Locked) [Shown in the latter image].

      2. Highlight the desired cell or range (you want to keep static no matter what) then Lock them using Step 1 (Tick Locked in the Protection section of Format Cells window).

      3. Go to the Preview tab > Tick Select Locked Cells and Select Unlocked Cells > Enter Password > OK > Re Enter Password.

      Step 2 and 3 represent Method 1 of this article.

      Untick Locked

      Maruf Islam (Exceldemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF