Dollar Sign in Excel: How to Lock Cell References

Dollar Sign in Excel Formulas (Cell References)

In this section, I will explain how the dollar sign locks cell references in formulas and why this matters for your work. Excel uses three types of references: Relative, Absolute and Mixed references and I’ll be touching on each of them.

Relative References (No $)

All references in Excel are relative by default. What this means is when you write a formula like = A1 + B1 in cell C1 and copy it down to C2, Excel automatically adjusts the references and the formula becomes = A2 + B2. Each cell reference shifts relative to its new position.

They work because most formulas follow a pattern down rows or across columns. You don't want to type the same formula repeatedly when you can copy it once, and Excel intelligently adapts each reference.

Absolute References ($A$1)

With this reference, you lock both the column and row. What this does is unlike the relative reference. When you write =$A$1, Excel keeps that cell reference fixed, and if you copy this formula anywhere in that sheet (C10, B20) to another sheet, it will always reference A1. 

Using an absolute reference solves a specific problem, which is: Imagine you have to calculate 40% off each item price in a sales dataset, and that is stored in cell D2, and you’re doing this for about 80 records. You don’t want to type the same formula 80 times, but rather copy one formula down.

The problem with trying to do that is if you write = E3*D2 in the first row and copy it down, this happens:

• Row 1: =E3*D2 (correct—calculates E3 times D2)

• Row 2: =E4*D3 (wrong—D3 is empty!)

• Row 3: =E5*D4 (wrong—D4 is empty!)

• Row 4: =E6*D5 (wrong—D5 is empty!)

Excel shifts both references down, and you only need the column E reference shifted and D to remain the same.

The solution to this is to use =E3*$D$2.

Now, when you copy it down, this happens:

• Row 1: =E3*$D$2 (E3 times the discount rate)

• Row 2: =E4*$D$2 (E4 times the same discount rate)

• Row 3: =E5*$D$2 (E5 times the same discount rate)

• Row 4: =E6*$D$2 (E6 times the same discount rate)

See the difference? E shifts (E3 becomes E4 becomes E5) while $D$2 stays locked. It never becomes $D$3 or $D$4. This technique is very important in financial modeling, and to understand more about financial modelling, you can take our Financial Modeling in Excel course. 

Mixed References ($A1, A$1)

Mixed references are used to either lock the column or row, but not both. This is powerful once you understand how to apply it. There are two ways to use the mixed reference; $A1 and A$1.

• =$A1 locks the column (A) but allows the row to change. When you copy this formula right to column B, it stays A, copy it down to row 2, and it becomes $A2.

• =A$1 locks the row (1) but allows the column to change. When you copy this formula right to column B, it becomes B$1, copy it down, and it remains row 1.

I’ve covered what each reference does. Now allow me to include a reference table which I may find helpful:

Here are the rules in a nutshell:

How to Add a Dollar Sign in Excel Using F4

To increase productivity, you can use the F4 key to cycle through the different reference types, which saves you the stress of manually typing the dollar sign.

To use this technique, click into a cell and start writing your formula, for example, =A1+B1

Stop typing and click right before or inside the A1 part (the part you want to change).

Press F4

Excel cycles through the different reference types:

• First press: A1 becomes $A$1 (both locked)

• Second press: $A$1 becomes A$1 (row locked, column free)

• Third press: A$1 becomes $A1 (column locked, row free)

• Fourth press: $A1 becomes A1 (back to the start)

When you get to the right reference format for your use case, stop pressing.

One thing to note is this shortcut works on both Windows and Mac; however, if F4 doesn't work on your Mac, check whether Function Lock is enabled or try Fn+F4. If the F4 doesn’t work at all, simply type the dollar sign yourself.

To understand more about shortcuts in Excel, the Excel Shortcuts Cheat Sheet is a good place to start.

Common Mistakes with Dollar Signs in Excel

There are certain mistakes you have the tendency to make while making use of the dollar sign in Excel for the first time and they are:

• Locking too much: You might often lock everything. You write something like =$A$1+$B$1+$C$1 when =A1+B1+C1 would suffice. While this doesn't break anything, it makes things unnecessarily complex. Lock only what needs locking. I recommend starting with relative references and adding locks only when a formula produces incorrect results.

• Forgetting to lock when necessary: The opposite error is copying a formula that references a constant without locking the reference. Your discount calculation references the discount rate in D2. You copy the formula down, intending D2 to remain fixed, but you used =amount*D2 instead of =amount*$D$2. Row 2 shifts to row 3, row 4, and so on. Your calculations break.

• Copying formulas without checking references: Always verify that your formula references adjust correctly after copying. Take thirty seconds to click the destination cell and examine the formula bar. Did the references change as expected? If not, adjust with F4 or manual edits.

Dollar Sign in Excel for Currency Formatting

If you landed here because you wanted to display dollar signs in your cells rather than use them in formulas, here's what you need to know.

When you apply currency formatting to a cell, what Excel does is to add the $ symbol to the display. You can do this in 3 ways:

• Right-click the cell → Format Cells → choose Currency.
• Use the $ button in the toolbar (Home tab, top of the screen).
• Press Ctrl+1 on Windows or Cmd+1 on Mac, then pick Currency.

Excel also offers two currency formatting styles:

• Currency Format: With this, the $ symbol sits right next to the first digit of a formatted cell.
• Accounting Format: The $ symbol aligns to the far left of the cell you formatted and the number on the far right which makes it easier to read columns of data because decimals line up perfectly.

One thing you should note is whatever formatting applied does not affect calculations in any way. For example, if in cell A1, you have $1,500.00 and in cell A2, you have 2, when you perform an operation, cell A1 is recognized as “1500” regardless of the formatting applied. Localization matters for symbols, but the underlying logic stays the same.

Conclusion

I hope this article has helped you understand how it is used to lock cell references in formulas, controlling how references adjust when formulas copy to other cells.

Another important takeaway from this article should have an understanding what reference type to use from the three mentioned in the article (Relative (A1), Absolute ($A$1) and Mixed ($A1 or A$1) references).

With all that said, the best way to understand concepts like this is by practising. Build a small spreadsheet of dataset, copy a formula down while watching the references shift, press F4 and cycle through reference types. Doing this will help solidify your understanding of the concept better than just a theoretical explanation.