Butterfly Graph in Excel: How to Build and Format One

Charts & pivot tables · Updated November 21, 2026

To make a butterfly graph in Excel, put one comparison series in negative values and the other in positive values, then plot both in a 2-D Stacked Bar chart. The bars extend away from a shared zero line, creating the familiar mirrored shape; Excel does not offer a chart type specifically named Butterfly Graph.

What a butterfly graph shows

A butterfly graph, often called a butterfly chart, compares two values for each of several matching categories. One series extends to the left of a central axis and the other extends to the right. The paired bars resemble wings, which explains the name. It is useful when the categories matter as much as the comparison: readers can scan down a list and compare the two groups category by category.

For example, a school might compare the number of students in two year groups across subjects, or a survey report might compare responses from two regions across questions. Population charts often use a related design, with age bands down the middle and one sex on each side. The chart works best when both series use the same unit and scale, such as people, percentages, or dollars.

In Excel, the central trick is data preparation. One side must be represented as negative numbers so its bars point left. Those negative values are a plotting device, not a claim that the underlying quantity is below zero. Labels and axis formatting can make the display clear that the chart compares magnitudes.

Set up the worksheet data

Keep the category names and the two original data series in separate columns. For a five-category example, enter headings in A1:C1: Category, Group A, and Group B. Enter these sample values in rows 2 through 6:

  • Alpha: Group A 42, Group B 35
  • Bravo: Group A 28, Group B 46
  • Charlie: Group A 55, Group B 31
  • Delta: Group A 18, Group B 24
  • Echo: Group A 37, Group B 52

Keep those original values positive and create two helper columns for charting. Put Left side in D1 and Right side in E1. With Group A values in B2:B6 and Group B values in C2:C6, enter =-B2 in D2 to make the left-side value negative, and enter =C2 in E2 to keep the right-side value positive. In this example, D2 returns -42 and E2 returns 35. Fill both formulas down through row 6. The original data stays intact, so later edits flow through to the chart without changing the source values.

Before charting, check that each category appears once and that the two compared groups use the same unit. If one series is a count and the other is a percentage, a shared scale would be misleading. Decide also whether missing values mean zero or unknown; replacing an unknown with zero can suggest a real absence that the data does not support.

Build the butterfly graph in Excel

In Excel for Microsoft 365 on Windows, select A1:A6 and, while holding Ctrl, select D1:E6. Then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. This plots the categories against the two helper series, with the negative series extending left from zero and the positive series extending right. Confirm that the category names appear on the vertical axis and both series appear in the chart legend.

If selecting separate ranges is inconvenient, you can copy the category column and helper columns together into a temporary contiguous range, then select that range before inserting the chart. Another option is to insert a chart from the helper data first and use Chart Design > Select Data to adjust the chart data range and category labels. The stacked bar chart is important: a clustered bar chart may place the two series in separate positions rather than making a single mirrored pair around the zero line.

On Excel for Mac, the same chart type is available from the Insert tab using the bar-chart controls; choose the 2-D Stacked Bar option. Ribbon labels and the way you select non-adjacent ranges can vary slightly by Mac version. Excel for the web also supports ordinary bar charts, although some detailed formatting controls may be in different places or more limited than in the desktop app.

Give the chart a descriptive title, such as Students by Subject and Year Group. Use Chart Design > Add Chart Element to add a chart title, axis titles, or data labels if they help explain the comparison. Avoid adding decorative elements that compete with the bars: the main purpose is to make corresponding categories and differences easy to see.

Format the scale, categories, and colors

A balanced scale is essential. If the largest value on either side is 55, both sides need room for at least that magnitude. Excel often chooses axis bounds automatically, but check the horizontal value axis: right-click it, choose Format Axis, and inspect Bounds. Set the minimum and maximum to equal magnitudes on opposite sides, for example -60 and 60 for this sample. The precise bounds should fit the real data rather than being copied blindly. Symmetric limits make the visual lengths comparable and keep the zero line centered.

For a tidy display, use the same color consistently for each group and choose colors with clear contrast. Select a series and use Chart Design > Change Colors, or use Format Data Series to adjust its fill. If the categories appear in the opposite order from the worksheet, right-click the vertical category axis and choose Format Axis; under Axis Options, use Categories in reverse order. Bar charts can reverse the apparent order because the first worksheet category may appear at the bottom.

The horizontal axis may show negative tick labels on the left, such as -20 and -40. That is accurate for the helper values but can confuse readers if the chart represents counts. A clear axis title such as Number of students and a note that the left-hand bars show Group A help. You can also format the value axis number display without a minus sign: in Format Axis, open Number and use the custom format code 0;0, then check the preview and resulting labels. Excel number formats can vary with regional settings, so verify that the display remains readable in the workbook. Keep the underlying negative values; changing them to positive would make both series extend to the same side.

Add labels without obscuring the comparison

Data labels make exact values easier to read, but five or six categories can become cluttered quickly. To add them, select a bar series and choose Chart Design > Add Chart Element > Data Labels, then pick a position that keeps labels visible. Repeat for the other series if needed. If bars are short, labels placed outside the bars may be clearer; if values are close together, a data table or a separate worksheet table may be more legible than labels crowded around the center.

When the left series uses negative helper values, labels may show minus signs even though the reported quantity is positive. Apply a suitable custom number format to the source helper cells and check whether the chart labels follow it. If they do not, select the labels, open Format Data Labels, and inspect the Number options; disable Linked to source if necessary before entering a display format. Test a couple of labels before applying the change to a large chart. Do not remove minus signs from the data itself, because the sign is what makes Excel draw that series to the left.

Use a legend with clear names rather than generic labels such as Series 1 and Series 2. If the two groups are already identified in the title or nearby text, a compact legend may be enough. Include units in an axis title or note, and state the period or population when that information is needed to interpret the values.

Common problems and how to fix them

  • Both sets of bars point right: Check that the left-side helper values are negative. A formula such as =-B2 should return a negative result when B2 is positive.
  • The bars overlap or do not look paired: Confirm that the selected chart is Stacked Bar, not Clustered Bar, and that the category labels align with the helper data rows.
  • The center is not visually centered: Set the horizontal axis minimum and maximum to equal negative and positive magnitudes. Unequal automatic bounds can distort the comparison.
  • Categories run from bottom to top unexpectedly: Reverse the category order in Format Axis, then check where Excel places the horizontal axis and labels.
  • Labels imply negative quantities: Explain that the sign positions the left series, use a clear legend, and format label display carefully while retaining negative source values.

Also check for blanks, text stored as numbers, and accidental totals included in the chart range. A total row can dwarf the category values and make ordinary differences almost invisible. If the data changes often, use an Excel table for the source and helper columns so that added rows can be included more reliably in the chart. Review the chart after adding categories, since the axis bounds may need to be adjusted.

When to choose a butterfly chart or another chart

A butterfly chart is a strong choice when the same set of categories has two directly comparable series and the left-right symmetry helps readers find differences. It is less suitable for more than two groups: extra series create visual clutter and make the mirrored design difficult to interpret. It can also be a poor fit when the values are not meaningfully comparable, or when precise ranking matters more than paired comparison.

A clustered bar chart is often better when both groups should be shown as positive values and readers need to compare bars without interpreting a mirrored axis. A line chart suits changes over time, especially when the sequence of dates matters. A simple table can be preferable when there are many categories or exact values matter more than visual patterns. For a single group’s values arranged by category, an ordinary bar chart is usually easier to understand.

Do not confuse a butterfly chart with a tornado chart. The terms are sometimes used for similar mirrored bar displays, but tornado charts commonly rank categories by the size of a difference or show sensitivity across low and high assumptions. A basic butterfly chart instead emphasizes the two values for each category. Choose a title and explanatory note that describe what the numbers actually represent, rather than relying on the chart’s nickname.

Frequently Asked Questions

Does Excel have a built-in butterfly chart?
No. In Microsoft 365, make one with a 2-D Stacked Bar chart and helper values that are negative for one series and positive for the other.

Why do I need negative numbers for one side?
Excel draws negative bars to the left of zero in a horizontal bar chart. The negative sign is a plotting method; the real-world quantity can still be a positive count or measurement.

Can I make a butterfly graph with percentages?
Yes, if both series are percentages measured on the same basis. Use a symmetric axis, such as -100% to 100% when appropriate, and make clear that the left side is displayed as a mirror rather than a negative percentage.

Can a butterfly chart compare more than two groups?
It is possible to construct more complex charts, but the mirrored layout is clearest with two groups. For three or more groups, a clustered bar chart or a small-multiple display is generally easier to read.

Is a butterfly chart the same as a population pyramid?
A population pyramid is a specific use of a mirrored bar chart, typically showing population by age band with two populations or sexes on opposite sides. Butterfly charts can compare many other types of paired category data.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.