Excel Formula Won't Copy Down to Other Rows

This page addresses the following common 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, it returns 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 may indicate 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, Excel automatically inserts the reference 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 from row 1 to row 2 of a spreadsheet, the references will attempt to update to 2:1048577.

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

 Formulas:
 A
1=COUNTIF( Sheet1!1:1048576, B1 )
2=COUNTIF( Sheet1!#REF!, B2 )
 Results:
 A
16
2#REF!

Solution:

Check the formula in the first cell. 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 will 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.