(Un)Natural language formulas

One of the very useful and powerful features of Excel is the ability to give a descriptive name to a range of cells.  This name can then be used in formulas in other cells, making them easier to read and understand.  However, it does take a few moments to define the names in the first place.

A feature in Excel actually circumvents the usual process of defining names by allowing you to use text labels adjacent to a range as an effective name for that range.  This option is called Natural language formulas, and was introduced in Excel 97. It has the apparent advantage of letting you use names without having to spend the time defining them in the first place.

However, Operis has found that in practice this option is potentially confusing and we strongly recommend you turn it off:

1.Select Excel | Tools | Options;

2.On the Calculation tab, uncheck the ‘Accept labels in formulas’ workbook option.

There are several reasons why this option is so dangerous:

•The results of formulas using text labels are unreliable, especially when you change the size of the range referred to by the label;

•Excel sometimes thinks a formula using a label is on a circular path when it is not;

•The labels are not actually Excel names and cannot be controlled by the user in the usual manner.

In fact Microsoft has itself thought better of this feature and removed it from Excel 2007.  From Deprecated features for Excel 2007:

Natural Language Formulas

This feature allowed people to use the labels of columns and rows on worksheets to refer to the cells adjacent to those cells without explicitly defining them as names.  This feature has been disabled by default since Excel 2000 based on customer feedback.  In Excel 12 we will completely remove this seldom-used feature from the product.  When opening files in Excel 2007 that use this feature, formulas will be converted to use cell references.

Note: This feature could be found in the Excel 11 options dialog with the title “Accept labels in formulas”

(Excel 11 is Excel 2003.  Excel 12 is Excel 2007.)

You can find some examples illustrating the dangers of Accept labels in formulas here.