In Excel I entered two formulas manually:

B3 = C3 * C2  
B4 = C4 * C2

I have a lot of rows so I wanna copy the formula down.
The problem is, I select them both and then pull down.

What happens is I instead get:

B5 = C5 * C4 (but the C4 should be C2).

Why doesn't Excel understand this even though I made the two first formulas manually?


Input into B3 = C3 * C$2 and then copy down.

C$2 is known as mixed cell reference. In this case, as the dollar sign ($) is put in front of the row number (2). It can help to fix the row number when copying.

Reference: http://blogs.office.com/b/microsoft-excel/archive/2011/08/17/making-sense-of-dollar-signs-in-excel.aspx

Absolute cell references

When a formula contains an absolute reference, no matter which cell the formula occupies the cell reference does not change: if you copy or move the formula, it refers to the same cell as it did in its original location. In an absolute reference, each part of the reference (the letter that refers to the row and the number that refers to the column) is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever the formula is copied or moved, it always refers to cell A1.

Relative cell references

In contrast, a relative reference changes if the formula is copied or moved to a different cell (i.e., a cell other than where the formula was originally entered). The row and column portions of a relative reference are not preceded by a “$” – for example, A1 is a relative reference to cell A1. If moved or copied, the reference changes by the same number of rows and columns as it was moved. So, if you move a formula with the relative reference A1 one cell down and one cell to the right, the reference changes to B2.

Mixed cell references

A mixed reference uses a dollar sign either in front of the row letter or in front of the column number, but not both – for example, A$1 is a mixed reference in which the row adjusts, but the column does not. So if you move a formula containing that reference one cell down and one cell to the right, it becomes B$1.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.