Dollar signs in Excel: relative, absolute and mixed references

You enter a formula, fill it down and see a sensible first result. The following results start behaving differently. Often, a reference has moved along with the copied formula. A dollar sign lets you specify which part of an address should stay fixed.

I start with one question: what should change when I copy the formula to a neighboring cell? The next product’s amount probably should change. A shared parameter usually should stay the same. Once I can describe those two roles, choosing the reference syntax becomes much easier than guessing which dollar signs to add.

What the letter and number mean

In the default A1 reference style, the letter identifies the column and the number identifies the row. B3 therefore means column B, row 3. That is the coordinate system used throughout this article. Unless I explicitly say otherwise, the examples copy formulas within one worksheet.

The reference syntax also describes its behavior during copying. A reference without dollar signs is relative, one with two is absolute, and one with a single dollar sign is mixed. This does not change the source cell’s value. It changes how its address is written inside a formula.

The following table shows a copy displaced one row down and one column to the right. Read it as a description of how the reference changes, without performing another calculation.

These rules are covered in Microsoft’s reference documentation. The dollar sign applies to the part immediately after it. Before a letter, it fixes the column; before a number, it fixes the row. I find it useful to say that explicitly before filling an entire table.

Relative references: the same pattern on the next row

Consider a synthetic dataset. Column A contains a product, B its unit price, C its quantity and D the amount to calculate. Row 1 contains headers. B2 is 10 and C2 is 2. B3 is 20 and C3 is 3.

In D2, I enter:

=B2*C2

The result is 20. Copying to D3 changes the references to B3 and C3, producing 60. This is the intended behavior. Each result uses the price and quantity from its own row instead of repeatedly returning to the first product.

I do not add dollar signs here as a precaution. Fixing both addresses absolutely would make every copied result calculate the first product. Excel need not display an error. It can return 20 in successive rows while the numbers continue to look plausible to someone scanning the sheet.

I read this formula relationally: take the cell two columns to the left and the cell one column to the left, both on the same row. That perspective explains why the same formula pattern can produce the correct calculation in many positions. A relative reference expresses a relationship that travels with the copy.

Absolute references: one shared parameter

Now I put an illustrative rate of 10% in F1. This is a practice parameter, unrelated to current taxes or a particular company’s rules. In E2, I want to calculate 10% of the amount in D2. Every product should use the same parameter.

I enter:

=D2*$F$1

With D2 equal to 20, the result is 2. The copy in E3 becomes =D3*$F$1 and gives 6 when D3 is 60. D2 moves with the copy, while F1 remains fixed. The formula combines a value belonging to the current row with a parameter shared by all rows.

Without dollar signs, copying down would point to F2. If that cell is empty, the result can stop resembling the expected calculation. The reader may eventually notice. I prefer spotting the unexpected F2 reference in the second row, before the formula reaches hundreds of records.

Keeping a parameter in a separate cell also makes an assumption easy to change. Updating F1 from 10% to 20% should double both results. There is no need to search for the number 0.1 inside every formula. This small example illustrates why I separate inputs from calculations.

Mixed references: copying in two directions

A single dollar sign is useful when I copy a formula both down and across. A small scenario table provides a good example. Column A holds amounts, while row 1 holds rates. Every interior cell should combine its row’s amount with its column’s rate.

The synthetic input layout is:

In B2, I enter:

=$A2*B$1

Copying right to C2 produces =$A2*C$1. The amount still comes from column A, while the rate moves to column C. The results are 10 and 20 respectively. Fixing row 1 keeps the rate reference on the header row.

Copying B2 down to B3 produces =$A3*B$1, or 200 multiplied by 10%, giving 20. In the bottom right cell, C3, the formula is =$A3*C$1 and gives 40. The amounts column and rates row remain correct, while the other coordinate parts adjust to the result’s position.

I read $A2 as “always column A, with a row that follows the copy.” I read B$1 as “a column that follows the copy, always row 1.” That is more precise than saying the cell is locked. A mixed reference fixes only one coordinate.

Choose the reference by describing the input

First, I describe the input’s role. If it should follow the next record in the same column, the row number remains relative. If it should always come from a header row, I fix that row number. Then I decide the column independently. Two small decisions are easier than choosing one of four patterns by instinct.

In the scenario table, should the amount move from A to B when I copy right? No, so I fix A. Should it move from row 2 to row 3 when I copy down? Yes, so the row stays relative. Applying the same reasoning to the rate gives B$1.

When copying only down, fixing just the row of a parameter can sometimes be sufficient. For a single shared parameter, I often use both dollar signs to express the intention clearly and allow for later copying across. The choice should follow the planned behavior of the sheet rather than a rule that every address needs two dollar signs.

F4 helps edit a reference

In Excel for Windows, while editing a formula, you can select a reference and use F4 to cycle through its variants. The editing context matters: I want to change an address inside the formula. Microsoft describes this in its formula tips.

If the key triggers a laptop hardware function, the required combination depends on keyboard settings. F4 is not a prerequisite for understanding references. You can type the dollar signs manually and the formula behaves the same way. On another platform, I check its shortcuts rather than assume an identical keyboard action.

After changing a reference type, I read the entire formula. With several addresses, it is easy to edit the wrong one. In a long calculation, I prefer identifying the parameters and separating readable stages first. A shortcut makes editing faster, but it cannot decide which input should remain fixed.

Copying differs from moving a formula

Copying adjusts relative references to the new position. Moving the formula itself with Cut and Paste leaves its internal references unchanged. The distinction is described in Microsoft’s move and copy guide.

If D2 contains =B2*C2, its copy in D3 uses B3 and C3. If I move the formula itself from D2 to E2, it still points to B2 and C2. Moving is a different operation from filling successive rows with the handle at the corner of the selection.

I do not extend that rule to every possible worksheet restructuring operation. Moving source cells, inserting a column and deleting a range are separate actions. After changing the layout, I inspect references again. A dollar sign does not promise that an address can never change under any operation, and it cannot rescue a deleted source.

Copying a cell versus copying formula text

Selecting the text in the formula bar and pasting it elsewhere transfers the literal address notation. I do not expect the same adjustment as when copying the whole cell. When someone reports that copied references stay unchanged, I check how they copied the formula. The visible result alone cannot establish whether they used the fill handle, copied a cell or pasted selected text. Microsoft Press describes this method.

Fixed ranges in lookup formulas

In a lookup, the product identifier usually changes by row while the lookup table should remain the same. I can fix its boundaries using a range such as $H$2:$I$4. Both endpoints have fixed columns and rows. The lookup value from the current record can remain relative.

Fixing the range does not settle matching behavior, duplicate keys or missing data. Those are separate decisions. For lookup logic, see the comparison of lookup functions. Reference behavior is one component of the complete formula.

I also check whether a newly added lookup record remains inside the selected area. A fixed range ending at row 4 does not automatically include any records entered farther down merely because it contains dollar signs. I need to choose the boundaries deliberately or use a structure that suits a growing dataset.

Excel tables and readable formulas

An actual Excel table supports structured references based on table and column names. That is a different notation from ordinary A1 addresses. Microsoft explains its syntax and behavior in the structured reference documentation.

I consider a table when the data regularly grows and the calculation applies to a column of records. I still need to understand where each value comes from. A column name can improve readability, but it cannot decide whether a rate is shared or assigned separately to each product.

I avoid changing every formula notation halfway through an exercise. First, I make the small example consistent and describe the input roles. Then I can move it into a table and inspect whether the references preserve the intended meaning. Learning another syntax is easier when the calculation is already understood.

Dollar signs and protection against editing

The phrase “lock a cell” can be ambiguous. Fixing its address with dollar signs does not prevent someone from entering another value into the source. If someone changes F1, formulas using $F$1 read the new parameter. That is exactly what this exercise expects.

Restricting editing requires cell settings and worksheet protection. This is a separate mechanism described in the worksheet protection guide. Microsoft also distinguishes its purpose: worksheet protection restricts changes and should not be treated as protection for the confidentiality of the file.

When someone asks me to lock something, I first establish the expected behavior. Should a formula keep its parameter when copied, or should a user be prevented from overwriting a result? A clear description of the problem helps choose the relevant feature and avoids solving a different problem from the one the reader has.

Check before filling an entire column

I begin with the first cell and one neighbor. I read the formula, identify its inputs and inspect the result using small numbers. Then I copy one row down. In the second row, I focus on addresses because a coincidentally identical result could hide an incorrect reference.

For a scenario table, I also inspect a copy to the right and the bottom right corner. Four cells let me trace both directions. I deliberately use different amounts and rates. When all inputs are identical, a bad reference can still return a number that appears correct.

Finally, I change the shared parameter and identify which results should respond. Changing one product’s amount should affect only the corresponding calculations. This checks the meaning of the layout as well as the behavior of a single address.

Three mistakes that return plausible numbers

The first mistake fixes the wrong coordinate. $F1 keeps column F but changes its row when copied down. If the only parameter is in F1, that notation misses the intended behavior. Conversely, F$1 keeps the row but moves to G1 when copied right. I consider both directions even if I initially need only one.

The second mistake confuses a shared parameter with an input belonging to a record. If each product has its own rate on its own row, fixing the first rate across the entire calculation column is a business error. The formula may look neat. Describing the input establishes whether it should read successive rates or repeatedly read the same one.

The third mistake checks only the final total. Errors in opposite directions can partially cancel, and a correct total can conceal misplaced row results. I inspect at least one specific record, preferably one whose price, quantity or rate differs from the first row.

When I find an error, I correct the template formula and copy it over the appropriate area again. Typing a correct result manually into a few cells hides the cause and breaks when the input changes. I want to leave a consistent calculation that another person can understand by reading neighboring formulas.

I also label the parameter cells when handing over a workbook. A brief description beside F1 identifies a rate, limit or scenario assumption. An address alone does not convey meaning. Entering 10 instead of 10% creates an input interpretation problem, so I review units and clarity alongside reference behavior.

A short exercise

Recreate the scenario table with amounts of 100 and 200 and rates of 10% and 20%. Enter one formula in B2 and copy it to the other three result cells. The expected values are 10, 20, 20 and 40. Explain which dollar sign fixes a column and which fixes a row.

Next, remove one dollar sign and predict the address change before copying. Do not look only for an error message: the wrong reference can point to an existing number. Restore the intended formula and compare copying it with moving it. Being able to explain the difference gives you a foundation for reading much longer formulas.