Formula Errors

How Dollar Signs Affect Excel Cell References

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to understand the function of dollar signs ($) in Excel formulas and how they impact cell references when formulas are copied or dragged across cells.

Product: Excel
Device & OS: not provided
Scenario: Copying and pasting formulas across multiple rows or columns in a spreadsheet without breaking calculations.
Observed behavior: By default, relative references change when copied. Applying a dollar sign ($) creates an absolute reference, which keeps the designated column or row fixed during the copy process.

Before you start

Before modifying your formulas, identify which specific data points (like a fixed tax rate or total sum) need to remain constant across all your calculations.

Solution 1Recommended

Using Dollar Signs to Create Absolute Cell References

Apply dollar signs ($) to lock the column, row, or both, preventing them from automatically shifting when you copy or drag the formula to a new location.

In Excel, a standard relative cell reference (like G11) automatically updates when copied to another cell. This is useful for scaling calculations across a dataset. However, an absolute cell reference (like $B$7) uses dollar signs to remain fixed, no matter where you paste the formula.

When you apply a dollar sign before the column letter, the column is locked. When you apply it before the row number, the row is locked. Using both locks the exact cell.

Select the formula cell

Double-click on the cell containing your formula or click it once and place your cursor inside the formula bar at the top.

Apply the dollar signs manually or via shortcut

Locate the cell reference you want to keep constant (e.g., B7). Type a dollar sign before both the column letter and the row number to make it $B$7. Alternatively, click within the reference and press the F4 key to automatically add the dollar signs.

Copy the locked formula

Press Enter to apply the changes. Click and drag the fill handle (the small square at the bottom-right corner of the cell) across your desired range. The absolute references will remain perfectly fixed.

Mixed References: You can also use mixed references to lock only one dimension. For example, $B7 locks only the column, while B$7 locks only the row.

Manage Formulas Easily

Use WPS Spreadsheet to Manage Cell References Seamlessly

WPS Spreadsheet provides an intuitive and powerful interface for creating complex formulas, fully supporting standard absolute, relative, and mixed cell references just like Microsoft Excel.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the data you need to calculate.
2. Input your formula: Select the target cell, type the equals sign (=), and select your desired cell references to build the formula.
3. Lock references quickly: Highlight the cell reference in the formula bar and press F4 to instantly cycle through absolute ($A$1), mixed (A$1 or $A1), and relative (A1) references.

Fully compatible with standard Microsoft Excel formulas, functions, and formattingQuickly toggle between absolute and relative references using the F4 key shortcutFree, lightweight, and operates smoothly across Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

What is the keyboard shortcut to add dollar signs to an Excel formula?

You can quickly add dollar signs to lock a cell reference by placing your cursor on the cell reference in the formula bar and pressing the F4 key. Pressing F4 multiple times cycles through the absolute, mixed, and relative reference formats.

What does $A1 mean in an Excel formula?

$A1 is a mixed reference. The dollar sign before the 'A' locks the column so it won't change when copied horizontally, but the row number '1' lacks a dollar sign and will still adjust dynamically when copied vertically.

Why is my formula showing an error when copied to another cell?

If you copy a formula without using dollar signs to make certain references absolute, Excel automatically shifts the relative references. This can cause your formula to point to blank cells or text strings, often resulting in #REF!, #VALUE!, or incorrect calculation outputs.

Excel & Data Problems