Addressing cells and ranges

1.Click the View tab.

2.Select or clear the R1C1 Reference Style check box.

This is the default style in which:

–Columns are labeled with letters (A, B, C, ...).

–Rows are labeled with numbers (1, 2, 3, ...).

–The cell address is formed as a combination of the column letter and the row number.

Examples

A1, C12, G37, ND185.

Types of A1 style references

Cell references in formulas can be of three types.

Relative (e.g., A1):

–Changed when you copy or autocomplete a formula.

–They are used by default.

Absolute (e.g., $A$1):

–Do not change when copying. Fixes the column and/or row.

–Used for constants (e.g., interest rate).

–Absolute reference options: $A$1 — fixes both column and row; A$1 — fixes row only; $A1 — fixes column only.

Mixed (e.g., A$1 or $A1):

–Combine absolute and relative addressing, as shown in the examples above.

In this style, both rows and columns are labeled with numbers.

The cell address is formed as R<n>C<m>, where:

–R — Row, n — row number.

–C — column (Column), m — column number.

Example

Cell D5 in style A1 will be called R5C4 in style R1C1.

Reference types in R1C1 style

Absolute reference: R5C4 (reference to the cell in row 5, column 4).

Relative references:

–R[5]C[4] — 5 rows below and 4 columns to the right of the current cell.

–R[-3]C[2] — 3 rows above and 2 columns to the right of the current cell.

–R3C[2] — 3 rows above and 2 columns to the right of the current cell.

When switching the link style, the link type (relative/absolute) is preserved.

A reference to a range is formed by its opposite corners:

–Starting cell — upper left corner.

–End cell — lower right corner.

Cells in the reference are separated by the : (colon) operator.

Example

B4:D6 — range from cell B4 to D6.

To reference a cell or range from another sheet in the current book, the following format is used:

'<sheet_name>'!<address>.

Examples

–'Sheet1'!D12 is cell D12 on sheet 'Sheet1'.

–'Sheet3'!D12:E16 — range D12:E16 on sheet 'Sheet3'.