How to create a line chart from multiple Excel sheets

Copper Contributor


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.

5 Replies
best response confirmed by dankello (Copper Contributor)


  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.

@Hans Vogelaar thank you :smile: 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


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


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:



@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?


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.