Row and Column level Formulae

Row and column level formulae apply to all cells in a row or column. They use the standard formula syntax, but can reference adjacent cells in other rows and columns using '_' as a dummy co-ordinate.

For example...

=A_*2

...produces a column of values, each twice the value of the adjacent cell in column A.

This is usually better than placing a copy of the formula in each cell:

Easily understood and maintained: One copy of the formula, rather than many.
Reliable: Copy/paste, fill down and drag fill are all easy to get wrong.
Robust handling of new data: Automatically applies to new rows or columns of imported data.
Robust handling of deleted data: Automatically not applied to empty cells, so that you can delete data without causing an error.

Column Level Formula Example

If column A is filled with numbers, then column B could be filled with the square of each adjacent number:

Select column B by clicking on the column selector.
Enter the following column-level formula in the formula bar:
```
= A_^2
```
Note the reference to column A, using '_' as a dummy row ordinate. An asterisk appears in the column header for column B, to remind you that these values are coming from the column level formula.

Row Level Formula Example

Row level formulae also use '_', but as a dummy column ordinate. For example:

Select all of row 6 by clicking on the row selector.
Enter the following row-level formula in the formula bar:
```
= _5^3
```
This fills row 6 with the values from row 5, raised to the third power.

Using Headers

Use Header Rows and Columns to make Column and Row Level Formulae self-documenting.

For example, if you have a header row plus columns...

A "Price"
B "Quantity"
C "Total".

You could just give column C the formula:

= A_ * B_

...but that could be hard to interpret, especially as the spreadsheet grew larger. Instead, you can do this:

= #Price#_ * #Quantity#_

...and get the same effect.

Using headers has the added advantage that moving the price and quantity columns will not break your formulae.

In Cell Formulae

If this syntax is used in cell formula then one refers to the entire column. So =SUM(A_) in a cell will evaluate to the sum of all the numeric values in column A. (Note, that this formula will create a cycle if placed in a cell in column A.)

Comments