Step Chart Without Risers

In Line Chart vs. Step Chart I compared step charts to line charts, and presented a quick way to prepare data for making a step chart. My insightful colleague Derek pointed out that a similar chart with the horizontal segments only, and with vertical segments (risers) hidden, may be a more effective display method, because the risers may attract undue attention.

Step Chart - USPS Data

Derek complained about the relative complexity of his chart: it used a line chart series to provide the nice date scale category axis, but it required an XY series so he could use X error bars for the horizontal segments. Too many series for his, and my, liking. As it turns out, the protocol to prepare line chart data for this variation on a step chart is really not more complicated than for the regular step chart.

The sequence of ranges below shows how to prepare the data in about three minutes. Start by copying the original data (yellow), and pasting it below the original data, once with only the dates and blanks for rates (orange), then both dates and rates (green), shown in the first block below. The second block shows four cells to be deleted: the first cell of the original dates, the first row of the intermediate date-only data, and the last cell of the original rates. Delete these cells, shifting cells up to fill the gap; the arrangement is shown in the third block. This arrangement is sufficient for creating a step chart if the X data are dates, because the line chart sorts the data internally before plotting it. However, to make the table more human-friendly, sort it by date, as shown in the fourth block below. Notice the sequence of date with old rate, date with blank rate, and date with new rate. The blank cell suppresses drawing of a line segment connecting the old and new rates.

Date Rate
14-Sep-75 $0.10
31-Dec-75 $0.13
29-May-78 $0.15
22-Mar-81 $0.18
1-Nov-81 $0.20
17-Feb-85 $0.22
3-Apr-88 $0.25
3-Feb-91 $0.29
1-Jan-95 $0.32
10-Jan-99 $0.33
7-Jan-01 $0.34
30-Jun-02 $0.37
8-Jan-06 $0.39
14-May-07 $0.41
12-May-08 $0.42
14-Sep-75  
31-Dec-75  
29-May-78  
22-Mar-81  
1-Nov-81  
17-Feb-85  
3-Apr-88  
3-Feb-91  
1-Jan-95  
10-Jan-99  
7-Jan-01  
30-Jun-02  
8-Jan-06  
14-May-07  
12-May-08  
14-Sep-75 $0.10
31-Dec-75 $0.13
29-May-78 $0.15
22-Mar-81 $0.18
1-Nov-81 $0.20
17-Feb-85 $0.22
3-Apr-88 $0.25
3-Feb-91 $0.29
1-Jan-95 $0.32
10-Jan-99 $0.33
7-Jan-01 $0.34
30-Jun-02 $0.37
8-Jan-06 $0.39
14-May-07 $0.41
12-May-08 $0.42
   
Date Rate
14-Sep-75 $0.10
31-Dec-75 $0.13
29-May-78 $0.15
22-Mar-81 $0.18
1-Nov-81 $0.20
17-Feb-85 $0.22
3-Apr-88 $0.25
3-Feb-91 $0.29
1-Jan-95 $0.32
10-Jan-99 $0.33
7-Jan-01 $0.34
30-Jun-02 $0.37
8-Jan-06 $0.39
14-May-07 $0.41
12-May-08 $0.42
14-Sep-75  
31-Dec-75  
29-May-78  
22-Mar-81  
1-Nov-81  
17-Feb-85  
3-Apr-88  
3-Feb-91  
1-Jan-95  
10-Jan-99  
7-Jan-01  
30-Jun-02  
8-Jan-06  
14-May-07  
12-May-08  
14-Sep-75 $0.10
31-Dec-75 $0.13
29-May-78 $0.15
22-Mar-81 $0.18
1-Nov-81 $0.20
17-Feb-85 $0.22
3-Apr-88 $0.25
3-Feb-91 $0.29
1-Jan-95 $0.32
10-Jan-99 $0.33
7-Jan-01 $0.34
30-Jun-02 $0.37
8-Jan-06 $0.39
14-May-07 $0.41
12-May-08 $0.42
   
Date Rate
31-Dec-75 $0.10
29-May-78 $0.13
22-Mar-81 $0.15
1-Nov-81 $0.18
17-Feb-85 $0.20
3-Apr-88 $0.22
3-Feb-91 $0.25
1-Jan-95 $0.29
10-Jan-99 $0.32
7-Jan-01 $0.33
30-Jun-02 $0.34
8-Jan-06 $0.37
14-May-07 $0.39
12-May-08 $0.41
31-Dec-75  
29-May-78  
22-Mar-81  
1-Nov-81  
17-Feb-85  
3-Apr-88  
3-Feb-91  
1-Jan-95  
10-Jan-99  
7-Jan-01  
30-Jun-02  
8-Jan-06  
14-May-07  
12-May-08  
14-Sep-75 $0.10
31-Dec-75 $0.13
29-May-78 $0.15
22-Mar-81 $0.18
1-Nov-81 $0.20
17-Feb-85 $0.22
3-Apr-88 $0.25
3-Feb-91 $0.29
1-Jan-95 $0.32
10-Jan-99 $0.33
7-Jan-01 $0.34
30-Jun-02 $0.37
8-Jan-06 $0.39
14-May-07 $0.41
12-May-08 $0.42
   
Date Rate
14-Sep-75 $0.10
31-Dec-75 $0.10
31-Dec-75  
31-Dec-75 $0.13
29-May-78 $0.13
29-May-78  
29-May-78 $0.15
22-Mar-81 $0.15
22-Mar-81  
22-Mar-81 $0.18
1-Nov-81 $0.18
1-Nov-81  
1-Nov-81 $0.20
17-Feb-85 $0.20
17-Feb-85  
17-Feb-85 $0.22
3-Apr-88 $0.22
3-Apr-88  
3-Apr-88 $0.25
3-Feb-91 $0.25
3-Feb-91  
3-Feb-91 $0.29
1-Jan-95 $0.29
1-Jan-95  
1-Jan-95 $0.32
10-Jan-99 $0.32
10-Jan-99  
10-Jan-99 $0.33
7-Jan-01 $0.33
7-Jan-01  
7-Jan-01 $0.34
30-Jun-02 $0.34
30-Jun-02  
30-Jun-02 $0.37
8-Jan-06 $0.37
8-Jan-06  
8-Jan-06 $0.39
14-May-07 $0.39
14-May-07  
14-May-07 $0.41
12-May-08 $0.41
12-May-08  
12-May-08 $0.42


To create a step chart, select the data (processed as in the fourth block above), and create a line chart using the lines without markers option.

 

Peltier Tech Charts for Excel

Comments

  1. It wasn’t x error bars; it was a proper scatter series, from a data block very like yours above. My mistake, that caused me to abandon the line series, was to have the gap row with a gap value, and a gap date. The line series then insisted on including the risers, forcing me to source a second series from the same data block.

    I went back and put dates in the gap lines, and the risers went away, making a scatter series unnecessary. Thanks for that.

    I would still want a second series for individually-formatted risers, although doubtless a macro can be written to do that on a point-by-point basis to a single series.

  2. Very Nice! The step graph clearly has affordances that lets it communicate way more information about this particular dataset. Getting rid of the risers was a thoughtful touch.

    I’m still worried about how the step chart would look if the data was much more erratic than gradual (i.e. lots of up and downs). Would it convey the trend just as clearly as a line chart then? Maybe I’ll just have to plot it myself and see how it looks.

    Also got around to looking around your website. Interesting stuff.

  3. demaws – “Maybe I’ll just have to plot it myself and see how it looks.”

    That’s exactly right. So much of charting is experimental. Partly to investigate relationships in the data, and partly to see how best to illustrate these relationships.

  4. demaws, sometimes the data becomes a story about how big the instantaneous rises and falls were, and when they occurred. When that happens, it may be better to have a graph that’s all risers and no steps, like a sort of time-lined waterfall graph. (a step graph without risers is a sort of quantitative Gantt chart)

    More subtle intermediate cases might benefit from risers and steps that have different formatting, as recommended in Edward Tufte’s 1983 classic The Visual Display of Quantitative Information. The last chapter I think.

  5. Jon,

    Thanks for your work on this.

    Have you ever tried using a scatter plot with the “-” symbol as the marker? If your x range is sufficiently dense, you can get decent-looking horizontal steps.

    This is an easy approach that I just used. The downside is that the steps might be slightly wider than they should be, but that’s not a problem for my current project.

    Best,

    Win

  6. Win –

    Yeah, I’ve tried that. I was never happy with how it looked. And deep down, I knew it wasn’t right.

  7. Thanks Jon. After playing with it some more, I found I’d rather use the error bars as well. Thanks for tip!

  8. i want Step Chart Without Risers view source code

  9. Vikas –

    You misunderstand. There is no source code. This is a manual technique that you have to apply yourself.

  10. Thanks for your guide. I have not succeeded in generating a step chart from the following data set during my practice but your instructions have widened my scope of working with MS Excel. After getting used to MS Excel 2003, I have upgraded to windows 7 and now having some a bit challenges in finding some functions. Your continued support will be highly appreciated.
    Thanks
    The data set

    x y
    1 4
    2 8
    3 12
    4 16
    5 20
    6 24
    7 28
    8 32
    9 36
    10 40
    11 44
    12 48
    13 52
    14 56
    15 60
    16 64
    17 68
    18 72
    19 76
    20 80
    21 84
    22 88

  11. I uploaded a workbook, StepChartForDrate.xlsx, that shows two ways to make this chart with your data.

    The first technique, which uses a line chart with a date axis and an extended data range, is from this post. The second is from an old web page, Step Charts in Microsoft Excel, which uses an XY chart rather than a line chart, with horizontal error bars (Custom Error Bars in Excel Charts might also be helpful).

  12. thank you sooo much

  13. I much prefer the step chart with risers to a waterfall chart. There’s a couple of reasons for this: One, with a step chart one needs to read the tops of the bars for the ups and the bottoms of the bars for the downs – the line on the step chart is the value of the step; Two, a waterfall chart is a bar chart of sorts and bars must start at zero. I’ve too often seen bars start at some greater than zero value to achieve some scale for a step that’s but a small fraction of the base value, e.g. a step of 10 where the base value is 1000. A step chart avoids this issue.

  14. Step charts may also show this data clearly, without truncating a tall bar to show details of the small changes in value.

    “…bars must start at zero.”

    On the other hand, like all rules, we might successfully suspend this one for such a waterfall, if the changes in value are being showcased. It may be useful to show a chart that starts at zero with the expanded one above it.

Trackbacks

  1. […] It’s possible to modify the data protocol in order to generate a step chart that has only the horizontal segments, without the vertical segments (risers). Derek pointed me toward this in one of the comments, and I incorporated it into a new blog post, Step Chart Without Risers. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0