Forum Discussion
Solved
How to create a line chart from multiple Excel sheets
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.
- 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.
5 Replies
- 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:
