Spread Sheets‎ > ‎

ss-Relative vs Absolute Cell Addresses

If you plan on copying formulas, you might want to specify absolute references in the original formula—to keep if from adjusting to new addresses when you copy it.

An example to follow

Relative Addresses
Unless you specify otherwise, cell references in a formula are relative. A spreadsheets finds each cell reference by its position in relation to the cell containing the formula, not by its address.

For example, if you enter +A1+A2 in cell A3, the spreadsheet interprets it as "Add the values in the two cells directly above this one." When you copy this formula to cell B4, the spreadsheet adjusts the formula to add the values in cells B2 and B3 (the two cells directly above).

Absolute Addresses
To prevent a cell reference from adjusting when you copy a formula, make the reference absolute by entering dollar signs ($) before the column and the row coordinates. An absolute cell reference always refers to the original cell address, regardless of where you copy the formula. 

Note: You can also make a sheet reference absolute if you are copying from one sheet to another.

For example, if you enter = $A$1+$A$2 in cell A3, the spreadsheet reads this as "Add the values in the cells A1 and A2." When you copy this formula to cell B4, Corel Quattro Pro still refers to cells A1 and A2.

Two ways yo make a cell address absolute (instead of relative):
  1. Automatically -- Use the Abs key (F4) while in Edit mode to quickly make the selected cell reference absolute.
  2. Manually -- Type dollar signs ($) before the column and the row coordinates you want to remain fixed.


More Advanced Cell Addressing
You can specify all or part of a cell address as absolute. Just insert a dollar sign ($) before the coordinate you want to remain fixed. For example,

$A$1 makes both coordinates of address A1 absolute.
$A1 locks the address into column A, but lets the row coordinate adjust.
A$1 locks the address into row 1, but lets the column coordinate adjust.
$A:A$1 locks the address into sheet A and row 1, but lets the column coordinate adjust.

When copying between sheets, you can make sheet references absolute, also. Include a dollar sign before the sheet name. If you omit the sheet reference in a formula, it will adjust to refer to the current sheet, wherever it is copied.
Comments