Excel chart question-I have the data in a spreadsheet, I go F11 and get lines for my price points but how do I scale the lines?

basically I have one series where the price declines 35% so that one shows nicely. The others that decline 15% or so are pretty much straight lines so I think that it's a problem of scale-I could convert everything to % terms but it won't look visually as good as having dollar terms-can you help me?

Chart 3 is the one I'm having the problem with
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

There is no chart 3 in the attached file. I can't match your description to any of the other two charts. It's not clear what you are asking.

cheers, teylyn
johngee123Author Commented:

I forgot to save the latest version of the file, this is the one with chart3


Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
OK. I see the chart now.

F11 will insert a clustered column chart. With so many data point that chart type is not a good visualisation.  I'm not sure what you mean with "scaling the lines". Can you explain that a bit more?
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

johngee123Author Commented:
I have the WTI line going down from 97.3 to 59.23 =minus 39.13%

AMLP has gone from 18.95 to 17.2 =minus 9.23%
CBA from 19.2 to 16.2 = minus 15.63%
CEM from 29,76 to 26.5 = minus 10.95%
CEN from 20.04 to 16,7= minus 16.67%
DSE from 19.85 to 14.73= minus 25.79%
EMO from 25.17 to 22.09= minus 12,24%

Visually in Chart 3 one can see that WTI has gone down and then up. All the others look like a straight line,

looking at the graph, the viewer would have no idea (without looking at Sheet1) that DSE had dropped 25.79% while AMLP had only declined 9.23%.

Therefore there is something wrong with Chart 3. I think the problem is that I am using the scale of WTI i.e the drop from 97.3 to 59.23 for the others. The others are priced around 20 so it seems to me that the chart lines are being compressed by a factor of 5 to 1. So, what I need is say a scale of 100-50 on the left and a scale of 20 to 10 on the right
keeping the dates along the bottom. I have no idea how to do this in Excel. I need the WTI using the left scale as is and the six others using a 20 to 10 scale on the right.

I can see from your comments that you have no concept of stock charts-if you still do not understand please feel free to pass this on to someone else at Experts Exchange

Kind regards

John Ginsbury
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You're right, I don't do much with stock charts.

What you describe is easily achieved by placing the series 2 to 7 on the secondary axis. Right-click Series2, then click Format Series and in the Series Options select "Secondary Axis".

Repeat for the other series.

The difficulty for the reader of that chart is to recognize which series belongs to which Y axis. You may need to add explanations or text boxes to the chart to make that clear.  I'll leave that up to you. Attached please find the chart with Series 2 to 7 on the secondary axis. As a suggestion to avoid the gaps I've also applied the setting to connect the lines where data points are missing.

cheers, teylyn
johngee123Author Commented:
Thanks Teylyn,

 When I right click Series2 I don’t get Format Series, I get Format Cells and no Secondary Axis. Is there another way of doing it, I presume that I am using a different version of Excel than you

How do I add text boxes to the chart?
Sorry but this is why I contacted the Experts, I can almost never find anything to help me in Microsoft Help but I actually did manage to stumble on the F11 function and groped my way forward from that.
Please just consider that I am a baby lost in the city for Microsoft charts
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You need to right-click the series line in the chart, not the cell of the data source.

To add a text box, click Insert > text box

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.