Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have multiple series of data that are on different scales and I would like to graph all of them in the same line chart based on percentage, but I want to label the points with the actual values. One series has values in 100's, another in 10's and another in 100,000's. If I chart them all together then only the series with the largest values shows up.

I added a percent column for each series, and then charted that. This gives me the lines I expect, but there is no way I could see to have the points show the actual values, and not the percentage value. So instead of a point showing 15% I want it to show $500.

What I would expect is the ability to plot each series on its own axis, but then remove the axis label and superimpose them all. Or perhaps tell Excel to plot based on percentage of whole.

I tried Stacked and 100% Stacked, but those didn't work. I could manually label each point, but that defeats the purpose of using a Excel to draw my graph.

I am using Excel 2010, but if there is another common or free tool (Numbers, etc.) that would work better, please let me know.

This is what I want it to look like, but with the series tied to the actual values instead of the percentages so I can show data labels.

enter image description here

Update: The values shown in the 3rd position (starts with the left end of the orange line at the top and going down) are 107,253 units, 22 units, 317 units, $61 million, 142 units, $130 million, and $798 million. So there is no natural factoring, grouping, etc. The interesting relationship is how they compare to the other values in the same series. That is what is depicted correctly now in the lines.

share|improve this question
    
In the legend, you could say in 10s, 100s, millions, etc next to the line? –  soandos Jul 24 '11 at 4:09
    
@Soandos I could do some sort of reduction but then I am showing 10th of a unit in one series, and another would be in $10,000 of dollars. So I end up with odd groupings. –  Jim McKeeth Jul 24 '11 at 4:19
    
Im saying stick the scale on the ledgend not the axes –  soandos Jul 24 '11 at 4:25
    
@Soandos: If I just stick the sales in the legend then the series with values in to 100,000 will be the only one showing fluctuation as the rest will be flat along the bottom of the graph since their maximum value is not enough to even be a blip at that scale. –  Jim McKeeth Jul 24 '11 at 4:28
    
No, i mean write (in thousands) after the line –  soandos Jul 24 '11 at 4:53
show 1 more comment

1 Answer 1

up vote 1 down vote accepted

You can achieve something like this using a data table in the graph. enter image description here

The trick here is to add your original data to the graph as well as your percentages. You then change the format of the original data so that the graph doesn't show up and change the axis of the original data to be on a secondary axis which is also formatted so that it doesn't show up. With some suitable names for the data you can present your original data values. The major problem I can see with this approach is that the data table would get very large.

If you know that the number of data points in a series will stay the same a slight variation would be to put the graph on a worksheet and then create the data table in the worksheet just below the graph. That way you have more control of the formatting of the table as well as what data you actually show. There are a few ever more complex variations on this approach as well that might get you something more suitable.

The final thing you might want to take a look at it using Python and Matplotlib. It is free and can produce some really nice looking graphs but it is a programming language so may not be what you are looking for.

share|improve this answer
    
That would work. I was hoping for the ability to have multiple scales, or have the scale proportionate. –  Jim McKeeth Jul 25 '11 at 4:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.