Step Charts in Excel

Hacking the error bars

The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.

Below is an example of a Step chart that was created in Excel.

You have to hack one of Excel’s chart features to create this type of chart. You might think that this is a variation on a line chart but it’s not.

The chart type you have to hack is the scatter chart. And the feature that you need to hack are error bars.

Error bars have a lot of uses, one of them is creating Waterfall charts in Excel 2013 and earlier versions.

Example

We will base our example on the table shown below.

Initially we will select the range A1 down to B29. Click on the Insert ribbon tab and click on the Scatter chart icon.

You should end up where the chart that looks like the one below.

Before we add error bars we need to add two columns that will work with the Error Bars.

The first column just has the number 1 in it. Which means we have one entry going across. The second column requires us to calculate the daily difference.

Cell D2 has zero in it. Cell D3 has =B3-B2. See image below

We now need to add the error bars to the chart. Click the chart and then click the plus sign that is near the top right corner you will see the Error Bars option which you can tick.

The Error Bars go up and down and left to right as seen below.

We need to tweak the Error Bar settings because we need them to look like they move from left to right on our Step chart.

Click on one of the horizontal Error Bars and press Ctrl + 1. On the Format Errors Bars task pane that opens on the right we need to choose

  • Plus
  • No Cap
  • Custom option
  • Specify Value – in the Positive Error section specify the range C2:C29

Click one of the vertical Error Bars and choose

  • Minus
  • No Cap
  • Custom
  • Specify Value– in the Negative Error section specify the range D2:D29

We now need to get rid of the markers so that only the Error Bar lines are visible.

Click one of the dots on the chart. Then in the Format task pane select

  • Paint can icon
  • Marker
  • Marker Options – None
  • Fill – No fill
  • Border – No line

See settings below.

The chart is basically finished – see image below.

We just need to sort out the start and end for the dates on the horizontal axis.

In our case I linked the start and end dates to cells and removed the formatting to find the underlying numbers for the start date and end date.

Click the horizontal axis and amend the minimum and maximum values as below.

The final step is to make both Error Bars more prominent by increasing their width.

Format each Error Bar and select the Paint Can icon and increase the point size to 1.5.

The last thing I did was drop the Horizontal Axis down to the Low position.

The chart is done

Please note: I reserve the right to delete comments that are offensive or off-topic.