Lock formula $ excel dollar sign shortcut

Posted by Andrew Gould on 02 December 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

In Excel, you put dollar signs in a cell reference to keep it fixed when you copy a formula around. It's laborious having to type dollar signs into a cell reference, but fortunately there's a keyboard shortcut you can use instead!

Start creating your formula up to the point where you've entered the cell reference you want to add dollar signs to.

Here we want to put dollar signs in the H3 cell reference, so we have selected it.

Make sure the text cursor is in contact with the cell reference, and then press the F4 key on the keyboard.

Two dollar signs appear in the selected cell reference.

You can continue pressing F4 to cycle through the various combinations of dollar signs - for the example above these would be H3, $H$3, H$3 and $H3.

You can see this technique in action in this short video tutorial, or see a full range of blogs, videos and exercises covering training in Microsoft Excel.

This blog has 0 threads Add post

Normally, when selecting a formula cell and dragging it down to fill the cells below, the cell references in the formula will be changed automatically. If you want to make the formula constant without changing with the cell, you need to convert the cell references to absolute. This article can help you.

Drag or copy formula and lock the cell value with the F4 key
Drag or copy formula and lock the cell value with Kutools for Excel

Drag or copy formula and lock the cell value with the F4 key

For locking the cell reference of a single formula cell, the F4 key can help you easily.

Select the formula cell, click on one of the cell reference in the Formula Bar, and press the F4 key. Then the selected cell reference is locked.

Go ahead to lock the other cell references of current formula with the same step above.

Drag or copy formula and lock the cell value with Kutools for Excel

The Convert Refers utility of Kutools for Excel can not only convert all cell references of a single cell to absolute at once, but also convert all cell references of a range of formula cells to absolute at once. Please do as follows.

Before applying Kutools for Excel, please download and install it firstly.

1. Select the formula cell (or a range of formula cells) which you need to lock the value, then click Kutools > More > Convert Refers. See screenshot:

2. In the Convert Formula References dialog box, select the To absolute option and then click the OK button.

Then values of selected formula cells are locked, and they won’t be changed with dragging or copying to another cells.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Drag or copy formula and lock the cell value with Kutools for Excel

No ratings yet. Be the first to rate!

If you work with formulas in Excel, you know that it's important to lock cell references. Otherwise, you run the risk of your formulas returning errors. But what's the best way to lock cell references?

There are a few different ways to lock cell references in Excel. You can use the F4 key, you can use the $ sign, or you can use a combination of both. Each method has its own advantages and disadvantages. Let's take a look at each one in turn.

1. Use the F4 key

The F4 key is the easiest way to lock cell references. Simply select the cell or range of cells that you want to lock, then press the F4 key. Excel will automatically add the $ sign to the appropriate places in the cell reference.

One advantage of using the F4 key is that it's quick and easy. You don't have to remember any special syntax, and you can lock cell references with just a few keystrokes. However, there are a few disadvantages to using the F4 key as well.

First, the F4 key only works if you're using the absolute cell reference style. That means that if you're using the relative cell reference style, you won't be able to use the F4 key to lock cell references. Second, the F4 key can be a little finicky. If you accidentally press the wrong key, you might end up changing your cell reference instead of locking it.

2. Use the $ sign

Another way to lock cell references is to use the $ sign. The $ sign is used to indicate an absolute cell reference, which means that the reference will not change when you copy or move the formula. For example, if you have a formula in cell A1 that refers to cell B1, you can use the $ sign to make it an absolute reference:

=B$1

If you copy this formula to cell C1, the cell reference will change to C1. But if you use the $ sign, the cell reference will stay the same:

=$B$1

You can use the $ sign to lock either the row reference or the column reference, or both. To lock the row reference, put the $ sign before the row number. To lock the column reference, put the $ sign before the column letter. To lock both the row and column reference, put the $ sign before both the row number and column letter.

One advantage of using the $ sign is that it's very flexible. You can lock just the row reference, just the column reference, or both. You can also use the $ sign in conjunction with the F4 key. For example, if you want to lock the row reference but not the column reference, you can use the following syntax:

=$B1

Another advantage of using the $ sign is that it's easy to remember. Once you get used to using it, you'll be able to lock cell references without even thinking about it.

However, there are a few disadvantages to using the $ sign as well. First, it can be easy to forget to use the $ sign, which can lead to errors in your formulas. Second, if you're not careful, you can accidentally lock the wrong cell reference. For example, if you want to lock the column reference but not the row reference, you might accidentally lock the row reference as well:

=B$1

This can be a frustrating mistake to make, and it can be hard to spot if you're not paying close attention.

3. Use a combination of the F4 key and the $ sign

Another way to lock cell references is to use a combination of the F4 key and the $ sign. This method is similar to the previous one, but it's a little more foolproof. Simply select the cell or range of cells that you want to lock, then press the F4 key. Excel will automatically add the $ sign to the appropriate places in the cell reference.

One advantage of using this method is that it's very difficult to make a mistake. If you accidentally lock the wrong cell reference, you can simply press the F4 key again to unlock it. Another advantage is that you can use this method with the relative cell reference style. That means that you can lock cell references without having to switch to the absolute cell reference style.

However, there are a few disadvantages to using this method as well. First, it's a little more time-consuming than the other methods. Second, it's not as flexible as the other methods. For example, you can't use this method to lock just the row reference or just the column reference. You have to lock both the row and column reference at the same time.

4. Use a named range

Another way to lock cell references is to use a named range. A named range is a range of cells that has a name. For example, you could create a named range for a range of cells that contains customer data. To create a named range, select the cells that you want to include in the range, then click the "Name" box on the formula bar. Type a name for the range, then press Enter.

Once you've created a named range, you can use the name in formulas instead of the cell references. For example, if you have a named range called "Customers", you can use the following formula:

=SUM(Customers)

This formula will sum all the values in the named range. You can also use the named range in other formulas, such as the VLOOKUP function:

=VLOOKUP(A1,Customers,2,FALSE)

This formula will look up the value in cell A1 in the named range "Customers" and return the value in the second column. You can also use the INDIRECT function to reference a named range:

=INDIRECT(A1)

This formula will return the value of the cell that is referenced in cell A1. For example, if cell A1 contains the text "Customers", the formula will return the value of the cell in the named range "Customers".

One advantage of using named ranges is that they're very flexible. You can use them in any type of formula, and you can reference them by name instead of by cell reference. This can make your formulas easier to read and understand. Another advantage is that you can use named ranges with the relative cell reference style. That means that you can lock cell references without having to switch to the absolute cell reference style.

However, there are a few disadvantages to using named ranges. First, they can be a little bit time-consuming to set up. Second, you have to remember the name of the range when you use it in a formula. This can be a challenge if you have a lot of named ranges, or if you're not used to using them. Finally, named ranges can't be used in array formulas.

5. Use the INDIRECT function

Another way to lock cell references is to use the INDIRECT function. The INDIRECT function is a built-in function in Excel that allows you to reference cells indirectly. In other words, you can use the INDIRECT function to reference cells by their address instead of by their cell reference. For example, if you have a cell that contains the text "A1", you can use the INDIRECT function to reference the cell that is at the address "A1":

=INDIRECT(A1)

This formula will return the value of the cell that is at the address "A1". You can also use the INDIRECT function to reference a named range:

=INDIRECT(A1)

This formula will return the value of the cell in the named range "Customers".

One advantage of using the INDIRECT function is that it's very flexible. You can use it to reference cells by their address, by their cell reference, or by their named range. This can make your formulas easier to read and understand. Another advantage is that you can use the INDIRECT function with the relative cell reference style. That means that you can lock cell references without having to switch to the absolute cell reference style.

However, there are a few disadvantages to using the INDIRECT function. First, it can be a little bit time-consuming to set up. Second, you have to remember the address or name of the cell when you use it in a formula. This can be a challenge if you have a lot of cells, or if you're not used to using the INDIRECT function. Finally, the INDIRECT function can't be used in array formulas.

6. Use the OFFSET function

Another way to lock cell references is to use the OFFSET function. The OFFSET function is a built-in function in