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?
You've got a couple different things making this difficult for you.
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)).
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:
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.
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.
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.
Sample Excel file: Multiscale Sample
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.
