Excel – Putting Multiple lines on an excel graph

chartsmicrosoft excelmicrosoft-excel-2007

Currently I have some data like this, here is some dummy data:

Month  Senior Manager   Average Hours
Jan     Bobby Jones      93.5%
Feb      Bobby Jones      81%
Mar     Bobby Jones      94.5%
Apr     Bobby Jones      95.5%
May     Bobby Jones      89.5%

Month  Senior Manager   Average Hours
Jan     Joanne Jones      93.5%
Feb      Joanne Jones      81%
Mar     Joanne Jones      94.5%
Apr     Joanne Jones      95.5%
May     Joanne Jones      89.5%

I have about five other senior manages like this.

What I want to do is create a line graph with each senior manager having its own colored line. The Y axis Should Have the % numbers from 0 – 100% plus, because some managers employees go over the targeted hours. Further, on the x axis I want the Month and the Senior Manager corresponding to that month to display.

How can I do something like this, or anything remotely similar? I have tried making a line graph, but it simply pus everything on one line graph with the same color. Here, Bobby Jones should have a different color and line than Joanne.

Best Answer

I think what you are trying to achieve would be done most easily with a scatter plot that uses several datasets; one for each manager. I don't have Excel with me right now, but if I recall correctly, these are the steps:

  1. Add a scatter plot into your spreadsheet ("Insert" ribbon > Scatter in the "Charts" section).
  2. Right-click somewhere in your chart and click on "Select Data".
  3. In the window that appears, click on "Add".
  4. Add a "Series name" (the manager's name would probably work best here), "Series x-values" (the months), and "Series y-values" (the percentages).
  5. Repeat steps 3-4 for as many managers as you need.
  6. You may add lines connecting the data points by right-clicking on one of the data sets and selecting "Format Data Series".

The following links might also be helpful to you:

http://www.java2s.com/Tutorial/Microsoft-Office-Excel-2007/0100__Chart/AddaDataSeries.htm

https://www.youtube.com/watch?v=BjsjAbvbWao

Hope this helps!

Related Question