A recent post in the Engineering Spreadsheets forum at Eng-Tips draws attention to a potentially dangerous Excel “feature”.
The feature is called “Extend data range formats and formulas” and can be found under Options-Advanced (click on the multi-coloured button in the top-left hand corner in Excel 2007, or the Tools menu in earlier versions). This is what the 2007 help says about it:
“Extend data range formats and formulas Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.”
Now making an exact (relative) copy of an existing formula when a new row of data is entered is one thing, but making automatic adjustments to existing formulas when data is entered in a blank cell is something else entirely (in fact I think it is downright rude), but this is what Excel does.
Try entering three numbers in cells A1 to C1, then the formula =SUM(A1:C1) in cell E1. If you now enter a number in cell D1 the formula will be changed to =SUM(A1:D1), without asking. If the number you enter is a zero the displayed value won’t even change, but the formula does.
If you enter text, the formula doesn’t change, and if you over-type the text with a number it still doesn’t change, but if you delete the text, then enter a number, it does automatically adjust.
If you enter the formula in cell F1 (leaving two blank cells), and enter data in cell D1, then cell E1, it will adjust for both, but if you enter data in cell E1, then D1, it adjusts for neither.
If you copy and paste into either cell either or both cells the formula does not adjust.
Formulas covering a column range act in much the same way. Formulas will extend without warning when numerical data is entered into a blank cell underneath a column range used in a formula.
The solution is simple; go into Options-Advanced and unselect the “Extend data range formats and formulas” feature.