Forum Discussion
How to create a line chart from multiple Excel sheets
- The y-values of a chart must be numeric. So change Bronze to 1, Silver to 2 and Gold to 3.
- However, you can use the custom number format [=1]"Bronze";[=2]"Silver";"Gold" to display them as Bronze, Silver and Gold.
- 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.
- The y-values of a chart must be numeric. So change Bronze to 1, Silver to 2 and Gold to 3.
- However, you can use the custom number format [=1]"Bronze";[=2]"Silver";"Gold" to display them as Bronze, Silver and Gold.
- 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.
HansVogelaar 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
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:
HansVogelaar 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.