Why don't named table columns show up in the Name Box?

[JenniferMurphy]

I am finally getting around to learning how to work with tables. They definitely have some powerful features, but a few curiosities. Here's one.

Suppose I select all of the cells in one column within Table1 and give them the name "Sum". Excel shows it in the Name Manager as "Table1[Sum]". But if I exit Name Manager, with those same cells selected, that name does not appear in the Name Box. Why is that? Or am I doing something wrong?

 

[RoryA]

I assume you mean that it does not appear in the name box just from having the cells selected, not that it doesn't appear in the dropdown list in the name box? If so, then that's just the way it is.

Out of curiosity, why name a table column when it effectively already is a dynamic named range?

 

[JenniferMurphy]

I assume you mean that it does not appear in the name box just from having the cells selected, not that it doesn't appear in the dropdown list in the name box? If so, then that's just the way it is.

As far as I can tell, it does not appear at all. I've attached a minisheet with a small table with some win percentages for several teams. In the table, column E has the formula =[@Wins]/[@Games]. No matter what range I have selected, the only name in the Name Box dropdown list is Table1.

Out of curiosity, why name a table column when it effectively already is a dynamic named range?

A couple of reasons. Mainly, it was because I hadn't gotten far enough into the Table tutorial to learn that the columns had table-related names based on the headers. That's pretty good. It will allow me to eliminate a lot of explicit naming.

But now that I've played with it a bit, I've found that several of my "tables" have multiple coliumns with the same header ("Sum", "%", etc.). When I convert them to tables, it cvhanges the second "Sum" header to "Sum2". This will force me to come up with unique headers, which means that they will be longer, which means that the columns will have to be wider, especially with the space taken up by the Sort buttons.

Here's that minisheet:

Book2
	B	C	D	E	F
5	Team	Wins	Games	%	Formula
6	A	10	12	83%	E6: =[@Wins]/[@Games]
7	B	7	13	54%	E7: =[@Wins]/[@Games]
8	C	11	14	79%	E8: =[@Wins]/[@Games]
9	D	3	12	25%	E9: =[@Wins]/[@Games]
10	E	9	13	69%	E10: =[@Wins]/[@Games]
Sheet1

Cell Formulas
Range	Formula
E6:E10	E6	=[@Wins]/[@Games]

 

[RoryA]

If you can't see the name in the name box at all, that would suggest that you didn't actually define the name. Do you see it in the Name Manager?

 

[JenniferMurphy]

If you can't see the name in the name box at all, that would suggest that you didn't actually define the name. Do you see it in the Name Manager?

Now I am confused. Are you talking about the column names generated by Excel as part of the table? Or ranges that I manually name? Ranges that I manually name do show up in the Name Box when selected and in the Name Box dropdown list and they are listed in Name Manager. Names assigned by Excel for this table do not appear in Name Manager or in the Name Box or in the Name Box dropdown whether they are selected or not.

Here's a Mini Sheet of a simplified table with named columns both inside and outside the table.

Named ranges in tables.xlsx
	A	B	C	D	E	F	G
1
2	temp	Team	Wins	Games	%1	%2
3		A	10	12	83%	83%
4		B	7	13	54%	54%
5		C	11	14	79%	79%
6		D	3	12	25%	25%
7		E	9	13	69%	69%
8
9
Sheet1

Cell Formulas
Range	Formula
E3:E7	E3	=[@Wins]/[@Games]
F3:F7	F3	=WinsM/GamesM

Named Ranges
Name	Refers To	Cells
WinsM	=Table1[Wins]	E3, F3:F7

Here are some examples of what seem like oddities or inconsistencies.

1. None of the named dynamic ranges within the table are listed in the Name Manager. Here's a scheen shot of the Name Manager. The "M" suffix is for "Manual", as in a manual copy of a dynamic table range.

I defined the WinsM range as C3:C7, but Excel changed it to Table1[Wins].

2. None of the named dynamic ranges within the table appear in the Name Box when selected. Here are some screen shots of the Name Box with various named ranges selected. On the left, when the range "Temp", which is outside the table, is selected, its name appears in the name box. In the middle, when the range Table1[Wins] is selected, no name appears in the Name Box. On the right, when the range "GamesM", which includes the Games range inside the table plus 1 cell above and 1 below, its name appears in the Name Box.

3. None of the named dynamic ranges appear in the Name Box dropdown whether they are selected or not. Here are scheen shots of the Name Box dropdown with different ranges selected. On the left, the GamesM range that I manually named is selected. It appears in the Name Box dropdown and is highlighted with a blue rectangle. On the right, the WinsM range that I manually named is selected. It does appear in the dropdown, but is not highlighted and it does not appear in the Name Box. And this range is the same as Table1[Wins], which appears nowhere.

4. The little sort/filter icon for the Wins column always shows a little arrow. Why just this one column?

In case anyone wants to fiddle with the actual workbook, I uploaded it to this OneDrive folder:

Table on OneDrive

PS: Here are a few more bugs in xl2bb:

1. It does not show the sort/filter dropdown icons on table headers.
2. It displays the formulas in F3:F7 as "=WinsM/GamesM", when they are actually "=@WinsM/@GamesM".
3. Under Named Ranges, it only lists one, "WinsM", which it shows as "E3, F3:F7". The "F3:F7" is correct. (It's actually "$F$3:$F$7".) I have no idea where the "E3" came from.
4. It fails to include "GamesM", "Table1", and "Temp", even though all of them are included in selected range when the Mini Sheet was generated.

 

[RoryA]

Suppose I select all of the cells in one column within Table1 and give them the name "Sum"

Now I am confused. Are you talking about the column names generated by Excel as part of the table?

I’m talking about what you said in your initial post (first quote above). That seemed to imply you had named the range.
Table columns do not appear as named ranges. If you want to know why, you‘d probably have to ask Microsoft.