Can I have one chart line on top and its legend in the middle?

[JenniferMurphy]

I am working on a chart showing the "power rankings" for the San Francisco 49ers football team. If I arrange the 3 plot lines so that the legends are in the same order as the plot lines, as in the image on the left, then the red plot line is on top. If I rearrange them so that the black plot line is on top, as in the image on the right, then the legends are out of order. Is there a way to get the blank plot line on top and still have the legends in the same order as the plot lines?

 

[6StringJazzer]

Imagine the plot line for each series to each be a layer. The order of the series in the legend is related to how they are ordered in the layers, which can be seen in the SERIES formulas for the chart. The series on top is going to be at the bottom of the legend, and you can't break that dependency.

I did a chart a little like yours and show how this is seen in the SERIES formula for the line that's on top.

 

[JenniferMurphy]

Imagine the plot line for each series to each be a layer. The order of the series in the legend is related to how they are ordered in the layers, which can be seen in the SERIES formulas for the chart. The series on top is going to be at the bottom of the legend, and you can't break that dependency.

I did a chart a little like yours and show how this is seen in the SERIES formula for the line that's on top.

View attachment 119270

So you are saying that there is no way to do what I want, right?

Here's the minisheet for my table.

2024 NFL Power Rankings.xlsm
	C	D	E	F	G
4	Week	Record	Lowest Ranking	Average Ranking	Highest Ranking
5	2/13/24	n/a	2	2	2
6	3/19/24	n/a	2	2	2
7	4/29/24	n/a	2	2	2
8	Pre Season	n/a	2	2	2
9	Week 1	0-0	2	2	2
10	Week 2	1-0	2	2	2
11	Week 3	1-1	2	2	2
12	Week 4	1-2	12	12	12
13	Week 5	2-2	12	12	12
14	Week 6	2-3	18	18	18
15	Week 7	3-3	14	10.5	7
16	Week 8	3-4	16	13.5	11
17	Week 9	4-4	14	11	8
18	Week 10	4-4	13	10	7
19	Week 11	5-4	14	9.8	5
49ers

 

[6StringJazzer]

I don't like to say that something is impossible because that's the moment someone shows how to do it.

But I've been working with charts in Excel for at least 20 years and I have never found a way to do what you want.

 

[JenniferMurphy]

Well, it was wise of you to not say it is impossible. I asked ChatGPT and it came up with what I think is a clever solution. I will briefly outline the solution, then post the minisheet and some images.

1. Create a dummy column in the table that is a duplicate of the Average column.
2. Add it to the chart as a new series.
3. Move it to the bottom of the list so that it will be the top plot line on the chart.
4. Set it to the same plot color(s) as the one it duplicates.
5. Delete the bottom legend entry for the dummy column. Just click to select it and press Delete.

Here is the new chart along with the Data Series dialog and a peek at the table.

And here is the minisheet.

2024 NFL Power Rankings.xlsm
	B	C	D	E	F	G
4	Week	Record	Lowest Ranking	Average Ranking	Highest Ranking	Average (Dummy)
5	2/13/24	n/a	2	2	2	2
6	3/19/24	n/a	2	2	2	2
7	4/29/24	n/a	2	2	2	2
8	Pre Season	n/a	2	2	2	2
9	Week 1	0-0	2	2	2	2
10	Week 2	1-0	2	2	2	2
11	Week 3	1-1	2	2	2	2
12	Week 4	1-2	12	12	12	12
13	Week 5	2-2	12	12	12	12
14	Week 6	2-3	18	18	18	18
15	Week 7	3-3	14	10.5	7	10.5
16	Week 8	3-4	16	13.5	11	13.5
17	Week 9	4-4	14	11	8	11
18	Week 10	4-4	13	10	7	10
19	Week 11	5-4	14	9.8	5	9.8
49ers

Cell Formulas
Range	Formula
G6:G19	G6	=[@[Average Ranking]]