Absolute and Relative Cell References, Sequential Data, Excel Tips (Tech Tip 12/18/13)
Posted by on 23 December 2013 02:49 PM
Absolute and Relative Cell References
Excel accepts cell references in what are called absolute and relative ranges. Absolute ranges have a $ character before the column portion of the reference and/or the row portion of the reference. Relative ranges do not use the $ character. The $ character indicates to Excel that it should not increment the column and/or row reference as you fill a range with a formula or as you copy a range. For example A1 is a relative range, while $A$1 is an absolute range. If you enter =A1 in a cell and then fill that cell down a column, the '1' in the reference will increment in each row. Thus, the formula in row 50 would be =A50. However, if you enter =$A$1 in a cell and fill down, the range reference will remain $A$1 -- it will not increment as you fill or copy down a column.
There are three absolute styles:
If you select all or part of a formula in the formula, you can press F4 to cycle range reference between the 4 styles (1 relative and 3 absolute).
Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. This works because Excel does not interpret the string "A1" as an address. Instead, it treats it as plain text and therefore does not change it.
A common use of mixing absolute and relative range specifications is to create a running total of a column of number. For example, if you have data in cells A1:A10, the formula =SUM(A$1:A1) in cell B1 and filled down to cell B10 will return the running total for the numbers in column A.
Please see this tutorial on copying-down sequential data in excel: http://www.excelfunctions.net/Excel-Autofill.html. As you can see, this copy-down feature is useful when copying formulas with absolute and relative cell values.
Note: To edit the forumla for a cell, you can press the F2 key.