Line Graph - Legend Series on an Axis - League Table Tracker

[JungleJme]

Hi,

I have a set of data that ranks a number of stores based on their performance each week.

I want to keep track of this data over the course of a "season" - showing each store, climbing or slipping down the league on a line graph.

From the attached you will see what i am trying to achieve. I want to have the legend entries on the y-axis and then have a second y axis on the right for position from one down.

Ideally i then want to put conditional formatting on that dims each line until you hover the mouse over the store name on the y-axis, then the line for that data highlights.

Any idea on how i can achieve this?

[Andy Pope]

Uses a more positive user interaction than simply mouse over but maybe useful.
http://peltiertech.com/Excel/Charts/ParallelCoord.html

[JungleJme]

Cheers mate, that does indeed look really useful gonna have a crack with this. See what i can do with it.

Any idea how to get the legend names to appear on the primary y-axis and add a secondary y-axis for the rank? I'll play with what you've sent tho, thanks a lot!

[Andy Pope]

Add another series and change chart type for that to Bar.
Add secondary vertical axis.
Format to be in reverse and cros on tickmarks.

I've left the fill trasnparent so you can see the bars. Set fill to none to remove them.

[JungleJme]

Great! I've seen it out there so many times i knew it had to be possible, thanks very much Andy!

[JungleJme]

Ah, just noticed that its kind of backwards. I wanted the Names to appear on the left, so the line starts with their starting position then you can follow the line across from its origin to where they end up. Reading left to right.

I fear i may be asking to much though as i've tried various combinations of playing with the axes to no avail as of yet! Although i have tweaked a couple of things to show what i mean...all i need is for the right axis to display on the left and the left on the right....

[Andy Pope]

set the Axis label position for both vertical axis to Low