# Specifying a Cell Reference in a Formula

Learn how to specify cell references in formulas and choose between absolute and relative cell references with Spread.NET's comprehensive guide.

## Content

Besides values, operators, and functions, a formula can contain references to values in other cells. For example, to find the sum of the values in two cells, the formula can refer to the cell coordinates by row and column. You can use an absolute cell reference (with the actual coordinates of the row and column) or a relative cell reference (with the coordinates relative to the current cell). You choose which type of cell reference for the sheet by using the [ReferenceStyle](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.ReferenceStyle.html) property. For details on the way to specify the reference style, refer to the [ReferenceStyle](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.ReferenceStyle.html) property of the [SheetView](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.html) class, and the [ReferenceStyle](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.Model.ReferenceStyle.html) enumeration.
If you have changed the cell reference style to a style that cannot represent the formula, the Spread component provides the formula with question marks as placeholders for cell references that cannot be represented.
The following table contains examples of valid formulas using references:

| **Function** | **Description** |
| -------- | ----------- |
| SUM(A1:A10) | Sums rows 1 through 10 in the first column |
| PI( )\*C6 | PI times the value in cell C6 |
| (A1 + B1) \* C1 | Adds the values in the first two cells and multiplies the result by the value in the third cell |
| IF(A1>5, A1\*2, A1\*3) | If the contents of cell A1 are greater than 5, then multiply the contents of cell A1 by 2, else multiply the contents of cell A1 by 3 |

If you have defined relative cell references used in a formula in cell B1 as RC[-1]+R[-1]C, the formula is interpreted as add the value in the cell to the left (A1) to the value in the cell above ("B0"). The component treats the value in the cell "B0" as an empty cell. If you change the cell reference style to the A1 style, the formula becomes A1+B?, because the A1 style cannot represent cell "B0". However, the component still evaluates the formula as it would using the R1C1 reference style.

> !type=note
> **Note:** Although most of Spread uses zero-based references to rows and columns, in the creation of formulas you must use one-based references. The column and row numbers start at one (1), not zero (0).

## Range Reference

Spread supports range references where the start row and end row consists of same reference types (either both row coordinates are absolute or both row coordinates are relative) and different reference types (one coordinate is absolute, other coordinate is relative and vice a versa).
The following table describes some examples of range references in R1C1 (Number-Number) notation:

| **Reference** | **Whether Supported** |
| --------- | ----------------- |
| R1C[-1]:R5C[-1] | supported (absolute row : absolute row) |
| R1C[-1]:RC[-1] | supported (absolute row : relative row) |
| RC[-1]:R5C[-1] | supported (relative row : absolute row) |
| R[-5]C[-1]:RC[-1] | supported (relative row : relative row) |

The following table describes some examples of range references with different reference types in A1 (Letter-Number) notation:

| **Reference** | **Whether Supported** |
| --------- | ----------------- |
| A$1:$B1 | supported (cell range starting with relative column plus one, absolute first row and ending with absolute second column, relative row plus one) |
| $A1:B$1 | supported (cell range starting with absolute first column, relative row plus one and ending with relative column plus one, absolute first row) |
| A$1:B$1 | supported (cell range starting with relative column plus one, absolute first row and ending with relative row plus one, absolute first column) |

The following table describes some examples of special usage cases that are supported in range references in Spread:

| **Reference** | **Whether Supported** |
| --------- | ----------------- |
| $A:$B<br>A:B<br>C1:C2<br>C[-2]:C[-1] | supported (refers to whole column) |
| $1:$2<br>1:2<br>R1:R2<br>R[-5]:R[-4] | supported (refers to whole row) |

For more information on range reference styles, refer to the [Formula Reference](https://developer.mescius.com/spreadnet/docs/latest/online-formula/overview.html).

## Using the Properties Window

1. At design time, in the **Properties** window select the **Sheets** property and click on the button to open the **SheetView Collection** editor.
2. Select the sheet from the Member list.
3. In the properties list (in the **Calculation** category), select the **ReferenceStyle** property.
4. Click the drop-down arrow to display the choices and select the value, either **A1** or **R1C1**.

## Using Code

Specify the reference style by setting the [ReferenceStyle](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.ReferenceStyle.html) property or use the default ReferenceStyle value.

## Example

This example sets the reference style.

```csharp
fpSpread1.Sheets[0].ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
fpSpread1.Sheets[0].Cells[2, 2].Formula = "SUM(A1:A6)";
```

```vbnet
fpSpread1.Sheets(0).ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
fpSpread1.Sheets(0).Cells(2, 2).Formula = "SUM(A1:A6)"
```

## Using the Spread Designer

1. Select the sheet tab name for the sheet.
2. In the property list (in the **Calculation** category), select the **ReferenceStyle** property.
3. Click the drop-down arrow to display the choices and select the value, either **A1** or **R1C1**.
4. From the **File** menu choose **Apply and Exit** to apply your changes to the component and exit Spread Designer.

## See Also

[Formulas in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula)
[Placing a Formula in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaplace)
[Specifying a Sheet Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulasheetref)
[Specifying an External Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-externalref)
[Using a Circular Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref)
[Nesting Functions in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulanested)
[Recalculating and Updating Formulas Automatically](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formularecalc)
[Finding a Value Using GoalSeek](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formula-goalseek)
[Allowing the User to Enter Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaallowuser)
[Creating and Using a Custom Name](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomname)
[Creating and Using a Custom Function](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomfunc)
[Creating and Using External Variable](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-external-variable)
[Using the Array Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-arrayformula)
[Working with the Formula Text Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulabar)
[Setting up the Name Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-namebox)
[Using Language Package](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/UsingLanguagePackage)
[Accessing Data from Header or Footer](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-headerfooterformula)
[Managing External Reference](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-extrenalreference)
[Working With Dynamic Array Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/WorkingWithDynamicArrayFormulas)