1

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.

6
  • In the legend, you could say in 10s, 100s, millions, etc next to the line?
    – soandos
    Jul 24, 2011 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. Jul 24, 2011 at 4:19
  • Im saying stick the scale on the ledgend not the axes
    – soandos
    Jul 24, 2011 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. Jul 24, 2011 at 4:28
  • No, i mean write (in thousands) after the line
    – soandos
    Jul 24, 2011 at 4:53

2 Answers 2

1

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.

1
  • That would work. I was hoping for the ability to have multiple scales, or have the scale proportionate. Jul 25, 2011 at 4:30
1

Starting in Excel 2013, which was not available at the time of your question, Excel allows you to use values from cells as data labels. So make your chart with the percentages, add data labels, and format the data labels to use values from the actual value ranges.

Since the beginning of time (Excel 97 or even earlier) there has been a non-commercial add-in for Excel called the Chart Labeler, written by Rob Bovey of Application Professionals. It has been providing this same labeling flexibility that Excel finally introduced in Excel 2013.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .