I have a table containing cycling data. Date, distance, time, calories burned, max heart rate and average heart rate. They all have different value ranges. Excel scales the chart to show the calories column correctly, but for example, the time line isn't even visible in that range.

The chart should present that with practice the heart rate drops as well as time and calories burned because you get into better shape.

How do I get them their own Y-axis labels to make it readable?

enter image description here

enter image description here


You've got a couple different things making this difficult for you.

  1. The most obvious, is how Excel handles times. Since you're talking about hours and have your time formatted as time, Excel is reading this as a fraction of a day. In Excel, dates are handled as serial numbers, with a single day having the value 1. So an hour has a value of .0416... This means any time value is going to be interpreted/graphed as a small decimal. So, you'll either need to accomodate their small values, or convert the hours/minutes to a integer/decimal value yourself (e.g. 00:54:05 (hh:mm:ss) = 0.0376 (Excel serial) = 0.9014 (hours)).

  2. The other problem is what your question centers around, and it's Excel behaving properly (believe it or not). Since the values of your categories Distance, Duration, Energy and Heart Rate all have different units, graphing them on a single axis will lead to the distortions you noted. Adding a second axis (as pnuts accurately described) doesn't really help your situation, other than allowing a second "scale" to be superimposed on your first. However, a second (or third or fourth) scale doesn't solve the problem that your putting disparate units on a chart in the same "space" (vertical area of the plot area).

So, what to do? You have three options to get what you're looking for:

  1. Adopt a scale that allows all your units to be plotted in their native scale. Similar to the chart you already have, all you need to do is convert your vertical axis to a log scale. This will allow all of your values to be charted in a much more observable size, but the down side is that vertical differences will be muted at the upper end of each base.

    Log Scale

  2. Use small multiples. This allows you to create a number of charts that are identical in all aspects, except Y axis unit/scale. This will allow you to see each category in an environment accurate to it, and relatable to the others. The downside is you'll be making several (four or five) charts for the same data.

    Small Multi

  3. Normalize the data into the same unit. In your example, use the starting values (2013-06-03) as a 100% value, and convert all of your other values to a percantage of the starting value, then plot the percentages all on the same scale. The downside is you lose the original unit values.

    Normal %

Sample Excel file: Multiscale Sample

  • Thanks for your answer. Could you post the files so I could have a look, please?
    – MikkoP
    May 31 '13 at 13:56
  • Thanks for the file. I think the normalize method is good enough but it would be nice to have values correctly on the left side. Can I manually add values on the Y-axis?
    – MikkoP
    Jun 1 '13 at 16:28
  • Sure, easiest way is to add another data series and change it's chart type to XY/Scatter Chart. Then you can added points at 0 X-axis and appropriate Y axis for each category's values and label the points. You can add as many series as you have value categories (e.g. distance, energy, heart rate...)
    – dav
    Jun 1 '13 at 20:49
  • Log would work perfectly except that it can't plot zeros (and seems to stop plotting once a data set has a zero in it) Sep 6 '13 at 5:57

You can add a secondary Y axis as described here:

In a chart, click the data series that you want to plot on a secondary vertical axis, or do the following to select the data series from a list of chart elements:

Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the data series that you want to plot along a secondary vertical axis.

On the Format tab, in the Current Selection group, click Format Selection.

The Format Data Series dialog box is displayed.

Note If a different dialog box is displayed, repeat step 1 and make sure that you select a data series in the chart.

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