Microsoft Office Tutorials and References
In Depth Information
And, not surprisingly, if you've defined a name for A1:A20 (such as Sales ), you can use the name in place of the
For more information about defining and using names, refer to Chapter 3.
Full-column or full-row as arguments
In some cases, you may find it useful to use an entire column or row as an argument. For example, the follow-
ing formula sums all values in column B:
Using full-column and full-row references is particularly useful if the range that you're summing changes — if
you continually add new sales figures, for instance. If you do use an entire row or column, just make sure that
the row or column doesn't contain extraneous information that you don't want to include in the sum.
And, make sure your formula isn't in the column that's being referenced. If the SUM formula above is in
column B, it will generate a circular reference error.
You may think that using such a large range (a column consists of 1,048,576 cells) might slow down calculation
time. Not true. Excel keeps track of the last-used row and last-used column and does not use cells beyond them
when computing a formula result that references an entire column or row.
Literal values as arguments
A literal argument refers to a value or text string that you enter directly. For example, the SQRT function,
which calculates the square root of a number, takes one argument. In the following example, the formula uses a
literal value for the function's argument:
Using a literal argument with a simple function like this one usually defeats the purpose of using a formula.
This formula always returns the same value, so you could just as easily replace it with the value 15. You may
want to make an exception to this rule in the interest of clarity. For example, you may want to make it perfectly
clear that the value in the cell is the square root of 225.
Using literal arguments makes more sense with formulas that use more than one argument. For example, the
LEFT function (which takes two arguments) returns characters from the beginning of its first argument; the
second argument specifies the number of characters. If cell A1 contains the text Budget , the following formula
returns the first three letters (Bud):