Dollar signs – quick guide

A quick recap on the $ signs in formulas

When a formula references another cell, that cell reference has a column letter and a row number, for example, A1 or CX216.  If a formula with a cell reference is copied, you can control how that cell reference behaves. You can make it point to the exact same cell, or you can let it adjust when the formula is copied. To do that, you can use the $ signs in the cell reference. Here’s how they work.

The $sign in a cell reference locks it, so it won’t change when copied down or across. The $ sign can be applied to the column letter, the row number, or both.

Relative reference

The reference A1 copied down will become A2. Copied across, it will become B1. Both the column letter and the row number are relative and will change when the formula is copied.

Lock in the column letter only (mixed reference)

The reference $A1 copied down will become $A2. Copied across, it will be $A1. The column letter is now fixed (absolute) and will not change when the formula is copied.

Lock in the row number only (mixed reference)

The reference A$1 copied down will be A$1. Copied across, it will be B$1. The row number absolute and will not change when the formula is copied.

Absolute reference

The reference $A$1 copied down or across will be $A$1. Neither row number nor column letter in an absolute reference will change.

Quickly change the reference type

animatedF4When you edit a formula you don’t have to type the $ signs manually. Often it is quicker to let Excel do that work. Place the cursor inside or just behind a cell reference and hit the function key F4 on your keyboard. Each press of this key will change the reference type. From relative to absolute to locked row to locked column.

Try it yourself

Here is a sample file. Four different ways to write a formula that references cell A1. Each time, the formula has been copied down two rows and then across two columns. Note that the F4 function key only works if the file is open in Excel for desktop, not in Excel online in the browser.

One comment

  1. It is good to see again clearly shown how you can control the way that cell reference behaves, thanks.

    Soon after starting with Excel , I came across the $ signs and absolute referencing possibility in the “A 1 column letter and row number notation”. I got slightly confused again later when I encountered the other notation type , ( “R C row and column number notation” ).
    I have found it helpful to think slightly differently in this general area and try to explain at an early stage to someone learning about absolute and relative referencing in a slightly different way. This is because I feel the choice of terminology can be very confusing if , as is often the case, one first meets the “A 1 column letter and row number notation” first and then sometime later one is introduced to the “R C row and column number notation”
    I would start with saying that Excel itself can hold internally the information relating to how a formula in a current main cell references another cell in two specific ways:

    _1) A simple fixed r, c ( like y, x or j, i etc.. ) coordinate system. This is absolute referencing

    _2) This second way effectively holds instead 2 vectors, rvec, cvec that are again held by Excel as fixed values and are based on the offset, or “how to get ““from”” the current cell to the other referenced cell”.

    Dragging down , copying and pasting over an extended range Area, or any array type entry using _1) will result in every new cell referencing the same original other cell. This is because the co ordinates are copied.
    Doing the same with _2) means that the fixed vector values are copied instead. Hence cells offset from the original main cell will refer accordingly to cells offset by the same amount from the original other cell.
    Having understood this, I would look at both “R C row and column number notation” and “A 1 column letter and row number notation” at the same time. I would consider the different notation convention as simply a different way to display the same thing. This is helpful, I feel, as often one is mislead into thinking that they are very different. It is just the terminology which, whilst having its uses, is very confusing.

    ( Consider for simplicity of explanation, that I am not considering mixed absolute and relative referencing, but noting that it is possible in both notation conventions )
    In the “A 1 column letter and row number notation”: ( Not mixed case )
    The use of a $ before the letter and a the use of $ before the number means that absolute references are being used for both the column and the row. As a cell is copied to other cells the reference, as seen displayed in the cell, will not change.
    The absence of a $ means that relative references are being used. As a cell is copied to other cells the reference, as seen displayed in the cell, will change appropriately to refer to different cells.
    The letter always refers to the actual column of the referenced cell. The number always refers to the actual row of the referenced cell.
    Having a $ or not tells us whether we have absolute or relative referencing.

    In the “R C row and column number notation”: ( Not mixed case )
    The use of a simple number after the R and a simple number after the C means we are using absolute referencing for both the row and the column. As a cell is copied to other cells the reference, as seen displayed in the cell will not change.
    To indicate relative referencing a pair of [ ]s are included, pseudo like
    R [rvec] C[cvec]
    As a cell is copied to other cells the reference, as seen displayed in the cell will not change. This is the significant difference: For “R C row and column number notation” in relative referencing we actually use the fixed row and column vectors rather than the actual cell r c coordinates.
    Having a [ ] or not tells us whether we have relative or absolute referencing. ( So sort of the other way around to the “A 1 column letter and row number notation” )

    I think often one confuses the R C as meaning relative referencing and leads initially to some confusion. Both notations are almost always interchangeable. It is just how the final reference is displayed that is different. This last point can occasionally be useful:
    A nice example: I want to use a reference within a formula that will always refer to the current cell in which the formula is. I need some way to make that reference effectively have always rvec and cvec of zero. For the “A 1 column letter and row number notation” this is quite tricky. I do not have available a way to enter those rvec=0 and cvec=0 which I want to have. I would need to use the Name of a named range which is referenced, ( the RefersTo:= argument ), with relative referencing, such as using for that argument , !A1 ( workbooks scope here) , when the selected cell is A1.
    If I changed my default Excel settings to “R C row and column number notation”, then in a formula I would simply use the reference R[0]C[0] ( or RC which defaults to R[0]C[0] )
    Alan

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s