docs / articles / Master Excel: Instant Lock Value in Formula with Shortcut

Master Excel: Instant Lock Value in Formula with Shortcut

Sara Jul 09, 2026 2026-07-09 04:40:45

Ever found yourself wishing to lock a value in an Excel formula, preventing it from changing when other cells update? You're not alone. This nifty trick can save you time and ensure consistency in your calculations. Let's delve into how to achieve this with a simple Excel formula shortcut.

4 Ways to Paste Values in Excel
4 Ways to Paste Values in Excel

Before we dive in, let's understand why you might want to lock a value. Suppose you're creating a complex formula that relies on a specific value, like a tax rate or a constant. You want this value to remain unchanged, even as other cells in your spreadsheet update. That's where locking a value comes in handy.

#Excel #Tricks: Remove formula but keep the values or numbers or results
#Excel #Tricks: Remove formula but keep the values or numbers or results

Using the Excel INDIRECT Function

The INDIRECT function in Excel is a powerful tool for this task. It converts a text reference into a true reference. In other words, it treats the text as if it were a cell reference, allowing you to lock a value within a formula.

ms excel formula
ms excel formula

Here's how you can use it. Suppose you want to lock the value 0.08 (representing an 8% tax rate) in your formula. Follow these steps:

Step 1: Define the Value

Excel Keyboard Shortcuts Every Office User Should Know
Excel Keyboard Shortcuts Every Office User Should Know

In a cell, say A1, enter the value you want to lock, such as 0.08.

Now, in another cell, say B1, enter the formula =INDIRECT("A1"). This tells Excel to treat the text "A1" as a cell reference, effectively locking the value in A1.

Step 2: Use the Locked Value in a Formula

How to lock and protect formulas in Excel?
How to lock and protect formulas in Excel?

Now, you can use the value in B1 (which is still 0.08) in your formulas. For example, in cell C1, you might have a formula like =A2*B1, where A2 is a changing value. Even if A2 updates, B1 will remain 0.08, ensuring your formula always uses the locked value.

Here's what your spreadsheet might look like:

BC
10.08
2500
3=B1*C2
How to Copy a Formula Down in Excel Using the Fill Handle
How to Copy a Formula Down in Excel Using the Fill Handle

Using Named Ranges

Another way to lock a value is by using named ranges. Named ranges allow you to give a specific name to a cell or range of cells, making it easier to reference and lock the value.

Microsoft Excel Cheat Sheet | Essential Formulas, Shortcuts & Productivity Tips
Microsoft Excel Cheat Sheet | Essential Formulas, Shortcuts & Productivity Tips
How to VLOOKUP to the Left in Excel
How to VLOOKUP to the Left in Excel
Excel Shortcuts Every Student & Professional Must Know | Boost Productivity Fast
Excel Shortcuts Every Student & Professional Must Know | Boost Productivity Fast
Excel Shortcuts Every Professional Should Know
Excel Shortcuts Every Professional Should Know
Excel Formulas Cheat Sheet: Essential Formulas for Data Analysis | Asim khan posted on the topic | LinkedIn
Excel Formulas Cheat Sheet: Essential Formulas for Data Analysis | Asim khan posted on the topic | LinkedIn
Excel shortcut ...✅
Excel shortcut ...✅
Excel Functions Cheat Sheet | 50+ Essential Formulas Every Beginner Should Know
Excel Functions Cheat Sheet | 50+ Essential Formulas Every Beginner Should Know
SWITCH Formula in Excel
SWITCH Formula in Excel
173K views | Reel by Harkesh Kumar
173K views | Reel by Harkesh Kumar
Excel Shortcut Keys #viral #trick #computer #exceltricks #ExcelTips
Excel Shortcut Keys #viral #trick #computer #exceltricks #ExcelTips
an orange and green banner with the words how to create dynamic excel filter - extra data as you type
an orange and green banner with the words how to create dynamic excel filter - extra data as you type
3 Crazy Microsoft Excel Formulas That Are Extremely Useful
3 Crazy Microsoft Excel Formulas That Are Extremely Useful
the ultimate guide to advanced excel and keywords tips & tricks for beginners
the ultimate guide to advanced excel and keywords tips & tricks for beginners
Master Excel faster with these beginner-friendly shortcuts ⌨️
Master Excel faster with these beginner-friendly shortcuts ⌨️
Excel Formulas Everyone Should Know
Excel Formulas Everyone Should Know
Management training, Soft skills, Power BI, Excel Learning solutions
Management training, Soft skills, Power BI, Excel Learning solutions
a computer screen with the text ctrl + g on it
a computer screen with the text ctrl + g on it
an advertisement for the advanced excel shortcuts program
an advertisement for the advanced excel shortcuts program
Computer tricks😉
Computer tricks😉
an excel shortcuts worksheet with the words, how to use it
an excel shortcuts worksheet with the words, how to use it

Here's how you can do it:

Step 1: Define the Named Range

Right-click on the cell containing the value you want to lock, say A1 with the value 0.08. Select "Define Name" from the context menu. In the "New Name" dialog box, enter a name for your range, like "TaxRate". Click "OK".

Now, you can use this name in your formulas. In cell B1, enter the formula =TaxRate. This will lock the value 0.08 in B1.

Step 2: Use the Locked Value in a Formula

Now, you can use the value in B1 (which is still 0.08) in your formulas. For example, in cell C1, you might have a formula like =A2*TaxRate, where A2 is a changing value. Even if A2 updates, TaxRate will remain 0.08, ensuring your formula always uses the locked value.

And there you have it! Two simple ways to lock a value in an Excel formula. Practice these methods, and you'll soon be a pro at maintaining consistency in your spreadsheets. Happy calculating!