How to create a line chart from multiple Excel sheets

[dankello]

Hi,

For student rewards (Bronze, Silver, Gold). I need to create a line chart which tracks progress over several weeks. Each week data is on a separate Excel sheet. I want the line chart to look like this:

The data is on sheets like this:

How can I create this line chart in Excel?

Thank you in advance.

[#2 · Accepted solution]

@dankello 

1. The y-values of a chart must be numeric. So change Bronze to 1, Silver to 2 and Gold to 3.
2. However, you can use the custom number format  [=1]"Bronze";[=2]"Silver";"Gold"  to display them as Bronze, Silver and Gold.
3. You'll have to create a new range to act as data source for the chart. In the attached example, I used VLOOKUP combined with INDIRECT to do this.

[#3]

@Hans Vogelaar thank you  I really appreciate your help. This works for me!

However how do I change the custom number format? I get a "cannot use the number format you typed" error each time I try to change it?

I need [=0]"4th Place";[=1]"Bronze";[=2]"Silver";[=3]"Gold";

It will let me do [=0]"4th Place";[=1]"Bronze"; but nothing else?

Thank you

[#4]

@dankello 

Unfortunately, that won't work - you cannot specify more than two sections similar to

[=1]"Bronze"

in a custom number format. I used two of such sections plus a section for all other numbers.

We cannot do that with 4 different values.

So I suggest you set the number format to General, and add a "legend" on the sheet that explains the relationship between the numbers and the scores:

[#5]

@Hans Vogelaar thank you. Is there a way to remove the vertical axis labels (0,1,2,3) and replace them with 4th Place, Bronze, Silver, Gold?

[#6]

@dankello 

It is possible, but it is rather complicated and tricky.

In the attached version, I added a dummy series with values 0.

I changed its chart type from Line to Clustered Bar.

I set the x-values for the bar chart to the descriptions Fourth Place etc.

I then hid the original y-axis and the new secondary x-axis.

I set the (vertical) x-axis of the bar chart to place the data on the tick marks.