diff between defining a name and creating a name (Excel 2003)

Topic

New Reply

Hi all,

Can somebody please tell me what is the difference between the five different name operations in the Insert > Name menu ? There is:

Define
Paste
Create
Apply
Label

In particular I want to know more details the difference between defining a name and creating a name.

I see that when I select a single cell and then click Insert>Name>Define, Excel lets me define a name.

But when I click Insert>Name>Create, Excel opens a dialog box called “Create names in” (note that “Names” is plural; why plural?) and the four options it offers shows that it treats creating names as a whole row/column thing. Why is it a whole row/column thing? Further if I then select one of those four options, e.g., Top Row, it does not then go and ask me for a name for the row, or whatever. So what is going on?

tia

– avi

Reply | Quote

Replies

When using Insert | Name | Define, you specify the name for the selected range.

When using Insert | Name | Create, Excel takes the values in the top row and/or left column of the selected range and uses them as names for the ranges below/to the right. See the picture below.

Reply | Quote

If you have created named ranges, you can make Excel use them retroactively in formulas:
– Select the range with the formulas.
– Select Insert | Name | Apply…
– Select the names that you want to use, or deselect the names that you don’t want to use, then click OK.

Example: let’s say you have a formula

=SUM(A2:A11)

and later on, you name the range A2:A11 Sales.
If you select the cell with the formula, then select Insert | Name | Apply, and click OK (leaving Sales selected in the list of names), the formula will now read

=SUM(Sales)

Reply | Quote

When you’re creating a formula, you can select Insert | Name | Paste… and select a name. When you click Paste, the name will be inserted into the formula at the position of the insertion point.

Reply | Quote

Thanks for the responses.

What are Labels? How do you define them and use them?

Without knowing what Labels are for, I tried to use the Label operation/window to declare a label for a range, and all it let me do was click Add as long as the “Add label range” field still contained the original cell reference, e.g., J9:J19. But as soon as I tried to type a descriptive name in that field instead of the cell reference, and then click “Add”, Excel just told me “Reference is not valid”.

What I have managed to work out is as follows:

– The “Define Name” window has a more general function than the next three ops. “Define Name” is a bit of a misnomer, IMO; rather it should be called “Names Manager”? Since I see that you can both add and delete names there. Also I see the PrintPreview name is there. Surprise, surprise! So names are a bit like bookmarks in Word, and this window is like the Bookmark window.

But what do Labels have to do with Names? Are they two different mechanisms that just happened to have been clumped together under the same menu.
Not that I have succeeded in creating a text label yet, but I have created names.

What’s going on?

Tia,

– avi

Reply | Quote

If you want a name manager, try Jan Karel Pieterse’s Name Manager. It’s much better than the Insert | Name | Define dialog.

I find it hard to remember what Insert | Name | Label does, I always forget, so I never use it. I use either Insert | Name | Define or Insert | Name | Create instead. Perhaps someone else can explain why Insert | Name | Label might be worth using.

Reply | Quote

I use them as you, Hans, Names with Define or Create. If I remember correct, a short search says so also, Label was introduced with Excel 97. User friendliness was the Big Thing, I guess, Office Assistant and all that.

I don’t use labels since they don’t work outside the worksheet, names as you very well know does but can get in trouble, but I will take that any day before labels. And one really has to trust that Excel interpret exactly what one want; since it looks around for something to put a label on (and it could be the row instead of the column or vice versa).

Our Woody wasn’t so impressed at the time, this “natural language”, nothing wrong with a good name, eh, label, but here it is Excel is doing it. Range Names and Labels in Excel 97

At default settings Labels are not used. One enables it for that book in Tools | Options | Calculation tab | Accept labels … After that it’s possible to write =SUM(East) directly and get 395, in your example.

If you update a table header from: North, East etc to East, North etc. the label you once used gets updated in the formula from =SUM(East) to =SUM(North), that’s nifty. But perhaps that’s all; or maybe get a value in a “plain English way” … say rows are Alpha, Bravo, Charlie, Delta, then =Bravo East, would result in 92, from your table. The intersection.

Yes, it can use numbers also, compared to names. If you have a table with top row columns for years, such as 2004, 2005 instead of North, East; Create name will not touch the top row even if one check the non-selected “Top row” check box.

With Labels: select top row and create a label range with: Insert | Name | Label and clicking Add (column labels selected). Then one can use for ex. =SUM(2006) to sum the 2006 column. Though, in case of a date Excel puts single quotation around it and updates the formula to =SUM(‘2006’).

But since they also are not easy to track I don’t like labels, and therefore don’t know so much either.

Hans, just stumbled on that “C” is not a valid name; as “A”, “B”, “C” etc. was going to ask, but found it’s the same as “R”, shorthand for Column and Row. Shows I usually don’t use that short names.

Reply | Quote

> Hans, just stumbled on that “C” is not a valid name.

This is language-dependent. In Dutch, “K” is not valid (the Dutch word for column starts with a K), and in German, “Z” is not valid (“Zeile”), etc.

Reply | Quote

Mmm, thanks. Many things that are forbidden in Excel, certain sheet names (uhm; “history”, for example, well not forbidden but reserved) etc.

Should perhaps have thought about it, but your explanation also explains that(!): in Swedish the word column starts with a K (row is the same, it starts with “R”: “rad”), can’t check now, no Swedish Excel around, but perhaps K also “sind verboten”. And thus I didn’t stumble on “C” before.

Even though I have used English Excel some time the functions is sometimes hard to find.

Reply | Quote

In English R is not valid either. I would presume that the equivalent of R and C for the cell designation type (“RC”) in whatever language is an invalid name. Also R1 – R65536 and C1 – C256 are invalid names in English all for the same reason and presumably equivalent names in other languages will be invalid. Even those designation for the rows and columns followed by letters are invalid (eg “R1a”).

Steve

Reply | Quote

Anything that resembles a cell address in A1 notation or in the localized version of R1C1 notation is not valid as a range name.

Reply | Quote

Labels enable you to use the labels above and to the left of tables as if they were defined as names. I advise against using that, because if you have tables with the same headings you might get confused what table the label refers to.

Reply | Quote

>> Labels enable you to use the labels above and to the left of tables

what type of tables? pivot tables? I never learned about those yet.

if you mean just the ABCD and 1234 above and to left, then I don’t understand what that adds coz you can use those as cell refs anyway.

tnx,

– avi

Reply | Quote

No, just a table in cells, with headings to the left and at the top of the table. post 736,633 in this thread describes it well.

Reply | Quote

amakeler,
“But as soon as I tried to type a descriptive name in that field”
You don’t define names in that dialog; you add a range and Excel “goes hunting” for suitable labels.

Labels in formula must be enabled, Tools | Options | Calculation tab. | Accept labels …

Yes, with Define you can add, delete and change names, and if one like, all that can be called “Define”.

For a “quick and dirty”-Define name, you can use the Name Box, left to formula bar. You will also find defined names in the Name Box dropdown for navigation, though not certain names as Dynamic named ranges, though it can be used to find such.

As for Create name, the dialog will also warn if you are trying to create a name already in use; Define name doesn’t do that, but then again its purpose is different. And with Create you can create names for several cells in one click.

I find names easier to work with. See also my reply to Hans.

If you like, see help at Office Online at MSFT (as compared to Offöine), for example: Define named cell references or ranges. And: about.com Using Labels in Excel Formulas and Functions

Reply | Quote