Excel Formula Won't Copy Down to Other Rows

Have you ever encountered this Excel problem ...

... You use a formula in one cell and it works perfectly. However, when you attempt to copy the formula down to other rows, you get the #REF error.

Possible Reason:

The #REF! error arises when Excel is attempting to reference an invalid cell.

If you are copying a formula that works in row 1, down to other rows in a spreadsheet, the #REF! error suggests that your formula contains relative references where absolute references should have been used.

For example, if you automatically insert a reference to an entire worksheet into a formula, by clicking on the grey square at the top left of the worksheet, the reference to the worksheet is 1:1048576 (or 1:65536 in Excel 2003).

As the reference 1:1048576 is a relative reference, when you copy the formula containing this reference down into other rows of your spreadsheet, Excel automatically increases the row references.

Therefore, when a cell containing the reference 1:1048576 is copied down to the next row the references would generally increase by 1 (i.e. to 2:1048577).

However, as the row number 1048577 does not exist, this causes the #REF! error to be generated instead.

1=COUNTIF( Sheet1!1:1048576, B1 )
2=COUNTIF( Sheet1!#REF!, B2 )


Check the formula in the first cell, that works. If you are referencing a range of cells that would result in invalid cell references as the formula is copied to other cells, then you need to adjust this cell reference in one of the following ways:

Reference Style 1

Add the dollar symbols to change the reference to an absolute reference (e.g. change the reference 1:1048576 to $1:$1048576).

The $ signs in the above reference prevent Excel from adjusting the row references as the formula is copied to other rows - read more about this on the Excel Cell References page.

Note: It is not really advisable to reference an entire worksheet's cell range in your formulas as this can slow down your calculations. It is more efficient to only reference the cells you need (e.g. $A$1:$D$100). However, you should ensure that you use the $ signs for references that are to remain fixed when copied to other spreadsheet locations.

Reference Style 2

Use a named range in your function. By default, Excel creates named ranges as absolute references. For details of how to define a named range in Excel, see the Excel Named Ranges page.