Spooky updating of formula!

[Jack_1812]

Can anybody explain this? I have a spreadsheet with a column of 18
numbers. In the 19th row, I have a formula to sum the first nine
cells. As I enter numbers in row 1 down to row 10 and below, the
formula magically changes to include the additional rows! In case it's
of relevance, the cells have conditional formatting to colour
background depending on value. Otherwise, nothing special about the
cells at all. I am mystified! This could be a useful function if I
knew how to create it again!!

 

[Guest]

Here's what it says in help...
Extend formats and formulas to additional rows
By default, Microsoft Excel automatically formats new data that you type at
the end of a range to match the preceding rows. Excel also automatically
copies down formulas that have been repeated in the preceding rows.

Note In order to be extended to new rows in the range, the formats and
formulas must appear in at least three of the five preceding rows.

You can turn this option off (or on) at any time:

On the Tools menu, click Options, and then click the Edit tab.
To turn automatic formatting off or on, clear or select the Extend data
range formats and formulas check box.