SOLVED

How to create a line chart from multiple Excel sheets

Copper Contributor
 
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:

 

chart1.png

The data is on sheets like this:

 

week1.PNGweek2.PNGweek3.PNG

 

How can I create this line chart in Excel?

 

Thank you in advance.

5 Replies
best response confirmed by dankello (Copper Contributor)
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.

@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

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

 

HansVogelaar_0-1697024177454.png

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

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

HansVogelaar_0-1697027151137.png

1 best response

Accepted Solutions
best response confirmed by dankello (Copper Contributor)
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.

View solution in original post