Reference styles


Introduction


A reference style is determined by the address of the home cell (the top left cell in a worksheet). Excel has two styles, the A1 reference style and the R1C1 reference style. You can download the workbook for this module from the link on the figure 6 Excel Web App #1.


A1 reference style



R1C1 reference style


xlf-reference-home-cell
Fig 1: Excel reference styles - left panel: A1 style, right panel: R1C1 style

Switching styles


To change styles, use the ribbon sequence Home > Options > Formulas > Working with formulas, then check / uncheck the R1C1 reference style box.


Cell references


Relative reference


The type of cell reference is important when:


xlf-relative-a1
Fig 2: Relative reference A1 style - formula cell D7, shown in edit mode

  1. The formula in cell Relative!D7 \( \left( 1+r \right)^{-t}\) has an exponent value from cell B7. D7: =(1+5%)^-B7
  2. Relative to D7, the cell B7 is in the same row and 2 columns to the left
  3. In R1C1 style, the formula in cell D7 is =(1+5%)^-RC[-2]. In the same row R and 2 columns left C[-2]
  4. When the D7 formula is copied down the column, the cell D8 formula is =(1+5%)^-B8
  5. In R1C1 style, the formula in cell D8 is =(1+5%)^-RC[-2]. The same as D7
  6. An example of R1C1 relative addressing is shown in figure 3

xlf-r1c1-circle
Fig 3: Relative reference R1C1 style - showing references of cells adjacent to the formula cell

Absolute reference


The ABC Farming Co carrot washer example is continued. This time the discount rate is assigned to a cell (Absolute!D5), rather than being entered as a formula constant.


xlf-absolute-a1
Fig 4: Absolute reference A1 style - used for the discount rate reference of the formula
  1. The formula in cell Absolute!D8 has an exponent value from cell B8 (relative), but the discount rate is an absolute reference to cell D5 as shown by the $ signs. =(1+$D$5)^-B8
  2. When the D8 formula is copied, the discount reference will always refer to the cell D5
  3. In R1C1 style, the formula in cell D8 is =(1+R5C4)^-RC[-2]
  4. When the D8 formula is copied down the column, the cell D9 formula is =(1+$D$5)^-B9
  5. In R1C1 style, the formula in cell D9 is =(1+R5C4)^-RC[-2]. The same as D8

Mixed reference


The discounted cash flow theme is continued. This example constructs a table of discount rate for present value (PV). A framework is shown in figure 5, and Excel Web App #1 worksheet Mixed. The idea is to enter one formula in cell C4, and then copy this formula to the range C4:L24.


xlf-mixed-a1
Fig 5: Mixed reference A1 style - used in the construction of a two dimensional array of discount factors
  1. The formula in cell Mixed!C4 has an exponent value reference to cell $B4 (absolute column and relative row, a mixed reference), and the discount rate reference to cell C$3 (relative column and absolute row, a mixed reference). C4 =1/(1+C$3)^$B4
  2. When the C4 formula is copied D4, the discount reference will always refer to the cell in row 3 and the same column as the formula. The period reference will always refer to the cell in column B and the same row as the formula
  3. When the C4 formula is copied C5, the discount reference will always refer to the cell in row 3 and the same column as the formula. The period reference will always refer to the cell in column B and the same row as the formula
  4. In R1C1 style, the formula in cell C4 is =1/(1+R3C)^RC2. When copied this same formula will be in every cell of the body of the Table of Discount Factors

Reference shortcuts



Fig 6: Excel Web App #1 - reference examples and discussion on 6 worksheets