Names tutorial H: Useful conventions

1	One way is to use the Insert \| Name \| Create command that we have just learned about.

If we highlight cells A1:G13, and use the Insert | Name | Create command specifying that the names are to be taken from the labels in the left column, we will give the name Revenue to row 3, and Costs to row 9. But those lines have no numbers on them, while the lines that do have numbers remain unnamed. So that's not much good.

2	If we highlight cells B1:G13, and use Insert \| Name \| Create, Excel will complain.

The reason is that Excel works down the selected rows, allocating the name dog to row 4, cat to row 5, fish to row 6 and parrot to row 7. Then it gets to row 10, and finds that it is being asked to allocate the the name dog again. At this point it pauses for instructions.

Depending on the answer to Excel's question, the name dog will be associated with rows 4 or 10; but not both. One line will go unnamed. The same is true for the other three names on offer.

3	We could alter the labels in columns A and B so that contained some unique names for each of the rows. But to those who think that they do a good job of communicating the intent of the spreadsheet as they are, this would be to compromise their readability.

Given that we are not having much luck with Insert | Name | Create, we could use Insert | Name | Define. We could highlight cells C4:G4 first, then invoke that command. Excel would propose dog as the name, but we could overtype that with something like SalesOfDogs. This would work, but require that we select each of the eight rows one at a time, and go through the naming process separately for each of them.

One recommendable way forward is to put the intended names down the right side of the spreadsheet.

Then we can select cells C4:H13 (the numbers plus the labels on the right) and use Insert | Name | Create, this time in the right column.

By devoting the right side of the spreadsheet to names, we attain a layout that is self documenting (what a row is called is written at its right side) and allows the names to be created in a single action. At the same time the left side can be arranged to make the purpose of the spreadsheet clear to human readers, without any of the compromise that would be needed if the two functions were merged, as they were in steps 1 and 2 above.

Remember those shortcuts we learned early on? F5 is Goto; F3 is for pasting names into a formula more accurately than one can type them. And we've also met Apply names. Every one of these commands lists the names in a workbook in alphabetical order. The convention Operis follows is that names are chosen so that related items are near each other in these sorted lists. So here, we have four kinds of Costs, and by calling them CostsDogs, CostsCats and so on, we ensure that they are to be found by each other when presented alphabetically. The noun (Revenue or Costs) goes first, then the qualifier (Dogs, Cats etc). It's a bit like the French preference to put adjectives after nouns (La Maison Blanche) in contrast with the opposite order in English (The White House).

Whether you follow this convention or another, the valuable thing is that you have one, and that it is followed by all members of a team that has to work on each other's spreadsheets.

Don't force these records of the names used to do double duty as the labels that make the spreadsheet intelligible to humans. Separate the roles. One way to do this, but not the only one, is to

•	put the labels down the left side, or occasionally along the top, of the values being identified

•	record the names down the right side of the spreadsheet, or occasionally below, the areas that have been named

Use Insert | Name | Create to add to the workbook names in bulk, in a way that ensures that they match what is documented.

Choose names to that related items sort next to each other in an alphabetic listing.