Is there a better way to format table headers?

[JenniferMurphy]

I often have difficulty getting table headers to display the way I want them to. Here's an example of a table the way I would like it to look.

This is how it looks if I don't use one of the "solutions" discussed below. If I turn Wrap on, Excel breaks the headers on certain characters (space, minus, ???).

I have found 2 ways to get it to look like I want, but both have problems.

1. Add extra spaces where I want the line to break. This is what I did in the example, above. See first minisheet below. This has two problems.
   1. If I change the column width, I have to adjust the number of extra spaces to match.
   2. Any formulas referencing these columns show the extra spaces.
2. Add a manual line break (Alt+Enter). This is independent of the column width, so I don't have to adjust the header text, but the formulas are even crazier. They cannot be displayed on a single line in the formula bar. See second minisheet below.

Is there a better way?

This table uses the extra spaces. Notice the spaced out formulas (K6:N6).

Weighted Ratings Demo.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Mean	3.00	3.00	15.00	8.00
4	Std Dev	1.58	1.58	7.91	1.58
5		A	B	C	D	A Z	B Z	C Z	D Z	A 0-100	B 0-100	C 0-100	D 0-100
6		5	1	25	10	+1.26	-1.26	+1.26	+1.26	100	0	100	100
7		4	2	20	9	+0.63	-0.63	+0.63	+0.63	75	25	75	75
8		3	3	15	8	=0.00	=0.00	=0.00	=0.00	50	50	50	50
9		2	4	10	7	-0.63	+0.63	-0.63	-0.63	25	75	25	25
10		1	5	5	6	-1.26	+1.26	-1.26	-1.26	0	100	0	0
Z Scores

Cell Formulas
Range	Formula
C3	C3	=AVERAGE(TblZs[A])
D3	D3	=AVERAGE(TblZs[B])
E3	E3	=AVERAGE(TblZs[C])
F3	F3	=AVERAGE(TblZs[D])
C4	C4	=STDEV.S(TblZs[A])
D4	D4	=STDEV.S(TblZs[B])
E4	E4	=STDEV.S(TblZs[C])
F4	F4	=STDEV.S(TblZs[D])
G6:G10	G6	=([@A]-C$3)/C$4
H6:H10	H6	=([@B]-D$3)/D$4
I6:I10	I6	=([@C]-E$3)/E$4
J6:J10	J6	=([@D]-F$3)/F$4
K6:K10	K6	=([@[A Z]]-MIN([A Z]))/(MAX([A Z])-MIN([A Z]))*100
L6:L10	L6	=([@[B Z]]-MIN([B Z]))/(MAX([B Z])-MIN([B Z]))*100
M6:M10	M6	=([@[C Z]]-MIN([C Z]))/(MAX([C Z])-MIN([C Z]))*100
N6:N10	N6	=([@[D Z]]-MIN([D Z]))/(MAX([D Z])-MIN([D Z]))*100

This table used the manual line break (Alt+Enter). Notice the multi-line formulas (K19:N19).

Weighted Ratings Demo.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
18	A	B	C	D	A Z	B Z	C Z	D Z	A 0-100	B 0-100	C 0-100	D 0-100
19	5	1	25	10	+1.26	-1.26	+1.26	+1.26	100	0	100	100
20	4	2	20	9	+0.63	-0.63	+0.63	+0.63	75	25	75	75
21	3	3	15	8	=0.00	=0.00	=0.00	=0.00	50	50	50	50
22	2	4	10	7	-0.63	+0.63	-0.63	-0.63	25	75	25	25
23	1	5	5	6	-1.26	+1.26	-1.26	-1.26	0	100	0	0
Z Scores

Cell Formulas
Range	Formula
G19:G23	G19	=([@A]-C$3)/C$4
H19:H23	H19	=([@B]-D$3)/D$4
I19:I23	I19	=([@C]-E$3)/E$4
J19:J23	J19	=([@D]-F$3)/F$4
K19:K23	K19	=([@[A Z]]-MIN([A Z]))/(MAX([A Z])-MIN([A Z]))*100
L19:L23	L19	=([@[B Z]]-MIN([B Z]))/(MAX([B Z])-MIN([B Z]))*100
M19:M23	M19	=([@[C Z]]-MIN([C Z]))/(MAX([C Z])-MIN([C Z]))*100
N19:N23	N19	=([@[D Z]]-MIN([D Z]))/(MAX([D Z])-MIN([D Z]))*100

 

[shift-del]

Did you try to change the column width?

 

[Edgar_]

When you're setting up the headers for a ListObject, remember they've gotta be unique, and whatever names you pick will show up in your formulas. In my opinion, it's preferred to use a ListObject and all its benefits as if it was a database table that only a Form can touch, if you're going to use it for work and show then make sure you won't have a complex view. Using the "view" as a form for entering data is a common practice among Excel users, most times that works, but once you want to make fancy stuff within controlled constraints, it's best to separate view from data entry.

As for your choices, each has its good points and bad points, but since you're basically stuck dealing with the limitations of the ListObject, I suggest these:
1. Change your header design
2. Don't use a ListObject
3. Keep unique things in the ListObject and add a line above with the same format but make sure it's not a part of the ListObject.

 

[JenniferMurphy]

Did you try to change the column width?

Yes, that's the problem. When I change the column width, Excel reformats the header text.

 

[JenniferMurphy]

When you're setting up the headers for a ListObject, remember they've gotta be unique,

Of course

and whatever names you pick will show up in your formulas.

I think that was my main point.

In my opinion, it's preferred to use a ListObject and all its benefits as if it was a database table that only a Form can touch, if you're going to use it for work and show then make sure you won't have a complex view. Using the "view" as a form for entering data is a common practice among Excel users, most times that works, but once you want to make fancy stuff within controlled constraints, it's best to separate view from data entry.

Hmm... I hadn't considered a form. But I think that's more work than I want to go to for this project.

As for your choices, each has its good points and bad points, but since you're basically stuck dealing with the limitations of the ListObject, I suggest these:
1. Change your header design

I don't know what you mean by that. Can you give an example?

2. Don't use a ListObject

Do you mean don't use a table? That would take away a lot of the benefits of a table. This is a relatively minor annoyance, so if there is no solution, I'll just live with it, like I do so many minor, and not so minor, annoyances from the geniuses at M$FT.

3. Keep unique things in the ListObject and add a line above with the same format but make sure it's not a part of the ListObject.

Not sure I understand this one either. Examples?

 

[shift-del]

Yes, that's the problem. When I change the column width, Excel reformats the header text.

Never happens to me.

 

[JenniferMurphy]

Never happens to me.

Here's what I mean. I have a table with 4 columns of data (A, B, C, & D) and then two sets of 4 columns with derivative data. I want each of the 12 columns to have the base data letters (A-D) at the top of the headers on a line by themselves.

1. Table 1 has the table headers formatted the way Excel formats them. In columns F-I, everything is on one line. In columns J-M, Excel broke the headers after the "-". Pertty much unreadable.
2. Table 2 has the table headers formatted the way I want them. In columns S-X, I accomplished this by adding spaces after the letters (A-D) until they were on a line by themselves. This is a bit of a trial and error process.
3. In Table 3, I widened columns AF-AM. This can happen if the data expands. Excel reformatted the headers and they are all screwed up again. To get it back the way I want it, I have to go in and add more spaces.

Is that clearer?

Are you saying widening the columns does not have the effect shown above for you?

Here's the minisheet.

Table Headers.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
3							Table 1													Table 2												Table 3
4	B	C	D	E	F	G	H	I	J	K	L	M		O	P	Q	R	S	T	U	V	W	X	Y	X		AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
5	A	B	C	D	A Z	B Z	C Z	D Z	A 0-100	B 0-100	C 0-100	D 0-100		A	B	C	D	A Z	B Z	C Z	D Z	A 0-100	B 0-100	C 0-100	D 0-100		A	B	C	D	A Z	B Z	C Z	D Z	A 0-100	B 0-100	C 0-100	D 0-100
6	5	1	25	10	+1.26	-1.26	+1.26	+1.26	100	0	100	100		5	1	25	10	+1.26	-1.26	+1.26	+1.26	100	0	100	100		5	1	25	10	+1.26	-1.26	+1.26	+1.26	100	0	100	100
7	4	2	20	9	+0.63	-0.63	+0.63	+0.63	75	25	75	75		4	2	20	9	+0.63	-0.63	+0.63	+0.63	75	25	75	75		4	2	20	9	+0.63	-0.63	+0.63	+0.63	75	25	75	75
8	3	3	15	8	=0.00	=0.00	=0.00	=0.00	50	50	50	50		3	3	15	8	=0.00	=0.00	=0.00	=0.00	50	50	50	50		3	3	15	8	=0.00	=0.00	=0.00	=0.00	50	50	50	50
9	2	4	10	7	-0.63	+0.63	-0.63	-0.63	25	75	25	25		2	4	10	7	-0.63	+0.63	-0.63	-0.63	25	75	25	25		2	4	10	7	-0.63	+0.63	-0.63	-0.63	25	75	25	25
10	1	5	5	6	-1.26	+1.26	-1.26	-1.26	0	100	0	0		1	5	5	6	-1.26	+1.26	-1.26	-1.26	0	100	0	0		1	5	5	6	-1.26	+1.26	-1.26	-1.26	0	100	0	0
Table Headers (2)

 

[Edgar_]

Hmm... I hadn't considered a form. But I think that's more work than I want to go to for this project.

That's perfectly valid. If you think a form is overkill, then don't add any form.

I don't know what you mean by that. Can you give an example?

Not sure I understand this one either. Examples?

Yes, this is what you want it to look:

1. Notice how I'm using two rows for the headers. You can't do this with a ListObject, that's why the first row is not a part of the ListObject. It's just a simple row formatted to look like it. For that, I had to duplicate the ListObject design; then I applied the duplicated design to the ListObject; finally, I modified the header to remove its top border. Once I was done with the ListObject format, applying a simple shade of the same color to the first row (the one that is not part of the ListObject) gives the desired effect. If you do not remove the border, it looks like this:

2. For columns A-D in my example, Z is not actually Z, but ZZ1, ZZ2, etc. Why does it show just Z? I simply edited the first and the last character to be the same color as the background. Had I used Z1, Z2, etc., it would not have been aligned with the character above. The double Z is just for consistency, what other character can you use? that's your choice, I chose the extra Z here.

3. Instead of just 0-100, they will be 0-1001, 0-1002, etc. In the first picture, I simply painted the last character with the same color as the background, again, but it will be there in the formula. The added character does not make it look off-center, so I left it like that. There's an argument to be made about consistency there, but we're working within the limitations of the tool.

This is what I meant by changing your header design and adding a line above. The workarounds aren't pretty, but the ListObject does not let us caption the columns, they just have a name property, so, again, we're working with the limitations of the ListObject, this is what we have for the columns:

Notice we only have a Name property. No caption. There's a ListDataFormat node there, but there's nothing usable there either. DataBodyRange, Range and the others are unusable too.

That's why separating view from data entry form is important. But if you want to avoid using a form and do everything directly on the report, workarounds will have to be made. You have made a few, these are just others. Any workaround fixes one thing and messes up another. It's up to us to decide what annoys us less. 4

In this case, I suggest making a form, saving the data in a ListObject and showing in some sort of report or view. But you've stated your position about not wanting to spend any more time on this.

 

[JenniferMurphy]

That's perfectly valid. If you think a form is overkill, then don't add any form.

My thinking exactly.

Notice how I'm using two rows for the headers.

I actually did fool around with that a bit, but the hassle of keeping the columns aligned was more trouble than I wanted.

For columns A-D in my example, Z is not actually Z, but ZZ1, ZZ2, etc. Why does it show just Z? I simply edited the first and the last character to be the same color as the background.

I forgot that I also played around with this awhile ago, too. The problem with it was that the formulas didn't look like the headers, which is error prone.

That's why separating view from data entry form is important. But if you want to avoid using a form and do everything directly on the report, workarounds will have to be made. You have made a few, these are just others. Any workaround fixes one thing and messes up another. It's up to us to decide what annoys us less. 4

In this case, I suggest making a form, saving the data in a ListObject and showing in some sort of report or view. But you've stated your position about not wanting to spend any more time on this.

Hmmm... I may have to rethink using a form.

Thanks

PS: I am going to mark your post as the solution because I think it's as close to a solution as we are going to get. Cheers

 

[shift-del]

When I use Excel tables then I do not add extra spaces or insert line brakes in the headers. As you pointed the structured references get garbled up. "Clean" display of formulas is more important then "clean" display of headers.
I may set "wrap text" if the header text is to wide and adjust the column width and the row heigth.