sodcalculator

10 Tech How-To

Google Sheets Dollar Sign Meaning & How to Use It

Guide 10-54593 · Updated · Step by step

In Google Sheets, the dollar sign ($) in a formula is not a currency symbol—it is an absolute reference marker. It locks either the column, the row, or both so that when you copy or fill a formula to other cells, the referenced cell does not shift. You enter it manually or press F4 (on Windows) or Fn+F4 (on Mac) while editing a formula. This article explains exactly what the dollar sign does, how to type it, when to use it, and common mistakes to avoid.

What the Dollar Sign Actually Does

Google Sheets uses A1 notation for cell references. A reference like B2 is relative: if you copy a formula from C1 to D1, the reference automatically changes to C2. The dollar sign turns a relative reference into an absolute one by locking part of it:

  • $B$2 – locks both column and row (absolute). Copying the formula anywhere keeps it pointing to B2.
  • $B2 – locks only the column. The row can change when you copy down or up.
  • B$2 – locks only the row. The column can change when you copy left or right.

This is standard spreadsheet functionality, not a Google Sheets quirk. It works the same way in Microsoft Excel, LibreOffice Calc, and Apple Numbers. The dollar sign has no effect on the displayed value or on currency formatting—it only controls how references behave during copy and fill operations.

Why You Need Absolute References

Most formulas use relative references because you want them to adjust for each row. For example, if you have a list of quantities in column A and prices in column B, a formula in C1 like =A1*B1 will correctly become =A2*B2 when filled down. But consider a sales tax rate stored in cell D1. If you write =A1*B1*D1 in C1 and fill down, the last part becomes D2, D3, and so on—which are probably empty or wrong. To keep the tax rate fixed, you write =A1*B1*$D$1. Now when you fill down, the D1 stays locked while A and B still adjust. This is the most common use case: referencing a single constant (tax rate, discount percentage, conversion factor, unit price) across many rows.

How to Type the Dollar Sign

You can type the dollar sign manually, but the fastest way is to use the keyboard shortcut that toggles through reference states:

  1. Click on the cell with the formula, then click into the formula bar (or press F2).
  2. Place the cursor on the reference you want to lock (e.g., on D1).
  3. Press F4 (Windows/ChromeOS) or Fn+F4 (Mac). Each press cycles: D1 → $D$1 → D$1 → $D1 → back to D1.

If you prefer typing, just add the dollar signs manually: $D$1. You can also use the Insert menu? No, there is no menu option—typing or F4 are the only ways. The F4 shortcut works while editing a formula, not when you simply select a cell.

Mixed References: When to Lock Only One Part

Sometimes you need to lock only the column or only the row. This is called a mixed reference. A classic example is a multiplication table. Suppose you have numbers 1–5 in row 1 (B1:F1) and numbers 1–5 in column A (A2:A6). You want each cell to be the product of its row header and column header. In cell B2, the formula should be =$A2*B$1:

  • $A2 – locks column A so that when you copy right, it always refers to the row header, but the row number can change when you copy down.
  • B$1 – locks row 1 so that when you copy down, it always refers to the column header, but the column letter can change when you copy right.

Filling this formula across the whole table gives correct products everywhere. Without the mixed locks, the references would drift and the table would be wrong. Mixed references are also useful when you have a list of items and a list of locations, and you want to build a grid of combinations.

Common Mistakes and How to Avoid Them

The most frequent error is forgetting to lock a reference and then copying the formula, which produces wrong results or #REF! errors. Another is locking the wrong part—for example, using $B2 when you actually want B$2. To debug, click on a filled cell and look at the formula bar: if the reference shifted unintentionally, you know it was relative. A quick fix is to use the F4 shortcut to cycle through the lock states until you get the right one.

Also note that the dollar sign does not affect currency formatting. If you want to display numbers as currency, use the Format → Number → Currency menu. The dollar sign in formulas is purely a reference lock—it never changes the value or the display.

Another mistake is applying the dollar sign to a range incorrectly. For example, =SUM($A$1:A10) locks the start of the range but leaves the end relative. That is intentional when you want the range to expand as you copy down, but it can be confusing. If you want the entire range fixed, use $A$1:$A$10.

When Not to Use the Dollar Sign

Do not use the dollar sign when you want the reference to adjust. Most formulas that operate row-by-row on data in the same row should use relative references. For example, =A1+B1 filled down should become =A2+B2. Adding dollar signs there would lock the first row and break the pattern. Also, avoid dollar signs in named ranges—if you define a name like TaxRate for cell D1, you can use =A1*B1*TaxRate and it will always refer to D1 without any dollar signs. Named ranges are often more readable than absolute references, especially in complex formulas.

Frequently Asked Questions

Does the dollar sign in Google Sheets mean currency?

No. In a formula, the dollar sign is an absolute reference marker. It locks the column, row, or both. To format a number as currency, use the Format menu, not the dollar sign in the formula.

How do I add a dollar sign to a formula quickly?

Click into the formula bar, place the cursor on the reference you want to lock, and press F4 (Windows) or Fn+F4 (Mac). Each press cycles through the four lock combinations.

What is the difference between $A1 and A$1?

$A1 locks the column (A) but lets the row change when you copy down. A$1 locks the row (1) but lets the column change when you copy across. Use the one that matches the direction you will fill.

Can I use absolute references in conditional formatting or data validation?

Yes. In custom formulas for conditional formatting or data validation, you can use dollar signs just like in regular formulas. For example, to highlight an entire row based on a value in column A, you might use =$A1>100 applied to the range A1:Z100.

Why is my formula showing #REF! after I copy it?

This usually happens when a relative reference shifts outside the sheet’s boundaries. For example, copying a formula from the last row that references the next row will cause #REF!. Adding dollar signs to lock the reference prevents this if the locked cell is within the sheet.

Does the dollar sign work the same in Excel?

Yes. The dollar sign for absolute references works identically in Microsoft Excel, LibreOffice Calc, and Apple Numbers. The F4 shortcut also works in Excel on Windows and Mac (though on Mac it may require Fn).

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →