Labels in Excel

[NSoulBri]

Can anyone explain the point of labels in excel.

I fully understand the point of naming ranges and constants etc but fail to understand why you would want to label a range of cells when Excel already provides labels automatically via the placement of row and column headings.

To explain my point I've put together a simple example

A B C D
1 Jan Feb Mar Apr
2 10 56 2 15
3 45 47 12 8
4 78 45 56 79
5 =SUM(Jan)

In the above example, Cell A5 will correctly calculate 10+45+78. This is because the system reads column A as being called 'Jan'. B is called 'Feb', C is Called 'Mar' and so on.

Therefore, my question is: what is the point of the "Insert/Name/Label" command in Excel 97?

Thankyou

Bri

 

[Dreamboat]

Actually, there are many other reasons to name ranges. For instance, at a certain point, I may want to clear all of the cells in columns A, D, and E using VBA. What easier way than to select those non-contiguous cells and call them DeleteMe?

Also, not everyone's files are set perfectly. I may want to name a range of several columns, as I did in our MyVlookup at:

http://www.thewordexpert.com/tipwarez.htm

http://www.TheWordExpert.com

 

[NSoulBri]

I appreciate your speedy response Dreamboat but I would use the Insert/Name/Create... command to do what you propose, not the Insert/Name/Label... command Bri

"Soul music is for life, not just christmas"

 

[Dreamboat]

Ah. Sorry, Bri. I read "name", not "label". I see that the words have changed. LOL (just kidding)

http://www.TheWordExpert.com

 

[JVFriederick]

Let's see if this helps.

Say you have a table with 30 or more headings (columns / fields). Instead of having months, which are easy to remember, you have instead EndingDate, ClassDescription, TimeTable, Company, etc.....

Trying to remember perfectly how something was spelled can be a hassle.

After using Insert/Name/Label, which creates a range name using the headings, you can use each field more easily in a formula (by using the F3 when creating it) instead of relying on the column address.

Or so the theory goes.

One such application might be employees in the first column, various pay codes (which tend to go on forever) as headings, and hours worked within the matrix. Trying to write a formula for "Pay but Not Worked" hours can be tough without being able to "see" each of the elements.

 

[NSoulBri]

Thanks again, it's nice to get such a quick response, this is more like chat than forum init?

However, I'm still confused, or maybe you are, or maybe we both are. afterall we are talking about MS here.

Can you explain why I would do Insert/Name/Label... instead of Insert/Name/Create...

I've downloaded the VLookup you referred to but that was simply a case of labeling a cell "BonusPCT" and thereafter using the label in formulas etc. (I have no problem with that, thats childs play) but what about the Insert/Name/Label.. command???

If I appear to be thick then thats probably because I am and seem unable to ask my question properly.

Okay, It's your turn now.

Bri

"Soul music is for life, not just christmas"

 

[NSoulBri]

Thanks to JVFriederick for your comments

The method you appear to suggest is Insert/Name/Create... which I fully understand and use frequently.

This is my interpretation of your suggestion. Please correct me if I'm wrong.

Column headings in row 1 are EndingDate, ClassDescription, TimeTable, Company, StudentQTY

Each column is populated with apprporiate values.

I want to see a total of StudentQTY so I select the range of cells E1:E25 then go Insert/Name/Create... and click the Top Row option.

If as an alternative I did Insert/Name/Label... What would I do at the Label Ranges dialog box?
Bri

"Soul music is for life, not just christmas"

 

[JVFriederick]

As I remember, using Insert/Name/Label creates ALL the ranges at ONE time without typing a single label. A few seconds and you have created 30 range names. Not bad.

Yes, you can create them individually, but .......

To get a total for the column you mentioned,
=SUM(StudentQTY )

I forget the actual key referrences to using Insert/Name/Label, but play around on a test sheet and see what happens.

 

[NSoulBri]

To best try to illustrate my point, Please follow these steps. (It will take you about 2 minutes)

Create a new worksheet

Go to Tools/Options... and on the Calculation tab check the 'Accept Labels In Formulas option and click OK.

Enter Info into the spreadsheet like this:

Row 1 contains Word, Excel, Powerpoint, Access, Outlook
Column A contains Men, women, Children, Total Users

Populate the matrix with numeric values that represent number of users per application

in Cell B5 type the formula =Sum(Word) and press enter.

Do the same for cells C5, D5 etc

you will now see that you have not had to use the Insert/Name/Label... command Bri

"Soul music is for life, not just christmas"

 

[SkipVought]

Hey Soul,

I have never used the Label feature. In fact, I never knew it even existed. You can learn ALOT at a forum like this!

What I did find out, via Help, is that Lables can be STACKED, unlike Names. See the following snippet...

About using multiple labels in formulas
Stacked labels   When you have labels for the columns and rows on your worksheet, you can use those labels to create formulas that refer to data on the worksheet. If your worksheet contains stacked column labels — in which a label in one cell is followed by one or more labels below it — you can use the stacked labels in formulas to refer to data on the worksheet. For example, if the label West is in cell E5 and the label Projected is in cell E6, the formula =SUM(West Projected) returns the total value for the West Projected column.

The order for stacked labels   When you refer to information by using stacked labels, you refer to the information in the order in which the labels appear, from top to bottom. If the label West is in cell F5 and the label Actual is in cell F6, you can refer to the actual figures for West by using West Actual in a formula. For example, to calculate the average of the actual figures for West, use the formula =AVERAGE(West Actual).

So it look like a nuance on Names for the people who are heavy into non-normalized data structures, which i do not encourage.


Skip,
metzgsk@voughtaircraft.com

 

[NSoulBri]

I thank you for your response Skip but once again the example you have given does not require you to use Insert/Name/Label...

the stacked column headings will be detected automatically

If you create a stacked heading, put some values in that column and then do =Sum(Projected West) then as long as you have enabled the "Allow Labels in Formulas" option on the calculation tab of Tools/Options then it will work. Trust me, I'm a doctor Bri

"Soul music is for life, not just christmas"

 

[SkipVought]

Geez,

Now you've got me wonderin' what Insert/Name/Label REALLY does!!! I won't be able to sleep tonite unless I find out! :-( Skip,
metzgsk@voughtaircraft.com

 

[NSoulBri]

Good Morning Skip

Did you sleep well? (If at all)

In a way I'm glad I've caused you all a problem with this because it's been buggin' me for ages and I thought maybe I should give up training as a bad job because I don't understand the product I'm trying to teach to others.

Fully understanding Insert/Name/Label... is now my number 1 priority in life. Bri

"Soul music is for life, not just christmas"

 

[SkipVought]

Soul,

Slept like a baby!

I decided that there may be some other things that more significant for me to cogitate on. Skip,
metzgsk@voughtaircraft.com

 

[JVFriederick]

Amen