How to create a funny dog breeds lifespan chart in Excel

Excel 2016
Span chart, also known as a range bar graph (range column graph), floating bar graph, difference graph, high-low graph, used to display the specific variable value range. Span charts are ideal for comparing ranges, and typically, each variable represents a particular category. All values for the selected category fall between minimal and maximal values that correspond to lower and upper bounds of the interval.

For example, below is a dog breed life expectancy chart created for the top 10 of 2018 Most Popular Dog Breeds by the American Kennel Club (AKC):

Dog life 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 other information, including minimum and maximum, use the box charts.

In this example, the chart is created from the top 10 lines of the dog breed life expectancy list by AKC:

Data for a dog life span chart in Excel 2016

To create a span chart, do the following:

   1.   Insert to the spreadsheet the picture with the bone for the chart ranges: the start of the bone, the middle of the bone, the end of the bone:

  • The simplest way: insert an existing picture, duplicate it and, cut as you need. For example:
    Three parts of the bone simple in Excel 2016
  • The creative way: create own pictures for the bone parts. For example:
    1. You can create the bone parts in Microsoft PowerPoint by using three types of shapes: heart, moon and rectangle (see How to combine shapes to create a custom shape):
      Three parts of the bone 1 in Excel 2016   Three parts of the bone 2 in Excel 2016

      Note: PowerPoint offers a useful function to union the shapes that doesn't exist in Excel.

    2. Copy the parts created in the PowerPoint to the Clipboard.
    3. Paste them to the Excel spreadsheets as pictures: on the Home tab, in the Clipboard group, click the Paste dropdown list and select the Picture button:
      Paste Special as Picture in Excel 2016

      Note: If you don’t see the as Picture button, you can select Paste Special... and then in the Paste Special dialog box, select the picture format and click OK.

    4. Make any adjustments you want. We recommend cropping all three pictures to have the same height. It helps to create a consistent chart easier:
      Three parts of the fine bone in Excel 2016

   2.   Add the new three columns to the data for the different parts of bone:

  • the Start and End values are fixed (for this example, 0.3),
  • the Difference value is calculated as the difference between maximum, minimum values and the width of the start and end points:

    = Lifespan Max – Lifespan Min – Start – End:

Additional data for a dog life span chart in Excel 2016

   3.   Create a span chart:

   3.1.   Select the data for the chart (in this example, A3:A12, C3:C12, E3:G12).

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

   3.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 Bar:

Stacked Bar in Excel 2016

Excel creates a chart:

A stacked bar chart in Excel 2016

   4.   Format the vertical axis:

   4.1.   Right-click on the vertical axis and choose Format Axis... in the popup menu:

Format Axis in popup menu Excel 2016

   4.2.   In the Format Axis pane, on the Axis Options tab, under Axis Position, select the Categories in reverse order checkbox:

Format Axis Options in Excel 2016

   5.   Format the horizontal axis:

   5.1.   Right-click on the horizontal axis and choose Format Axis... in the popup menu.

   5.2.   On the Format Axis pane, on the Axis Options tab, under Bounds, type the appropriate values for Minimum and Maximum fields:

Bounds on the Format Axis in Excel 2016

   6.   Format the data series:

The first data series:

   6.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

   6.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, 0%:
    Gap Width in Format Data Series Excel 2016

The second, third, and fourth data series:

   6.3.   Select the appropriate picture (the start, middle or end part of the bone).

   6.4.   Right-click on the appropriate data series and choose Format Data Series... in the popup menu.

   6.5.   On the Format Dara Series pane, on the Fill & Line tab, in the Fill group, select the Picture or texture fill option, and then click Clipboard:

Fill Data Series from Clipboard in Excel 2016

   7.   Add the data labels:

   7.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

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

Format Data Labels in popup menu Excel 2016

   7.3.   On the Format Data Labels pane:

  • On the Label Options tab, in the Labels Options section, under Label Contains, select the Category Name option and unselect others:
    Labels Options in Format Labels Excel 2016
  • On the Size & Properties tab, in the Alignment group, remove Left margin and Right margin values:
    Size and Properties in Format Labels Excel 2016

   8.   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.

pixel