If you have been working in Excel for more than a second day, then you have probably already met or used dollar-sign references in Excel formulas and functions, for example $ D $ 2 or F$3 etc. Let’s finally figure out what exactly they mean, how they work and where they can be useful in your files.
Relative links
These are regular references in the form of a column letter-row number ( A1, С5, i.e. “battleship”) found in most Excel files. Their peculiarity is that they are shifted when copying formulas. Those. C5, for example, turns into С6, С7 etc. when copying down or to D5, E5 etc. when copying to the right, etc. In most cases, this is normal and does not create problems:
Mixed links
Sometimes the fact that the link in the formula, when copied, “slides” relative to the original cell is undesirable. Then, to fix the link, the dollar sign ($) is used, which allows you to fix what it comes before. Thus, for example, the link $C5 will not change across columns (i.e. С will never turn into D, E or F), but may shift across lines (i.e. may shift by $C6, $C7 etc.). Likewise, C$5 – will not move along the rows, but can “walk” along the columns. Such links are called mixed:
Absolute links
Well, if you add both dollars to the link at once ($C$5) – it will turn into absolute and will not change in any way during any copying, i.e. dollars are fixed tightly and the row and column:
The easiest and fastest way to turn a relative reference into an absolute or mixed reference is to select it in the formula and press the F4 key several times. This key circles all four possible options for fixing a link to a cell: C5 → $C$5 → $C5 → C$5 and all over again.
Everything is simple and clear. But there is one “but”.
Suppose we want to make an absolute cell reference С5. Such that she ALWAYS referred to С5 regardless of any further user action. It turns out a funny thing – even if you make the link absolute (i.e. $C$5), it still changes in some situations. For example: If you delete the third and fourth lines, it will change to $C$3. If you insert a column to the left С, then it will change to D. If you cut a cell С5 and paste into F7, then it will change to F7 and so on. What if I want a really hard link that will always refer to С5 and nothing else under any circumstances or user actions?
Truly absolute links
The solution is to use the function INDIRECT (INDIRECT), which generates a cell reference from a text string.
If you enter the formula in a cell:
=INDIRECT(“C5”)
=INDIRECT(«C5»)
then it will always point to the cell with the address C5 regardless of any further user actions, inserting or deleting rows, etc. The only slight complication is that if the target cell is empty, then INDIRECT outputs 0, which is not always convenient. However, this can be easily circumvented by using a slightly more complex construction with a check through the function ISBLANK:
=IF(ISNULL(INDIRECT(“C5″)),””, INDIRECT(“C5”))
=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))
- XNUMXD sheet group references when consolidating data from multiple tables
- Why you need the R1C1 link style and how to disable it
- Exact copying of formulas by a macro with the PLEX add-on