Putting Multiple Lines on an Excel Graph
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...
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.
3 Answers
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:
- Add a scatter plot into your spreadsheet ("Insert" ribbon > Scatter in the "Charts" section).
- Right-click somewhere in your chart and click on "Select Data".
- In the window that appears, click on "Add".
- Add a "Series name" (the manager's name would probably work best here), "Series x-values" (the months), and "Series y-values" (the percentages).
- Repeat steps 3-4 for as many managers as you need.
- 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:
Hope this helps!
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.
(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
Result:
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.
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:
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:
Disclosure: I'm a developer of Funfun
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
