microsoft office - Multiple Y-axis labels in Excel 2010 line chart

16
2014-04
  • MikkoP

    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

  • Answers
  • dav

    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

  • pnuts

    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.


  • Related Question

    Excel bar chart - how to make axis as time scale
  • Stan

    My data has column 1 = date (date format), column 2 = quantity (number format). The data looks like:

    date       qty
    1/3/2010   5
    2/3/2010   2
    2/4/2010   3
    2/9/2010   1
    4/13/2010  5
    5/31/2010  5
    6/1/2010   1
    

    Then I select these 2 columns and make a bar chart, column1 = x axis, column2 = y axis. I got below chart:

    alt text

    How can I make the date axis have a real time scale, meaning every month should have the same distance?


  • Related Answers
  • Mike Fitzpatrick

    The most simple way to do it is with an Scatter (X-Y) chart but this is a line chart and won't display the bars you want.

    To do it with bars you need to create a pivot table with date in the row labels and sum of qty as the values. Note this will only work if you only have one sample per date.

    Set grouping on the row labels to 'Days' and 'Years' and then go in to field settings and turn on 'Show items with no data'.

    Then create a pivot chart using the bar chart style. You'll get a bar chart with 365 days and the samples spaced correctly.

  • RocketGoal

    I know you've accepted an answer, but...

    You could try some in cell charts. Count the amounts your dates appear in your range and then use the REPT() function to add | within a cell. Here's a link to a website that shows good ways of using in-cell charts.www.chandoo.org/in-cell-charts

    Also if you make your range that it counts dynamic, using OFFSET(), then the chart will update as you receive more dates.

    ...possible issue in that you'll need to find all the unique dates - but this can be done quite easily with a bit of snooping around sites like these..