How To Create Tornado Chart (Butterfly Chart)

There's many ways to create , I'll share with you another example, see the final chart:
Tornado Chart Example

This chart is really looks like a butterfly. OK, let’s jump straight into creating a Butterfly Chart.

Download and Install The XY Chart Labeler

The XY Chart Labeler is a useful Add-ins to add, move, manage and delete the chart labels, you can download here: The XY Chart Labeler. You need to restart your Excel to activate this Add-ins.

Getting the data

In this tutorial, we will compare the sales performance of Store A and Store B. See below table.

1
2
3
4
5
6
7
8
9
10
A B C
Products Store A Store B
Product 1 308 245
Product 2 454 428
Product 3 550 513
Product 4 602 547
Product 5 632 677
Product 6 754 692
Product 7 823 836
Product 8 890 845
Product 9 907 857

Add helper column

We'll add one helper column to our data, named "Gap", it'll display products labels in our Tornado Chart. You can change the Gap values to fit your category names width. see below table. Copy A1:D10 to a new worksheet to start our tutorial.

1
2
3
4
5
6
7
8
9
10
A B C D
Products Store A Store B Gap
Product 1 308 245 1200
Product 2 454 428 1200
Product 3 550 513 1200
Product 4 602 547 1200
Product 5 632 677 1200
Product 6 754 692 1200
Product 7 823 836 1200
Product 8 890 845 1200
Product 9 907 857 1200

Create Tornado Chart

  1. Select A1:D10 or select any cell of range A1:D10.
  2. Under the Insert menu tab, in the Charts group, click the Bar button and choose Stacked Bar in 2-D Bar.
  3. In Chart Area, Right click any of the Series "Store A" (Blue Bar), choose Format Data Series in the quick menu.
  4. In the Format Data Series window, under Plot Series On, select Secondary Axis, then click Close.
  5. In Chart Area, Right click Secondary Horizontal (Value) Axis (in the top of chart), choose Format Axis.
  6. In Format Axis window, under Axis Options, Minimum: Fixed, -2500, Maximum: Fixed, 2500, and click Values in reverse order, then click Close.
  7. In Chart Area, Right click Horizontal (Value) Axis (in the bottom of chart), choose Format Axis. Or press CTRL+1.
  8. In Format Axis window, under Axis Options, Minimum: Fixed, -2500, Maximum: Fixed, 2500, then click Close.
  9. In Chart Area, Right click and choose Select Data.
  10. In Select Data Source window, select Gap series, click Move Up once.
  11. In Chart Area, select Vertical (Category) Axis press Delete.
  12. In Chart Area, select Series "GAP", press CTRL+1.
  13. In Format Data Series window, click Fill tab, click No Fill, then click Close.
  14. In Chart Area, Right click Series "GAP", choose Add Data Labels.
  15. Select Series "GAP" Data Labels, press CTRL+1, in Label Options, under Label Contains, select Category Name, and deselect Value, then click Close
  16. In Chart Area, Right click Series "Store A", choose Add Data Labels.
  17. In Chart Area, Right click Series "Store B", choose Add Data Labels.
  18. Select Series "Store A" Data Labels, under the XY Chart Labels menu tab, in the Charts Labels group, click the Move Labels button. In Move Labels window, click Left Arrow button to move the Series "Store A" Data Labels.
  19. Select Series "Store B" Data Labels, under the XY Chart Labels menu tab, in the Charts Labels group, click the Move Labels button. In Move Labels window, click Right Arrow button to move the Series "Store B" Data Labels.
  20. In Chart Area, select Secondary Horizontal (Value) Axis, press Delete, select Horizontal (Value) Axis, press Delete, select Horizontal (Value) Axis Major Gridlines, press Delete. Add chart Title, named with "Tornado Chart" or what you like.

Video

Create Tornado Chart

Comments

    • luna says

      cool i dont know if you know any info on tornadoes so i can teach my class next year…thanks for helping me

Leave a Reply

Your email address will not be published. Required fields are marked *