Extend data range formats and formulas not working (like I'd expect)

Hi,

seeing you're on XL 2010, I'm not sure if this still applies, but on this page

http://support.microsoft.com/kb/231002

Microsoft has some explanations that were valid for XL2000. Ten years on, who knows, maybe it still applies.

I find that the problem does not present when using lists (xl 2003) or tables (xl 2007) instead of relying on the auto extend functionality

When Auto Extended Formatting May Not Work
Because Excel automatically applies Auto Extended Formatting, there are situations that may cause this feature to not work exactly as prescribed. The following list explains when the Auto Extend formatting may not work as expected:

* Formatting does not auto extend in columns (copy down) if data entered in a new cell is not similar to data in cells above. For example, if you have formatted cells as bold, align center, and shaded, in a column containing column or row headings (or any text) and then enter a numeric value at the end of that column in the list, the formats do not copy down.

* Formatting does not copy down if the new cell already has custom formatting applied, which can include shading or cell formatting such as currency or special date formatting.

* Excel does not update formatting in cells that have already been formatted with the Auto Extend feature when you change the cells above.

* Excel never copies down Data Validation settings.

* Hyperlink formatting is a special case. If, during the scan process, Excel detects that a subset of the cells being scanned contain hyperlinks, Excel ignores those cells. If all cells in the set of cells being scanned contain hyperlinks, the feature does not activate at all.

and

When Auto Extended Formulas May Not Work
Formulas copy down if four of the last five cells contain a similar formula and the cell above the active cell contains the formula that you want copied down. There are times when formulas may not extend. The following list contains examples of formulas that Excel will not copy down.

* Formulas that reference cells in other sheets or workbooks do not copy down, even if they also reference a cell in the list containing the active cell.

* Formulas in which all of the formula's references point to areas outside the list.

* Formulas that reference a dimensional horizontal range in which not all row references are relative, for example:
=SUM($A$1, A2, B1, B2)
Column references have no effect on the copy down trigger.

* A formula that uses a series reference, where none of the row references are relative, for example:
=SUM($A$1, $B$1)
Column references have no effect on the copy down trigger.

* Formulas containing a range name or natural language formula.

* Data that you paste into the new row.

Most Microsoft Excel functions also copy down, as long as they follow the basic guidelines outlined in this article.