Overview
There is a general principle within Microsoft Excel that we should always avoid entering values directly into formulas and functions. The issue with doing so is that if the value changes, then the formula has to be manually updated. In most cases it would be better to declare these potential variable values in their own cells and reference them instead. This works fine until we need to copy the formula, when we find that there are problems. In this article we look at the problem and the fix, known as an 'absolute cell reference'.
The Problem in Detail
When we reference cells in Excel, by default they are treated as being 'relative', which is to say that they are relative to the current row. If we have a number of formulas of the same type in a column or a row, the simplest way of completing them all together is to use the 'AutoFill Handle'. This is the small black square which is situated at the bottom right-hand corner of every cell on the spreadsheet. Hovering the mouse over this will produce a cursor which looks like a small plus sign. If the AutoFill Handle is clicked and dragged down a column or across a row it will replicate the formula in every cell in the range.
However, the formula is not exactly the same; if it were then we would get the same result in every cell! Instead Excel does something rather clever. It changes each cell reference to reflect the row in which the new formula is placed. So for instance clicking on the fill handle on cell A1 and dragging down will change a cell reference in say B1 to B2, B3, B4 etc. as the formula is copied down the column. This is known as a 'relative cell reference' and is one of the most useful features within Microsoft Excel. So what's the problem? Well, consider the previous assertion that it is better to reference potential variables. If we reference a single cell then use the AutoFill handle on a formula with multiple references, all will be treated as relative having the effect of incrementing the row or column when dragged across the range. This means that what should be a fixed cell reference at say B1, may change to B2, B3 etc. What seemed like a great idea suddenly now looks less promising; however the solution is really very simple and highly effective.
The Simple Fix
What is required is a mechanism by which we can 'fix' the variable cell reference when the AutoFill Handle is applied to the formula containing it. The way we do this is to enter a $ sign before the column or row which is to be fixed, or remain constant. The $ sign acts like a sort of anchor, fixing the cell in place and preventing it from incrementing when the row or column changes. Consider the following example: we have a formula in cell B1 and wish to reference a variable value in cell A1. If we use AutoFill to copy the formula down column B, then the relative reference to cell B1 will change to B2, B3, B4, etc. Now, if we add a $ to the cell reference in the formula so that it reads B$1, the row will be 'anchored' in place when AutoFill is used. In other words, the cell reference remains as B$1 in every cell in which the formula is copied into. This is known as an 'absolute cell reference'. A shortcut to creating absolute cell references is to simply press the F4 key just after you have entered the cell reference into the formula. This has the effect of putting a $ in front of both the row and the column, effectively anchoring the cell in two dimensions.
There is a general principle within Microsoft Excel that we should always avoid entering values directly into formulas and functions. The issue with doing so is that if the value changes, then the formula has to be manually updated. In most cases it would be better to declare these potential variable values in their own cells and reference them instead. This works fine until we need to copy the formula, when we find that there are problems. In this article we look at the problem and the fix, known as an 'absolute cell reference'.
The Problem in Detail
When we reference cells in Excel, by default they are treated as being 'relative', which is to say that they are relative to the current row. If we have a number of formulas of the same type in a column or a row, the simplest way of completing them all together is to use the 'AutoFill Handle'. This is the small black square which is situated at the bottom right-hand corner of every cell on the spreadsheet. Hovering the mouse over this will produce a cursor which looks like a small plus sign. If the AutoFill Handle is clicked and dragged down a column or across a row it will replicate the formula in every cell in the range.
However, the formula is not exactly the same; if it were then we would get the same result in every cell! Instead Excel does something rather clever. It changes each cell reference to reflect the row in which the new formula is placed. So for instance clicking on the fill handle on cell A1 and dragging down will change a cell reference in say B1 to B2, B3, B4 etc. as the formula is copied down the column. This is known as a 'relative cell reference' and is one of the most useful features within Microsoft Excel. So what's the problem? Well, consider the previous assertion that it is better to reference potential variables. If we reference a single cell then use the AutoFill handle on a formula with multiple references, all will be treated as relative having the effect of incrementing the row or column when dragged across the range. This means that what should be a fixed cell reference at say B1, may change to B2, B3 etc. What seemed like a great idea suddenly now looks less promising; however the solution is really very simple and highly effective.
The Simple Fix
What is required is a mechanism by which we can 'fix' the variable cell reference when the AutoFill Handle is applied to the formula containing it. The way we do this is to enter a $ sign before the column or row which is to be fixed, or remain constant. The $ sign acts like a sort of anchor, fixing the cell in place and preventing it from incrementing when the row or column changes. Consider the following example: we have a formula in cell B1 and wish to reference a variable value in cell A1. If we use AutoFill to copy the formula down column B, then the relative reference to cell B1 will change to B2, B3, B4, etc. Now, if we add a $ to the cell reference in the formula so that it reads B$1, the row will be 'anchored' in place when AutoFill is used. In other words, the cell reference remains as B$1 in every cell in which the formula is copied into. This is known as an 'absolute cell reference'. A shortcut to creating absolute cell references is to simply press the F4 key just after you have entered the cell reference into the formula. This has the effect of putting a $ in front of both the row and the column, effectively anchoring the cell in two dimensions.
I read your article. You share such great information about cell references in Microsoft excel. You have done great project to here.
ReplyDeletephotoshop training sydney
i don't have much experience on Excel but this post will help me alot in working on excel more easily and efficiently The Seo Portal
ReplyDelete