How to manage table headings

[JenniferMurphy]

I keep running into situations where the table heading names are unnatural and cumbersome because they must be unique. This is the most recent example. This table contains information about the true makeup of a can of nuts labelled "Whole Cashews". Looking at the contents, it seems like most of the cashews are "whole" (intact), but not nearly all of them. Curious, I separated them into 3 groups: Whole, Half, and Fragment. Then I weighed and counted them.(1)

So, for each category, I have 5 columns: Weight, Weight %, Count, Count %, and Weight Each. I would like a nice compact, easy-to-read table like this:

But the best I can do using the Table facility is this, which for me is distinctly inferior in appearance and readability:

Advertising Checks.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
4			All Cashews		Whole Cashews					Half Cashews					Cashew Fragments
5	Date	Can Wt	Total Wt	Total Wt%	Whole Wt%	Whole #%	Whole Wt	Whole #	Whole Wt Ea	Half Wt%	Half #%	Half Wt	Half #	Half Wt Ea	Frag Wt%	Frag #%	Frag #	Frag Wt	Frag Wt Ea	Total #
6	1/29/22	517g	523g	101%	72%	44%	377g	266	1.42g	18%	22%	95g	132	0.72g	10%	35%	211	51g	0.24g	609
Planters Whole Cashews

Cell Formulas
Range	Formula
D6	D6	=[@[Whole Wt]]+[@[Half Wt]]+[@[Frag Wt]]
E6	E6	=[@[Total Wt]]/[@[Can Wt]]
F6	F6	=[@[Whole Wt]]/[@[Total Wt]]
G6	G6	=[@[Whole '#]]/[@[Total '#]]
J6	J6	=[@[Whole Wt]]/[@[Whole '#]]
K6	K6	=[@[Half Wt]]/[@[Total Wt]]
L6	L6	=[@[Half '#]]/[@[Total '#]]
O6	O6	=[@[Half Wt]]/[@[Half '#]]
P6	P6	=[@[Frag Wt]]/[@[Total Wt]]
Q6	Q6	=[@[Frag '#]]/[@[Total '#]]
T6	T6	=[@[Frag Wt]]/[@[Frag '#]]
U6	U6	=[@[Whole '#]]+[@[Half '#]]+[@[Frag '#]]

Is there any way to make the Table look more like the example? I am mainly talking about the headings.

I wish Excel had an option for multi-row headings where the reference labels are some combination of each row and cells in one row that span several columns are shared by the other column rows.

(1) Yes, Alan, I know what you are thinking. If I have time for silliness like this, I have time to learn Power Query.

 

[JenniferMurphy]

I did try one solution that I think ought to work but doesn't. I tried using the the Number format to change the formatting of one of the headings from General to "Num". It accepted the format, but it did not change the text of the heading.

 

[Alex Blakenburg]

I tried a few options but they might all cause you grief further down the track.
eg.
1) Use headings like the below and select the word "Whole" and format it using the same colour as the background color.
This would leave only the common part visible.
Wt% Whole, #% Whole, Wt Whole, # Whole
You would still see the full column name in the formulas

2) Add a different number of spaces to matching headings/
It wouldn't be very clear in formulas which column you are using though.

3) Copy you current headings into the body of the table and make them how you would like to see them.
Then turn off the Header Row on the table, so the unique heading names are no longer visible.
If you do it in order the formulas will still auto expand with the table.
Fixing a corrupted formula that needs to be copied across all rows would be a bit painful.
Adding a filter to the table would automatically make the hidden header row visible again,
Having the 2 header rows in the body of the table could also cause you grief.

 

[JenniferMurphy]

I tried a few options but they might all cause you grief further down the track.
. . .

Some interesting ideas, but, as you say, probably more risky than useful.

Thanks

 

[Alex Blakenburg]

Sorry I don’t have a better option for you.

 

[JenniferMurphy]

Sorry I don’t have a better option for you.

I'm afraid that's because there isn't one. Thanks for trying.

I went to the trouble to set up a table like I used to do before I was nudged over to tables. It takes a lot more setup and maintenance work, but I can get a nice compact, readable table. Here's a screen shot because the minisheet doesn't preserve all of the formatting. I like to use a light blue background to indicate data entry cells. All others are calculated.

It's too bad that the table feature doesn't allow for something like that.

And here's the minisheet. It isn't reporting the formulas correctly. They should all have @ signs. The first one in D5 is actually =@WtWhole+@WtHalf+@WtFrag because all of the columns are infinite named ranges ($D:$D). The same is true for the header row ($4:$4) and the averages row ($7:$7). Interestingly, the minisheet got the formula in D7 right.

Advertising Checks.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3			All Cashews			Whole Cashews					Half Cashews					Cashew Fragments
4	Date	Can Wt	Wt	%	#	Wt%	#%	Wt	#	Wt Ea	Wt%	#%	Wt	#	Wt Ea	Wt%	#%	Wt	#	Wt Ea
5	1/26/22	517g	523g	101%	609	72%	44%	377g	266	1.42g	18%	22%	95g	132	0.72g	10%	35%	51g	211	0.24g
6	1/29/22	517g	551g	107%	559	72%	48%	398g	268	1.49g	17%	21%	91g	118	0.77g	11%	31%	62g	173	0.36g
7		Averages	537g	104%	584	72%	46%	388g	267	1.45g	17%	21%	93g	125	0.75g	11%	33%	57g	192	0.30g
Planters Whole Cashews (2)

Cell Formulas
Range	Formula
D5:D6	D5	=WtWhole+WtHalf+WtFrag
E5:E6	E5	=WtAll/WtCan
F5:F6	F5	=NumWhole+NumHalf+NumFrag
G5:G6	G5	=WtWhole/WtAll
H5:H6	H5	=NumWhole/NumAll
K5:K6	K5	=WtWhole/NumWhole
L5:L6	L5	=WtHalf/WtAll
M5:M6	M5	=NumHalf/NumAll
P5:P6	P5	=WtHalf/NumHalf
Q5:Q6	Q5	=WtFrag/WtAll
R5:R6	R5	=NumFrag/NumAll
U5:U6	U5	=WtFrag/NumFrag
D7:U7	D7	=AVERAGE(OFFSET(@Hdr,1,0),OFFSET(@Avgs,-1,0))

 

[JenniferMurphy]

1) Use headings like the below and select the word "Whole" and format it using the same colour as the background color.

I thought I tried this before, but apparently not. I just tried it again and it seems be working.

Here's a table I created to see how the final Olympic medal totals would be different if the medals were weighted. This one is sorted by the column that weights the medals: 4-2-1 for Gold, Silver, & Bronze. I had to put a character both before and after the visible header to keep it centered.

Olympic Medals.xlsx
	B	C	D	E	F	G	H	I	J	K	L
3		Medals			Weighted Totals			Rankings
4	Country	Gold	Silver	Bronze	111	321	421	.111.	.321.	.421.	.Gold.
5	Norway	16	8	13	37	77	93	1	1	1	1
6	Germany	12	10	5	27	61	73	3	2	2	2
7	ROC	6	12	14	32	56	62	2	3	3	9
8	United States	8	10	7	25	51	59	5	4	4	4
9	Sweden	8	5	5	18	39	47	6	6	5	4
10	Canada	4	8	14	26	42	46	4	5	6	11
11	Austria	7	7	4	18	39	46	6	6	6	7
12	Netherlands	8	5	4	17	38	46	9	8	6	4
13	China	9	4	2	15	37	46	11	9	6	3
14	Switzerland	7	2	5	14	30	37	12	11	10	7
15	France	5	7	2	14	31	36	12	10	11	10
16	Japan	3	6	9	18	30	33	6	11	12	12
17	Italy	2	7	8	17	28	30	9	13	13	13
18	South Korea	2	5	2	9	18	20	14	14	14	13
19	Finland	2	2	4	8	14	16	15	15	15	13
20	Slovenia	2	3	2	7	14	16	16	15	15	13
21	New Zealand	2	1	0	3	8	10	18	17	17	13
22	Australia	1	2	1	4	8	9	17	17	18	18
23	Hungary	1	0	2	3	5	6	18	19	19	18
24	Great Britain	1	1	0	2	5	6	20	19	19	18
25	Belgium	1	0	1	2	4	5	20	21	21	18
26	Czech Republic	1	0	1	2	4	5	20	21	21	18
27	Slovakia	1	0	1	2	4	5	20	21	21	18
28	Belarus	0	2	0	2	4	4	20	21	24	24
29	Spain	0	1	0	1	2	2	25	25	25	24
30	Ukraine	0	1	0	1	2	2	25	25	25	24
31	Estonia	0	0	1	1	1	1	25	27	27	24
32	Latvia	0	0	1	1	1	1	25	27	27	24
33	Poland	0	0	1	1	1	1	25	27	27	24
20220221

Cell Formulas
Range	Formula
G5:G33	G5	=3*[@Gold]+2*[@Silver]+1*[@Bronze]
H5:H33	H5	=4*[@Gold]+2*[@Silver]+1*[@Bronze]
I5:I33	I5	=RANK.EQ([@111],[111])
J5:J33	J5	=RANK.EQ([@321],[321])
K5:K33	K5	=RANK.EQ([@421],[421])
L5:L33	L5	=RANK.EQ([@Gold],[Gold])

Hmmm... It looks like the minisheet ignored the character colors. Here's how it really looks:

Thanks for that suggestion.

 

[Alex Blakenburg]

Thanks for showing us the end product. Glad it helped.

 

[JenniferMurphy]

Thanks for showing us the end product. Glad it helped.

I also tried your solution #3 with a slight modification. Instead of copying the header row inside the table, I copied it just above the table. Then when I turn the header off, I move it down a row. That also works pretty well. If I need to add a column or do anything else that would be easier with the header row on, I just move the faux header up 1 row and turn the header back on. After I make whatever changes I need, I repeat the steps above.

I'm not sure which one I like better.