A very un-useful “feature”

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.

This entry was posted in Excel and tagged , . Bookmark the permalink.

One Response to A very un-useful “feature”

  1. William says:

    Thanks for the solution. This ”Feature” really useless


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.