How to create Butterfly chart in Excel

Excel 365
A butterfly chart is a simplified version of a diverging chart, also known as a divergent chart. Unlike a diverging chart, a butterfly chart can only compare two data sets using two horizontal bars with a central baseline.

Butterfly charts typically leave spaces between columns to accommodate the names of the variables being compared, which is why they resemble butterflies with wings and a body:

Butterfly chart in Excel 365

Below is described one of the options for creating a butterfly diagram. See other options in the descriptions of very similar charts:

  • Creating a regular bar chart using the negative values of the compared values to reflect them relative to the central baseline on the example of a mirror chart:
    Mirror chart in Excel 365
  • Using two axes as in the proposed method of creating a butterfly diagram, but in opposite directions on the example of the bidirectional chart or two-sided diagrams:
    Bidirectional chart in Excel 365
  • Using additional data on the example of plotting a tornado chart:
    Tornado chart in Excel 365

For example, the butterfly chart will be created based on the results of a survey conducted at the company among pet owners who said they have a "man's best friend" - a dog or feline companion - a cat:

Butterfly chart data in Excel 365

To create a butterfly chart like the one above, do the following:

   1.   Create a regular bar chart

   1.1.   Select the data range (in this example, B2:G7).

   1.2.   On the Insert tab, in the Charts group, choose the Insert Column or Bar Chart button:

Insert Bar or Column Chart in Excel 365

From the Insert Column or Bar Chart dropdown list, under 2-D Bar, select Clustered Bar:

Clustered Bar chart in Excel 365

Excel creates a simple clustered bar chart:

Simple bar chart in Excel 365

   1.3.   Optionally, change the order of the vertical axis labels:

   1.3.1.   Right-click on the vertical axis and select Format Axis... in the popup menu:

Format Axes in popup Excel 365

   1.3.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options group:

  • Select the At maximum category option,
  • Check the Categories in reverse order check box:
Format Vertical Axis in Excel 365

   1.4.   Optionally, format the chart to move the legend, type a title, etc.

For example:

Formatted bar chart in Excel 365

   2.   Add the secondary horizontal axis

To display the second data series on the opposite side, select it, then do the following:

   2.1.   Right-click on the selection and choose Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 365

   2.2.   On the Format Data Series pane, on the Series Options tab, in the Series Options group, under Plot Series On, select the Secondary Axis option:

Secondary axis in Format Data Series Excel 365

   3.   Format and hide the primary horizontal axis

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

   3.2.   On the Format Axis pane, on the Axis Options tab:

  • In the Axis Options section:
    • In the Minimum field, type some negative value (in this example, -1.2),
    • In the Maximum field, type the appropriate value:
    Format Axis bounds in Excel 365

    Note: You can experiment with the minimum amount to adjust the size of the middle section of the chart. If you want to create a more interesting version of the inner element, see how to add middle points in the tornado chart.

  • In the Labels section, from the Label Position dropdown list, select None:
Hide Axis labels in Excel 365

   4.   Format and hide the secondary horizontal axis

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

   4.2.   On the Format Axis pane, on the Axis Options tab:

  • In the Axis Options group:
    • In the Minimum field, type the same negative value as for the primary axis (in this example, -1.2),
    • In the Maximum field, type the appropriate value,
    • Check the Values in reverse order check box:
    Values in reverse order in Format Axis Excel 365
  • In the Labels section, from the Label Position dropdown list, select None.

Excel recreates the chart:

Simple butterfly chart in Excel 365

   5.   Format data series

For example, to create the rounded data series, do the following:

   5.1.   Right-click the data series, then select Format Data Series... in the popup menu (see step 2.1.).

   5.2.   On the Format Data Series pane:

  • On the Series Options tab, in the Series Options group, type or select the maximum value (500%) in the Gap Width field:
    Gap Width for butterfly chart in Excel 365
  • On the Fill & Line tab:
    • Optionally, in the Fill section, select the color you prefer,
    • In the Border section, select the Solid line option, then:
      • Select the same color that is selected for the filling,
      • Select or type a larger width in the Width field (for example, 15pt),
      • From the Join type dropdown list, select Round:
Data Series format line in Excel 365

   6.   Add data labels

To add data labels to the chart, do one of the following:

  • Click on the Chart Elements button, select the Data Labels list, then select the position of the labels:
    Chart Elements, Data Labels, Inside End in Excel 365
  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
    Add Chart Element button in Excel 365

    From the Add Chart Element dropdown list, choose Data Labels, then select the place for the labels:

    Add Data Label in Excel 365

Make any adjustments to the stepped chart as you desire.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.