How to create a span chart in Excel

Excel 2016
Span chart, also known as a range graph, floating graph, difference graph, high-low graph, shows dataset ranges between a minimum value and a maximum value:
A span chart in Excel 2016

Span charts focus on only the extreme points and give no information on the values in between the minimum and maximum or on averages or the data distribution. To show more details, including minimum and maximum, use the box charts.

In this example, the chart demonstrates the difference of lifespans of popular domestic pets:

Data for a span chart in Excel 2016

To create a span chart, do the following:

   1.   Add the new column to the data with the difference between maximum and minimum values:

= Lifespan max – Lifespan min:

Additional data for a span chart in Excel 2016

   2.   Create a span chart:

   2.1.   Select the data for the plot (in this example, A3:B10, D3:D10).

Note: You can choose several discontinuous ranges by holding Ctrl and selecting them.

   2.2.   On the Insert tab, in the Charts group, click the Insert Bar or Column Chart button:

Column Charts in Excel 2016

From the Insert Bar or Column Chart dropdown list, select Stacked Column:

Stacked Column in Excel 2016

Excel creates the following chart:

A stacked column chart in Excel 2016

   3.   Format the data series:

The first data series:

   3.1.   Right-click on the first data series (Lifespan Min) and choose Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 2016

   3.2.   On the Format Data Series pane:

  • On the Fill & Line tab:
    • In the Fill section, choose the No fill option,
    • In the Border section, choose the No line option:
    No fill and No line in Format Data Series Excel 2016
  • On the Series Options tab, in the Gap Width field, choose the value you like. For example, 50%:
    Gap Width in Format Data Series Excel 2016

The second data series:

   3.3.   Right-click on the second data series (Difference) and choose Format Data Series... in the popup menu.

   3.4.   On the Format Axis pane, on the Fill & Line tab, in the Fill group, select the color you prefer.

   4.   Add the data labels (optional step):

   4.1.   Right-click on the middle part of the bone data series and choose Add Data Labels -> Add Data Labels in the popup menu:

Add Data Labels in the popup menu Excel 2016

   4.2.   Right-click on the data labels and select Format Data Labels... in the popup menu:

Format Data Labels in popup menu Excel 2016

   4.3.   On the Format Data Label task pane, on the Fill & Line tab, in the Fill group, check Picture or texture fill and then click on the File... button (if you have the picture already), Clipboard or Online... (if you would like to upload picture from Internet):

Fill Data Label in Excel 2016

   4.4.   Repeat the previous step for all other data labels and insert the appropriate pictures.

   4.5.   To hide the data label text, on the Format Data Labels pane, under Text Options, in the Text Fill & Outline tab:

  • In the Text Fill group, select No fill,
  • In the Text Outline group, select No line:
No fill and No line for Label in Excel 2016

Remove the vertical axis, the legend, and make any other adjustments you want.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.