Revised: March 7, 2000

Additional 1-2-3 Topics

Absolute Reference - Locking in only one dimension

Use of a $ before the row letter and before the column number locks in both dimensions when the formula is copied.  However, sometimes one only needs or wants to lock in one dimension, while permitting the other to change.  An example would be a spreadsheet where one want to present the effect of varying an assumption, rather than printing out the spreadsheet after each change in assumption.  For example, consider a spreadsheet that calculates the future value of an investment over the next 10 years for a variety of interest rates.  Instead of having a single cell which shows the interest rate (as in the loan calculator example), one has a range of cells showing different values for the interest rate. In such a case, one or more of the formulas will use absolute references in only one dimension.  To do this, one uses the dollar sign in front of the dimension that remains absolute.  For example, $B1 when copied, will keep the column (B) the same, but allow the row to change.

Naming Ranges and Using Named Ranges in Formulas

Rather than referring to cells with letters and numbers, one can name cells (a single cell or a range of cells) with names.  Names can be up to 15 characters in length, starting with an alphabetic character.  Using names in spreadsheets is a matter of taste.  I never have used names in spreadsheets but this is more a result of never having gotten into the habit of using them, rather than thinking good or ill of them.  Let me describe what I see as their advantages and disadvantages, and you can decide for yourself.  Names have several advantages:

1) If one needs to enter a range of cells repeatedly, naming it and then using the name may be easier and less error-prone than explicitly entering the range each time.

2) Names can make your formulas more readable --e.g., your formula references PURCHASE_PRICE rather than $B$1.

3) Names can make debugging formulas easier -- if you see a reference in a formula to $B$1, you have to check what entity (e.g., purchase price) that refers to, whereas the name tells you right off

Names have several disadvantages:

1) Use of descriptive names means that the formula ends up being longer

2) In debugging formulas, one must make sure to verify that the names refer to the cells they purport to.  If one looks at a formula that references PURCHASE_PRICE, one must make sure that the name PURCHASE_PRICE refers to the appropriate cell.  (However, one only need to do this once rather than each time there is a reference to PURCHASE_PRICE).

3) Using a dollar sign before the name can make the reference absolute in both dimensions; I don't know how one does it for only one dimension.