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 365

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 365

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 365

   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 365

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

Stacked Column in Excel 365

Excel creates the following chart:

A stacked column chart in Excel 365

   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 365

   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 365
  • 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 365

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 365

   4.2.   Do one of the following:

  • Right-click on the data label and select Format Data Label... in the popup menu:
    Format Data Label in popup menu Excel 365
  • Right-click on the data point and select Format Data Label... in the popup menu:
    Format Data Label in popup menu Excel 365

   4.3.   On the Format Data Label task pane, on the Fill & Line tab, in the Fill section:

  • Choose Picture or texture fill
  • Click on the Insert... button:
    Fill Data Label in Excel 365
  • In the Insert Pictures dialog box, choose the picture location, select the picture file, and then click Open or Insert:
Insert pictures in Excel 365

   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 365

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.