PTS Blog

Main menu:

PTS Utilities

Commercial Utilities developed by Peltier Technical Services

  Waterfall Chart

Excel Books

Books that I own and use while developing in Excel

Goods and Services

Excel or charting related products and services which I use or feel are worthwhile additions

Subscribe

Site search


Recent Posts

Recently Commented

May 2008
S M T W T F S
« Apr   Jun »
 123
45678910
11121314151617
18192021222324
25262728293031

Archive


 

Categories


 

Step Chart Without Risers

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

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.

Share/Save/Bookmark

Comments

Comment from derek
Time: Saturday, May 24, 2008, 8:42 am

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.


Comment from demaws
Time: Sunday, May 25, 2008, 12:02 am

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.


Comment from Jon Peltier
Time: Sunday, May 25, 2008, 6:22 pm

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.


Comment from derek
Time: Thursday, June 5, 2008, 11:25 am

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.


Write a comment





Create Excel dashboards quickly with Plug-N-Play reports.