5

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.

1
  • Do you have an image of what you'd like the result to be? Also, is the sample data the layout you must work with, or can you reformat your source?
    – dav
    Aug 17, 2016 at 12:28

3 Answers 3

2
+50

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!

8
  • When I put the months in the Series X Value, the X axis still comes in numbesrs like 0.2,0.4,0.6.. etc Aug 10, 2016 at 12:24
  • Excel probably doesn't realize that your "Month" column consists of dates. Try clicking on one of the cells in this column and make sure that the number format is set to "Date". You might need to change the values in these cells so that Excel recognizes them, e.g. 01/31/2016 instead of just "Jan".
    – kruschk
    Aug 10, 2016 at 23:46
  • 1
    Chiming in for some help. Kruschk has a good idea. You can also use something like this for month: =MONTH(1) for January. Then customized the formatting to MMM for 3-digit months.
    – ejbytes
    Aug 12, 2016 at 1:07
  • Oh cool, I didn't know about the MONTH() function. Thanks for chiming in, ejbytes!
    – kruschk
    Aug 12, 2016 at 2:09
  • It isn't really working. Do you guys have a sample chart or table that I can view from? Aug 15, 2016 at 20:11
4

The below screenshots are from Office 2010. Steps in Office 2007 should be pretty much the same.

Step 1 - Merge both tables into a single one. Something like this.

Step 1

(Note: Both Sr. Mgrs have same Avg. Hours, so i have reduced Joanne's by 10% so that lines representing each manager is displayed separately instead of overlapping each other)

Add other Sr. Managers in row 4, 5, & so on...

Step 2 - Select the table > 'Insert' in ribbon > 'Line ' under Charts group > 2-D Line > 'Line' chart

Step 2 - Selecting the type of chart

Result:

Chart

2

The first thing you need to do is to arrange your data a little bit, make all the data of the managers into the same table. It should look like this.

Average hours   Jan     Feb     Mar     Apr     May
Baby Jones      93.5    81      94.5    95.5    89.5
Joanne Jones    91.5    90      88.5    85.5    81

@xypha above has already answered about how to do this directly in Excel. In here I propose to use a tool called Funfun which allows you to use JavaScript within Excel so that you could use powerful JavaScript libraries like Chart.js or D3.js to plot chart.

enter image description here

For your problem, I draw a chart as above by writing some JavaScript code with Chart.js in the Funfun Online Editor, you could check the link below:

https://www.funfun.io/1/edit/5a365e7c74efa7334ff272a6

Then, the Funfun Excel Add-in enables to run JavaScript code in Excel. You could of cause type code in the embedded editor of the add-in, but what is cooler is to directly load my code by its URL as follows to use it:

enter image description here

enter image description here

Disclosure: I'm a developer of Funfun

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .