Excel Formula Won't Copy Down to Other Rows

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 (in current versions of Excel). This is a reference to all the rows in the worksheet.

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 will automatically attempt to increase the row references.

Therefore, when a cell containing the reference 1:1048576 is copied from row 1 to row 2 of a spreadsheet, Excel will attempt to update this reference to 2:1048577.

However, as the row number 1048577 does not exist, this is an invalid reference, which causes the #REF! error to be generated.

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

Results:   A 1 6 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: An even better fix to the above example would be to just reference the cells you need (e.g. $A$1:$D$100). However, you should still ensure that you use the $ signs for references that you want 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.