There is also a general principle within

There is also a general principle within Microsoft Exceed that we should always avoid entering areas directly into formulas and functions. The problem with doing so is that if the worth changes, then the formula conseil en r&amp;eacute;f&amp;eacute;rencement has to be personally updated. In most cases it would be better to declare these potential variable values inside their own cells and reference these people instead. This works fine until we need to copy the formula, whenever we find that there are problems. In this article we look at the problem and the fix, called an 'absolute cell reference'.

Once we reference cells in Excel, by default they are treated as being 'relative', which is to say that they are relative to the current line. If we have a number of formulas of the identical type in a column or a row, the simplest way of completing them all at the same time is to use the 'AutoFill Handle'. It is the small black square which is positioned at the bottom right-hand corner of every cell on the spreadsheet. Hovering the computer mouse over this will produce a cursor which in turn looks like a small plus sign. In the event the AutoFill Handle is clicked plus dragged down a column or perhaps across a row it will replicate the formula in every cell in the range.

However, the formula is just not exactly the same; if it were then we might get the same result in every mobile! Instead Excel does something instead clever. It changes each cell reference to reflect the row where the new formula is placed. So for example clicking on the fill handle about cell A1 and dragging along will change a cell reference in say B1 to B2, B3, B4 etc. as the formula can be copied down the column. This is termed as a 'relative cell reference' and is one of the useful features within Microsoft Exceed. So what's the problem? Well, look at the previous assertion that it is better to personal reference potential variables. If we reference an individual cell then use the AutoFill take care of on a formula with multiple recommendations, all will be treated as relatives having the effect of incrementing the line or column when dragged through the range. This means that what should be a repaired cell reference at say B1, may change to B2, B3 etc. What seemed like a great idea suddenly at present looks less promising; however the fix is really very simple and highly effective.

What exactly is required is a mechanism by which we can 'fix' the variable cell guide when the AutoFill Handle is placed on the formula containing it. Just how we do this is to enter the $ sign before the column or perhaps row which is to be fixed, or even remain constant. The $ indication acts like a sort of anchor, fixing the cell in place and stopping it from incrementing when the row or column changes. Consider the following example: we have a formula within cell B1 and wish to reference a new variable value in cell A2. If we use AutoFill to copy the formula down column B, then a relative reference to cell B1 changes to B2, B3, B4, and so forth Now, if we add a $ to the cell reference in the formula in order that it reads B$1, the row will be 'anchored' in place when AutoFill can be used. In other words, the cell reference remains to be as B$1 in every cell when the formula is copied into. This really is known as an 'absolute cell reference'. A shortcut to creating absolute cellular references is to simply press the particular F4 key just after you have got into the cell reference into the formula. This has the effect of putting a dollar in front of both the row and the steering column, effectively anchoring the cell in two dimensions.

The AutoFill handle makes light work of duplication formulas down rows or throughout columns, but without the absolute mobile reference it would be rendered useless in many instances. Put the two techniques together in addition to a very powerful of way of upgrading your spreadsheets, potentially saving you a great deal of work in the future.