Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Adding Multiple Line Series To One Chart

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I dont know if I used the right terminology in the title but, i am trying to create a milestone graph - using an incremental y scale (going up in 1s) , dates along the x axis and the milestones plotted at 0.5, 1.5, 2.5, 3.5.....etc

Problem is I need to colour each point differently, for example missed milestones (dates in the past) as red, others in green , some amber, etc. I created a table with 3 columns, red, amber, green - there are dates for each point(0.5,1.5,2.5....) but you cant have a date against multiple points i.e if there is a date in the red column, there wont be one in the green column, instead it will be #n/a.

So, when I select the Red and the position columns i create a normal line graph, now i want to select the amber column and the position column , copy it and paste special over original graph as a new series, but this just seems to repeat all the points generated by selecting the red column. If i create a separate graph using amber and position then it looks as i expect with different points plotted to the red graph.

Any help appreciated!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from

Similar Topics







Hello,

Guys/ Ladies

I was wondering if I could leverage some expertise on creating a macro to achieve the following.

If we take the table below as an example...


column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated)
column B = project: x, x, x, x, ,x (name of the project and trend line)
column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph)
column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table)
column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)

NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.

Date Project Delivery Date Status Comments
01/02 x 01/05/2009 Green original date
07/02 x 08/05/2009 Amber delay supply
14/02 x 20/05/2009 Red out of money
21/02 x 30/05/2009 Green on track
28/02 x 28/02/2009 Blue delivered-wow

So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.

Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.

Any help would be amazing!!!

Thanks in advance guys.


Ok, I have this pretty much sussed but I am unable to get the colour RED to show up.. weird I know. I have GREEN, AMBER & RED in the series (source data) and values in NAME & VALUES

GREEN: ='Graph Data'!$C$5
AMBER: ='Graph Data'!$D$5
RED:='Graph Data'!$E$5

Help please?


I have a Line graph combined with an XY graph on a single chart object. They use different axes: the x-axis of the line graph are dates, the x-axis of the XY graph are numbers. The data points of the line graph fall between the gridlines whereas the data points of the XY graph fall right on top of the gridlines.

How do I change the XY graph so that its points fall between the gridlines?

I've tried changing the XY axis to time-scale but the option is greyed out. The column has already been set to Number format. I'm using Office 2003.


Hi All,

I've not really done much on graphs in excel and am attemping to create a Red/Amber/Green chart.

I have some data listing a project and the number of weeks it has been running.

I have a few lookup cells also with the max number for Red Amber & Green.

E.g.
Project A running for 20 weeks
Project B running for 35 weeks
Project C running for 10 weeks
Project D running for 45 weeks

Green is up to 25 weeks
Amber is 25 to 40 weeks
Red is +40 weeks

I would like to show each project on a graph with a column that starts green, goes amber where applicable and obviously goes red if the project has been running for 40+ weeks.

Thanks
dvent


I am trying to create a line graph where the colour of the performance line changes colour depending on if its under or over performing. - is this possible?
Example data

"Qtr1 92.9%
"Qtr2 100.0%
"Qtr3 94.0%
"Qtr4 85.0%

Target 95%

Now I can create a line chart that shows the performance of Q1 to Q4 and a line showing that performance against the target.

Is it possible to show every thing that is 95%+ as green, below 90% as red and 90-95% as amber.

Ideally I will have two lines - a target line - static at 95% over the 4 periods.
and a performance line that is multicoloured depending on that periods performance.


I need to create a graph based on a cell colour. The spreadsheet is used to control budgets by using a traffic light system to determine what has been spent (or definitely will be spent), what may be spent, or what budgeted cost have yet to be spent (or planned to be spent). These will be coloured green, amber, red respectively.

The chart will be split monthly with each bar split green, amber, red, accordingly.

I would appreciate any help at all with this problem.


I created a graph in excel to show Price (Series 1) Quantity Ordered (Series 2, plotted on secondary y axis) all based on the dates ordered (x axis) The dates are the same for the two series and match up on the data entered to create the graph. When hovering to view a data point, the data points are not matching the data entered, and the wrong dates are being placed on the wrong spot of the x axis. The arent even going in sequential order. The data that was input was in sequential order. Please help


Hi I have a set of data to work out the staff utilisation on a monthly scale
What i require is an area and line graph in 1 There will be two axis one for porportion (%) and one for number. The "number of people" and number of people utilised" will be lines and will plotted against the left axis (number axis) and the "green target" amber target" will be constant lines and plotted against the right axis (%) while the "prop of staff utilised" will also be ploteed against the right axis but it will be in the form of area/mass instead of a line Can anyone help please. When i tried it i could only get eithe an area or a line graph i dont know how to combine it in 1


Hi All.
Newbie first question - and I can't get the html bit to work in Excel, so you'll have to use your imaginations!!

I've created a graph, finally, which looks exactly as I want it.

For my sins, it has two sets of (vertical) stacked columns and a single column, from the first Y axis.

It has two sets of points (x-y style) from the second Y axis. All well and good.

But, I can't get the points to join together with lines - like a line graph!!
(I have tried X-Y and Line Graph types - neither help!)

I have clicked on the data set, and formatted the data series to add a line, and in the legend, it shows a little point with a line through it. But the line does not appear on the graph.

Any ideas?

Was that even remotely clear??!! Thanks!!


EDIT: TO ANSWER THIS QUESTION, PLEASE USE KRISTY'S LINK BELOW! THANKS!


Are there figures that I can put in a series to be plotted on a line graph that won't appear in the graph? As an example, let's say that I have cells A1:A10 as consecutive dates, and cells B1:B10 as car sales on those dates. My graph includes ranges for both series down to row 10. I'd like to have an if/then statement for each cell, which in some circumstances only the first five days are plotted (without having to reset the range included on my chart for each series) and the others ignored by the graph; i.e., don't show up as zeros (which is what happens when I have the result as "" to show a blank).

Any thoughts are appreciated.


I have a chart with data and need to add a trendline for part of that data.

The x-axis contains dates from 2007 to present.
The primary y-axis contains measurements in feet (scale ten to zero feet going up) plotted as a line graph.
The secondary y-axis contains measurements in feet (scale zero to ~five feet going up) plotted as a column graph.

I have no flexibility on changing the graph types.

What I would like to be able to do is add a trendline for the data on the secondary y-axis from 2009 to current. I've looked around at linest (don't completely understand that yet) and have tried adding a third series for just the data that I want, but I can't select that series to add the trendline (and don't want that series to show up in the legend).

Any suggestions?


Hi folks,

I've found it easy with the chart wizard to produce a line graph showing a daily balance over time. The y axis is nicely labelled so I can see the values of the points on the graph. What I can't seem to do is to find a way of being able to see when these points occur i.e. to put the relevant dates on the x axis. I need to be able at least to show months and ideally, to be able to see the date when I mouse-over the line. In the table, the corresponding dates are in the adjacent column to my balance column.

Regards,
Gareth


Hi everyone!
This is probably a really simple question, but I am quite new to excel and am trying to teach myself.
I have a data set that I want to graph as a scatter plot. There are three data series of values that have to be plotted on the x axis and a set of position values for the y axis. I have already graphed each sample individually, but now I want to combine similar samples into one graph.
I want to use the average of 4 x axis values for each position on the y-axis. They are all listed in columns (eg/ position, data series 1, data series 2, data series 3). I am currently trying to do this by filtering the data by position and manually taking the average of each of the data series for each position, then listing it in a seperate spreadsheet. But this is very labour intensive (I have about 60 different position values). It occured to me that there MUST be an easier way to do this!
I would really appreciate any help at all.
Thanks very much.
mosquito


Hi,
I want to graph the temperature of water samples taken approx. once a week (but not exactly) during one year, hence, I do not want the data points to be distributed evenly in the x-direction Moreover I would like the dates (or months) to be shown in the x-axel (not as number 0-70 as in the upper graph in the attached file).

In the attached worksheet I have made two examples (one point graph and one line graph), neither of them satisfy my needs: The line graph looks ok but as I understand it is not possible to have data points unevenly distributed in this mode. The point graph also seems to distribute the points evenly?!, and I just cant the dates displayed correctly in this mode.

I use Excel 07 and I am highly grateful for any assistance

Best regards
Marco


Hello,

I am having trouble trying to automate the following graph (see attachment). Basically, there is a graph with mean scores plotted on it. I have a table of the mean score for each participant that completed the survey. Currently I manually plot each one on a graph.

What I want to do is have the score automatically plotted on a x-axis graph - with duplicate scores (x's) placed above each other. I have tried to create a chart doing this, but am unable to get it to look similar to how the graph was created manually (the color of the data points to be consistent with manual layout AND automating the y-axis calculations).

Any suggestions??

Thanks in advance!
Sarah


I feel like a total noob

I'm trying to plot a graph with 3 series on y-axis, and a value x-axis (i mean, not category). One of the series is very different in scale, so it needs a second y-axis to the right of the graph. However, i can't seem to get both a second y-axis and value-type x-axis.

I can get the series right when i select multiple axis graph, but then the x-axis assumes the category view, which doesn't line with the plot points at all..

Any help is greatly appreciated, thanks in advance.


I'm trying to graph the 2 series below on a Line graph chart using Excel. The problem is I am trying to use an x- scale starting on 3/21 and ending on 4/2.

So I want Series on to graph 3228.8356 on the 3/21/08 x-axis point and 3229.3098 on the 3/23/08 axis point. Is this possible? Is it also possible to have all the Series 1 points connected on a line graph.

Series 2 presents another problem: The data isn't taken on the same days all the time. IE it has data taken on 3/24 whereas series 1 did not.

Any tips to accomplish this? I've spent a large amount of time on this trying to figure it out.

SERIES 1
3/21/08 3228.8356
3/23/08 3229.3098
3/25/08 3227.8971
3/27/08 3226.7427
3/29/08 3228.3647
3/31/08 3227.1972
4/2/08 3227.05

SERIES 2
3/21/08 3230.9656
3/23/08 3231.8488
3/24/08 3235.2461
3/27/08 3234.8028
3/29/08 3234.8922
3/31/08 3234.8061
4/2/08 3232.7155


I am trying to create a dynamic graph where the user can select which series they want to be plotted for the primary and secondary axis (line graph), and what they want the X-axis to be. I am importing data from another source (that code is finished) and have a sheet with a variable number of columns depending on what was imported. I want a checkbox for primary and secondary y-axis series, and a drop-down menu for what the X-axis is.

The method I use right now is to create a second table of data that the graph comes from and create a checkbox based on turning those columns from values to NA()...when I do that it includes all of the series (anywhere from 5-15) on my legend though and I would really like to just include what I am graphing in the legend. I can only figure this out for the primary axis and don't know how to do the secondary axis (there may not always be one).


I can attach a sample worksheet if that would help, but it will basically be a blank graph and big data table.

Things I can't figure out:
-Selecting the x-axis from a drop-down
-Selecting which series to include on a secondary axis
-How to select the series without including every possible series in the legend


Any suggestions or help would be appreciated!

I have a small data problem - I need to create a count or sum forumla which will allow me to select a reporting period (Q1,Q2,Q3,Q4) then it will look up a specific department and tell me how many elements are Red, in the next formula I will have green and then amber.

Data
Column A - Sales,Sales,Retail,Internal,Sales,Retail
Column B (Q1), Red, Amber, Red, Red, Green, Green
Column C (Q2), Green, Amber, Red, Green, Green, Amber
Column D (Q3), Red, Amber, Red, Green, Anber, Green
Column E (Q4(, Green, Green, Red,Green, Green

Basically I have a report that allows be to select the reporting period (Q1-Q4) once chosen I then provides a summary per section so the results would be if I chose Q1

Section - Green, Amber, Red
Sales 1 1 1
Internal 0 0 1
Retail 1 0 1

I tried this =COUNTIF(INDEX(ragy_data,0,MATCH($J$1,periods,0)),"Green")
Where ragy_data B1 to E6 and periods are Q1-Q4 - this worked great and when I chose a period I would get the TOTAL green but I need to only show the green per section.

I think I need something like this
=SUM(IF(!$A$1:$A$6="Retail",IF(INDEX(ragy_data,0,MATCH($J$1,periods,0)),"Green"))) but this does not work


I have a macro to create a stacked bar graph and now the last step is to label the graph but with labels from other cells than what is in the bargraphs linked data. I have the following code that will label data points of my stacked bar graph with the cells I want to use but I would like to set up a macro to loop through the data points and add the labels from the following ranges. Then the macro can set the number of data points just as it determines the number of series.

any help would be greatly appreciated or even a generally direction for me to look into. I have put a peice of the code below so that you can see the pattern (series 1, 3, 5...). Thank you again for your time.


Code:

 
Sub LabelSeries1_To_17()
  Sheets("Graph").ChartObjects.Activate
    With ActiveChart
      .SetElement (msoElementDataLabelNone)  'clears chart labels
'========================================= start series 1 =================================================
'====Monday - (series 1) ====
      .SeriesCollection(1).Points(1).ApplyDataLabels
      .SeriesCollection(1).Points(1).DataLabel.Text = Sheets("Graph").Range("d48").Text _
      & Sheets("Graph").Range("e48").Text
      .SeriesCollection(1).Points(2).ApplyDataLabels
      .SeriesCollection(1).Points(2).DataLabel.Text = Sheets("Graph").Range("d56").Text _
      & Sheets("Graph").Range("e56").Text
'====Tuesday - (series 1)====
      .SeriesCollection(1).Points(4).ApplyDataLabels
      .SeriesCollection(1).Points(4).DataLabel.Text = Sheets("Graph").Range("d49").Text _
      & Sheets("Graph").Range("e49").Text
      .SeriesCollection(1).Points(5).ApplyDataLabels
      .SeriesCollection(1).Points(5).DataLabel.Text = Sheets("Graph").Range("d57").Text _
      & Sheets("Graph").Range("e57").Text
'====Wednesday - (series 1)====
      .SeriesCollection(1).Points(7).ApplyDataLabels
      .SeriesCollection(1).Points(7).DataLabel.Text = Sheets("Graph").Range("d50").Text _
      & Sheets("Graph").Range("e50").Text
      .SeriesCollection(1).Points(8).ApplyDataLabels
      .SeriesCollection(1).Points(8).DataLabel.Text = Sheets("Graph").Range("d58").Text _
      & Sheets("Graph").Range("e58").Text
'====Thursday - (series 1)====
      .SeriesCollection(1).Points(10).ApplyDataLabels
      .SeriesCollection(1).Points(10).DataLabel.Text = Sheets("Graph").Range("d51").Text _
      & Sheets("Graph").Range("e51").Text
      .SeriesCollection(1).Points(11).ApplyDataLabels
      .SeriesCollection(1).Points(11).DataLabel.Text = Sheets("Graph").Range("d59").Text _
      & Sheets("Graph").Range("e59").Text
'====Friday - (series 1)====
      .SeriesCollection(1).Points(13).ApplyDataLabels
      .SeriesCollection(1).Points(13).DataLabel.Text = Sheets("Graph").Range("d52").Text _
      & Sheets("Graph").Range("e52").Text
      .SeriesCollection(1).Points(14).ApplyDataLabels
      .SeriesCollection(1).Points(14).DataLabel.Text = Sheets("Graph").Range("d60").Text _
      & Sheets("Graph").Range("e60").Text
'====Saturday - (series 1)====
      .SeriesCollection(1).Points(16).ApplyDataLabels
      .SeriesCollection(1).Points(16).DataLabel.Text = Sheets("Graph").Range("d53").Text _
      & Sheets("Graph").Range("e53").Text
      .SeriesCollection(1).Points(17).ApplyDataLabels
      .SeriesCollection(1).Points(17).DataLabel.Text = Sheets("Graph").Range("d61").Text _
      & Sheets("Graph").Range("e61").Text
'====Sunday - (series 1)====
      .SeriesCollection(1).Points(19).ApplyDataLabels
      .SeriesCollection(1).Points(19).DataLabel.Text = Sheets("Graph").Range("d54").Text _
      & Sheets("Graph").Range("e54").Text
      .SeriesCollection(1).Points(20).ApplyDataLabels
      .SeriesCollection(1).Points(20).DataLabel.Text = Sheets("Graph").Range("d62").Text _
      & Sheets("Graph").Range("e62").Text
'========================================= start series 3 =================================================
'====Monday - (series 3)====
      .SeriesCollection(3).Points(1).ApplyDataLabels
      .SeriesCollection(3).Points(1).DataLabel.Text = Sheets("Graph").Range("f48").Text _
      & Sheets("Graph").Range("g48").Text
      .SeriesCollection(3).Points(2).ApplyDataLabels
      .SeriesCollection(3).Points(2).DataLabel.Text = Sheets("Graph").Range("f56").Text _
      & Sheets("Graph").Range("g56").Text
'====Tuesday - (series 3)====
      .SeriesCollection(3).Points(4).ApplyDataLabels
      .SeriesCollection(3).Points(4).DataLabel.Text = Sheets("Graph").Range("f49").Text _
      & Sheets("Graph").Range("g49").Text
      .SeriesCollection(3).Points(5).ApplyDataLabels
      .SeriesCollection(3).Points(5).DataLabel.Text = Sheets("Graph").Range("f57").Text _
      & Sheets("Graph").Range("g57").Text
'====Wednesday - (series 3)====
      .SeriesCollection(3).Points(7).ApplyDataLabels
      .SeriesCollection(3).Points(7).DataLabel.Text = Sheets("Graph").Range("f50").Text _
      & Sheets("Graph").Range("g50").Text
      .SeriesCollection(3).Points(8).ApplyDataLabels
      .SeriesCollection(3).Points(8).DataLabel.Text = Sheets("Graph").Range("f58").Text _
      & Sheets("Graph").Range("g58").Text
'====Thursday - (series 3)====
      .SeriesCollection(3).Points(10).ApplyDataLabels
      .SeriesCollection(3).Points(10).DataLabel.Text = Sheets("Graph").Range("f51").Text _
      & Sheets("Graph").Range("g51").Text
      .SeriesCollection(3).Points(11).ApplyDataLabels
      .SeriesCollection(3).Points(11).DataLabel.Text = Sheets("Graph").Range("f59").Text _
      & Sheets("Graph").Range("g59").Text
'====Friday - (series 3)====
      .SeriesCollection(3).Points(13).ApplyDataLabels
      .SeriesCollection(3).Points(13).DataLabel.Text = Sheets("Graph").Range("f52").Text _
      & Sheets("Graph").Range("g52").Text
      .SeriesCollection(3).Points(14).ApplyDataLabels
      .SeriesCollection(3).Points(14).DataLabel.Text = Sheets("Graph").Range("f60").Text _
      & Sheets("Graph").Range("g60").Text
'====Saturday - (series 3)====
      .SeriesCollection(3).Points(16).ApplyDataLabels
      .SeriesCollection(3).Points(16).DataLabel.Text = Sheets("Graph").Range("f53").Text _
      & Sheets("Graph").Range("g53").Text
      .SeriesCollection(3).Points(17).ApplyDataLabels
      .SeriesCollection(3).Points(17).DataLabel.Text = Sheets("Graph").Range("f61").Text _
      & Sheets("Graph").Range("g61").Text
'====Sunday - (series 3)====
      .SeriesCollection(3).Points(19).ApplyDataLabels
      .SeriesCollection(3).Points(19).DataLabel.Text = Sheets("Graph").Range("f54").Text _
      & Sheets("Graph").Range("g54").Text
      .SeriesCollection(3).Points(20).ApplyDataLabels
      .SeriesCollection(3).Points(20).DataLabel.Text = Sheets("Graph").Range("f62").Text _
      & Sheets("Graph").Range("g62").Text
end with
end sub





I'm have a Stacked Bar graph using this data.

MW
Wind 27
Hydro 3,579
Nuclear 12,070
Coal 32,875
Renew 103
Gas 45,443
LOil 351

When I set this up in Excel, it looks fine, the data is set to columns and the x-axis gives accurate representation and scale from 0 to 10,000 MW. I have a line graph that I'm attempting to overlay in front of the stacked bar graph, but I can't get the axes setup correctly. I'm trying to get the Marginal Cost on the right axis and have the points plotted based on the Cumulative Capacity, but show only the axis used by the bar chart and still have it plotted correctly along the bottom axis. Btw, I have 1,000 data points for this line. Any thoughts?

Cumulative
Capacity
MW Total Marginal Cost $/MWh
0.12 0
0.24 0
0.243 0
0.245 0
0.365 0
4.985 0
11.705 0
17.105 0
27.185 0
38.585 0.48
54.185 0.48
... ...
94448.409 358.8300174


Help?!? I am having a problem generating a graph in Excel similar to one done by SPSS. It is a line graph with 3 series of data (three lines), 4 age categories on the X axis and percentages (75-98) on the Y axis. I am trying to get the Excel graph to offset the lines slightly to the right the way that the SPSS generated graph does, so that the data points for a given value on the X axis are not mapped on top of one another. This is a problem when, say for the Y axis category age 6, the three points in each data series are all very close (e.g. 78%, 79%, 78%). I have tried formatting the X axis. I have tried to format an individual data series. I have tried to figure out how to make subcategories along the x axis. All to no avail. Any ideas? I also tried to cut and paste the line graph here so you coudl see what I mean, but had no luck with that. Did I describe clearly enough what I want it to look like? Thx, elt


This should be easy to do but I can't figure it out.

I have two sets of data I'm trying to put in a line graph. They both have the same x-axis which is "date". And both sets of data even have the same values for date.

The y-axis is temperature and this is the only point in which they differ. However, when I graph in a series, only one set of data has the proper y-axis values. The other one is on the graph but it is not plotted at the right temperature points. How do I get both graphs to graph with the right y-axis values?

I have attached the chart.


Hi Guys

Is it possible for a single series line graph to have multiple coulours?

I have attached a workbook, showing a chart. I want the line graph to be green from the 10/03/2010 and anything previous to that date should be red.


Hi,

This may seem simple to you guys but im struggling here!

Im creating a line graph which include 5 series of data. However my x-axis (independant variable) data is not in a consistant flow, eg, not every 2 units such as 2,4,6,8,10 etc... but it is random such as 7,12,17,20,23,27. Now my 5 subjects were measured at these 6 independant variables.

The problem i'm having is that the x-axis is a scale measure so the distance apart need to measured and not catagorised. And when i create my line graph, no lines are produced betweem the points because i dont have any data for each indervidual x-axis notation, just for the 6 i have above.

So therefore i need to somehow make the graph ignore where there is no data and just create the graph with the line where it has the data.


If this makes sense to anyone out there and could help me, it would be much aprreciated!!

Cheers

Jimbob