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