Microsoft Excel Graph Editing Help (XP)

Kimihiro

I'm working on a graph that involves two airplanes, the Airbus A380 and the Boeing 747-400ER, and the amount of fuel used as they travel on a trip. I need to put a dotted line where their fuel tanks would be fully empty, but I must admit that I have no idea how to accomplish this with this program.

For example, the Airbus A380 runs out of fuel when it has used up 223 tonnes. This means I need a straight, dotted line *the entire way across the graph* at y = 223.

I have tried making an entirely new line, but I need it to extend all the way across and it always seems to stop an inch before the right side, which irritates me to no end.

Can anyone give me some much appreciated help here?

Thanks.

whuppins

Can you post an image of your graph the way you have it set up currently, and also the worksheet cells where your data is being pulled from? This is a very simple problem, but your data could be formatted in a hundred different ways, so it's kind of hard to give an answer that'll work for you without seeing how you have everything arranged so far.

Serpent

just make a new line from x=-1000 to x= huge number. set the axis bounds to be x=0 to x=250 or so.

done!

Kimihiro

Sure, here's the data:

Airbus A380
Time (h) Fuel Consumed (tonnes)
0.333 2
16.711 176
17.211 179

Boeing 747-400ER
Time (h) Fuel Consumed (tonnes)
0.333 2
12.558 112
13.058 115

And here's the graph I currently have:

whuppins

How does this work for you?



If that looks like what you need, let me know and I'll explain how to get it to look that way.

Short answer: Added a dummy series, as Serpent said, and did some formatting work.

Kimihiro

Aaahahahahahaha, that's perfect.

Mind showing me how to do it?

Thanks for all the help, by the way.

whuppins

I think it's basically what you were initially trying to do, but the reason that your line always came up short was (I'm guessing here) because you had Excel auto-adjust your graph's maximum X value. So when you added on a line that stretched all the way to, say, 20 hours, Excel would bump up your max X to, say, 25 hours, to give it a little buffer between the line and the end of the graph. In your case, you want the line to run all the way to the end, so you have to disable the auto-adjusting that Excel's doing. It will also help to use error bars instead of plotting an actual line, to make labeling easier.

It sounds like you have a pretty good handle on how to work with charts in Excel, so a lot of this info may be redundant to you. Sorry, just trying to cover all the bases. Also, it looks like you're using an XY scatter chart with connecting lines. If you're using some other type of chart, the specifics on how to add series and such will be wrong, but the basic idea will remain the same.

Hard-code in a maximum X value

Right-click on your X-axis and choose "Format Axis...". On the "Scale" tab, un-check the box "Maximum:" and type in an appropriate value.

Add a third set of data

Go to your worksheet and add a third set of data with only one point: 223 tons at x hours where x is the maximum value you typed into the box above. Now right-click the chart itself and click "Source Data...". On the "Series" tab, click "Add" to add a third series (you should already have two series here, one for the Boeing and one for the Airbus). With the third series highlighted, click on the buttons in the "X Values" and "Y Values" boxes and assign the x and 223 cells accordingly. Also, fill in the "Name" box with whatever you want the line's label to be. You can go for comedic effect like me, or just simply "223".

Format the series

OK your changes and you should have your new point plotted at the very edge of your graph. Right-click on the point and choose "Format Data Series...". On the "X Error Bars" tab, choose "Minus", set the error amount to "Percentage", and fill in 100%. On the "Data Labels" tab, check "Series name" to have the name you typed in above appear next to your point. On the "Patterns" tab, select "None" for Marker so there are no dots interfering with your error bar. For Line, set the color and style (e.g., dotted) that you want the line to be, even though this isn't where your line will actually be formatted. It's complicated, it helps the legend make sense; I'll explain later.

OK those changes and your error bar will be plotted. Right-click on it and choose "Format Error Bars..." to change it to a dotted style or whatever color. If you're using a legend, make sure you format your error bar in the same way your formatted the data series itself, above.

The series name should appear to the right of your point, just off the right edge of the plot area as in my example. If you want to change its orientation, you can right-click on the name itself, choose "Format Data Labels...", and adjust "Label Position" on the "Alignment" tab.

Other stuff

If you want your series name to appear off the left side of the chart instead of the right, you'll have to change 3 things: First, plot your point at 0 hours instead of the max. Second, make your X error bar the "Plus" kind instead of the "Minus" and change the amount to a "Fixed amount" equal to the max value, x, from before. Third, change the Label Position, as described above, to "Left".

Also, the only drawback to this solution is that you have to put up with a third series appearing in your legend if you're using one. It's not a super-big deal, and if you give it a reasonable name and format your data series the same as your error bar, it should make total sense to whoever's looking at it. There's just no way to remove it from the legend entirely and keep the other two series.


TL;DR: Disable auto-scaling of your chart's maximum X value. Add an additional data series to plot a single point at (<max X>,223). Use error bars to draw the line. Format your data set/error bars appropriately so your label and legend make sense.