Names tutorial F: Names that identify several cells

1	To get started with this lesson, load the example spreadsheet Names Tutorial F Multi cell names.xls.

This spreadsheet is different from the examples earlier in this tutorial, in that rather than having a single cell for Sales, and another one for Costs, we have five years of them. The sales are calculated by marking up the costs by 20%.

3	Use Excel's Insert Names Define command. As it did in Lesson B with single cells, a dialog box will appear, which Excel will have prepopulated with

•	the address of the selected cells (the five Costs figures)in the lower text box, the RefersTo field

•	the word Costs in the upper text box, the Name field.

Excel offers the word Costs because it is located in a cell near to the selected cells and appears to be a plausible candidate for the name we'd like now to create. But Excel selects the name, so that if its choice isn't what we had in mind, we can conveniently overtype it.

4	In this case, Costs will do fine as a name, so we can accept Excel's proposal and dismiss the dialog by clicking OK or pressing Enter.

5	Examine the bottom line, which calculates what we have to charge for our product if we are to add a 20% markup to the costs. See that it does so using a simple formula expressed in coordinates.

6	We carefully introduced Excel's Apply names command in Lesson E, because seeing what it does is the best way to understand how Excel's names work. Try using it now.

•	Click on any one cell. (This is necessary because Apply names follows the One-cell rule. It won't do anything useful if we leave the selection at the five costs numbers, since they don't contain any formulas that Apply name might act on.)

•	Activate Excel's Insert \| Name \| Apply command.

•	In the dialog that appears, select the entry for the name we have created, Costs.

•	Dismiss the dialog by clicking OK or pressing Enter.

7	Now examine the bottom line, and see that the Sales calculation has changed so that it now reads =Costs*120%

The first of these Sales formulas is in cell B7. When Excel needs to evaluate the formula in that cell

•	it looks up the name Costs, and sees that it is defined as $B$3:$F$3

•	It substitutes this reference in place of the name, making the formula = $B$3:$F$3*120%

But now Excel has a problem. It is trying to evaluate one formula, B7, but it has been given five cells, B3:F3. What is it to do? We've no hope of fitting the five numbers into one cell.

Many spreadsheet programs would react to this situation by showing an error (though that's changing*). Excel does something more useful. It solves the problem that it has more inputs than are needed for a single-cell result by doing something pragmatic: it makes a choice of one of the cells from among the ones specified.

The rule used to make this choice is is simple: Excel picks out the cell that is in the same column as the calculation. Our calculation is in cell B7; so of the five cells, B3:F3, the one that it will involve in this calculation is B3.

Subsequent Sales formulas, in cells C7, D7, etc, do the same thing; Excel matches columns so that their formulas act on cells C3, D3, and so on.

* Open Office, Gnumeric, Google spreadsheets and the latest version of MS-Works now all support coordinate matching, no doubt to achieve strong compatibility with Excel. The major package that does not is the once dominant Lotus 1-2-3.