Relative and Absolute cell references are a very basic concept in Excel. But since many people have learned Excel on the job with no formal training, I thought it would be a good topic to cover. Since we’re going back to basics, a cell reference is simply the column and row of the cell. For example, the cell in the top left corner is cell A1. In most cases, when you enter a formula Excel automatically enters relative cell references. Let’s say you put a total at the bottom of some cells in column A. Then you copy that formula to column C. When the formula uses relative references, Excel will adjust the cell references in the formula relative to its new location. So after copying it to column C it will now the cells in column C instead of column A. If the formula had used absolute references it would still total the cells in column A no matter where you copy.
Relative references use just the letter of the column and number of the row, such as A1. You can make the reference absolute by adding a $ to the column, row, or both. A reference to $A$1in a formula would remain unchanged when you copy it. $A1 would adjust the row number when copied but would still point to column A. And A$1 would keep the row number the same while adjusting the column reference. You can also highlight all or part of a formula and press F4 to change between relative and absolute references.
Here is a good example of how this can save you time. Let’s say you have a list of sales by department in Excel. Column A holds the department names, column B holds this month’s sales, and column D holds the year to date sales. The totals for both columns are in row 10. You need to see each department’s sales as a percentage of total sales. You could go to the empty cell at C2 and enter “=B2/B10” (dept 1 monthly sales divided by total monthly sales), then in C3 type “=B3/B10”, and so on down the list. But who wants to have to type a formula on every line, and if you copy that formula down column B, the second line would become “=B3/B11” (dept 2 sales divided by an empty cell one row below total sales). To fix the problem, change the first formula to “=B2/B$10”. Now when it is copied down the column, the relative reference B2 will be adjusted for each row, but B$10 will remain the same. Next, highlight cells C2 through C10, copy them and paste them into column E, next to the YTD amounts. Now the references to column B (monthly sales) will automatically be changed to D (YTD sales).
