Reference cells and cell ranges

Product:

Application: Table Web version

Referencing is linking to cells or ranges for further use in calculations. There are three types of references:

•Relative: This type of references changes when copying or auto-filling. That means that formula in every row or column will be recalculated using the new data. By default, all references in the editor are relative.

•Absolute: This type of references does not change when copying or auto-filling. Only the data to which a formula refers will be used in the calculations. Therefore, they should be used for formulas that have constant values, such as interest rates.

•Mixed: This type of references allows you to combine both absolute and relative references. For example, in the В$5, D$12 reference the row number remains the same, but the column name may change.

А1 cell reference style

In the A1 reference style, the cell address consists of a column letter name and a numeric row name. For example: A1, C12, G37, ND185 etc.

Relative reference

А1

Absolute references

$A$1 (the column and the row do not change when copying)

$A1 (the column does not change when copying)

A$1 (the row does not change when copying)

R1C1 reference style

In the R1C1 style, both columns and rows are denoted by numbers. The address of a cell is determined using the formula RnCm, where:

•R stands for row, and n stands for row number.

•C stands for column, аnd m stands for column number.

For example, the name of D5 cell in the R1C1 style is R5C4.

Absolute reference

R3C2

Relative references

R[3]C[2] (a cell is located 3 rows below and 2 columns to the right of the current cell)

R[-3]C[2] (a cell is located 3 rows above and 2 columns to the right of the current cell)

R3C[2] (a cell located on the third row of the sheet and two columns to the right of the

current cell)

If a spreadsheet contains absolute references when switching from A1 to R1C1 reference style, they will remain absolute afterwards.

Cell range reference

The reference to a cell range is defined by referencing its two corner cells:

•Starting cell: The upper-left cell of the range.

•End cell: The lower-right cell of the range.

The starting and end cells are separated by the (:) operator. For example: B4:D6.

In the desktop version of MyOffice application, users can assign a name to a range of cells, which can be used to insert into a formula instead of a reference with a starting and end cell.

Naming the ranges is only possible in the desktop version of MyOffice applications. This feature will be available for use in the next web versions of the application.

Referencing cells on other sheets

You can also refer to a range or cells on another sheet of the current spreadsheet. To do this, the link must look as follows:

'<sheet name>'!<range reference>

For example: 'Sheet1'!D12 or 'Sheet3'!D12:E16.

Was this helpful?

Yes