How can I copy a formula to an entire column on Numbers?

Hi botanez,

What Quinn says, with one variation:

Copy the cell containing the formula.

Select the whole column.

Command-click on the cell(s) in the Header row(s) to deselect them.

Paste.

The formula will paste into all of the selected rows, but not into the (unselected) header rows.

Regards,

Barry

Hi botanez,

You can copy the formula, select the column and paste. You will have have to reenter the column header label.

quinn

In the formula:

=D2*$E$2

D2 is relative address, which will be adjusted according to the relative offsets between source and destination cells when copied or filled.

E.g., if this formula is to be assigned to F2 and you want to fill it across column F, put the formula in F2, copy it, select column F and paste. Note that location of source cell matters if the formula contains relative addresses.

Regards,

H

Hi Botanez,

Hiroto has pointed out the probable culprit.

Your initial formula is =D2 * $E2$. As your formula does not include a tale name, it is safe to assume that it is in the same table as D2 and E2.

In your initial post, you indicated the formula would be filled down (or pasted into) an entire column. As all columns in a table will have the same number of rows, we can make an arbitrary assignment of column for the formula—it will work in the same manner, and produce the same error messages, no matter which column it is in (UNLESS it is in either column D or column E). For illustration purposes, I'll assume it's in column C.

The initial formula can be in the same row as the initial cell references, in the row above the one containing the referenced cells, or in a row one or more rows below the one containing the referenced cells. As the formula is pasted into the column, or filled down through the column, the cell reference to $E$2 will remain pointed at that specific cell due to the 'absolute reference' operators attached to the column and row references. The reference to D2 will change as the formula is filled down column C, increasing by one for each row the formula moves down the table (and decreasing by one for each row the formula moves up the table.

The error message you received: =#REF!*$E$2

usually means your formula is attempting to reference a cell that does not exist.

If your initial formula is in C2 (or any other cell in row 2—except D2 or E2) then the Dn reference will always match the row that the formula is in. Since the row must exist for the formula to be in it, the same row must also exist in column D, and the #REF! error will not occur.

If your initial formula is in C1 (with the same restrictions as above), then the Dn reference will always be to the cell in column D that is one row below the cell containing the formula. The #REF! error will occur only in the last row of the table (say C100, where the cell reference to column D will be to D101, a non-existant row in a 100 row table).

If your initial formula is in C3 (ditto), the Dn reference will always be to the cell in column D that is in the row one above the row containing the formula. The #REF! error would occur only if the formula were pasted into row 1, where ther is no "row above the formula."

Copying the formula will not cause the error message. Pasting it into another location will cause an error message IF the relative reference to cell D2 causes the formula to reference a non-existant cell located the same number of rows above or below the pasted formula as D2 was above or below the original location or located the same number of columns left or right of the pasted formula as D2 was left or right of the original location.

Apologies for the lengthy post (necessary to cover the conditions that could have created the error message). Had you provided information regarding the original location of the copied cell, this message could have bn MUCH shorter.

Regards,

Barry