Butterfly Chart in Excel: How to Build and Format One

To make a butterfly chart in Excel, put the values for one group in a column as negative numbers and the other group’s values in a second column as positive numbers, then insert a 2-D Clustered Bar chart. The bars extend away from a shared zero line in opposite directions; format the left-side labels to show positive-looking values and set the value axis to symmetric bounds.

What a butterfly chart shows

A butterfly chart, sometimes called a tornado chart or bilateral bar chart, compares two values across the same set of categories. Each category has one horizontal bar extending left and another extending right from a central axis. The shared baseline makes it easy to compare the size of the two values and spot where the groups differ most.

For example, a product team could compare satisfaction ratings from new and returning customers across five service areas. A human-resources report might compare survey responses by two departments, or a researcher might compare results for two age groups. The chart is most useful when both sides measure the same thing, use the same unit, and share a sensible scale.

Excel does not provide a chart type named Butterfly Chart. The usual approach is to create the visual from a regular clustered bar chart and prepare the data so one series is negative. This is a charting technique, not a special Excel feature, and it works in Microsoft 365 for Windows as well as recent desktop versions of Excel.

Prepare the data for the chart

Arrange the worksheet with one category column and one value column for each group. For example, use this layout in A1:C6:

A1: Service area; B1: New customers; C1: Returning customers.
Rows 2–6: Wait time, Product quality, Delivery, Support, and Ease of use.
Enter the new-customer values as negative numbers, such as -42, -35, -28, -22, and -18.
Enter the returning-customer values as positive numbers, such as 38, 31, 33, 19, and 24.

The minus signs are necessary for the bars to extend left of zero. They do not mean the underlying measure is actually negative: they are a plotting device. Keep a separate original-data table if you need to preserve the values as collected, or clearly label the helper column so no one mistakes the signs for the meaning of the data.

Selecting the whole table, including headers and category names, helps Excel assign the two value columns as separate series and the first column as category labels. If your data is already in an Excel table, you can select its relevant columns. Avoid mixing percentages and counts on the same butterfly chart, because a common scale would not make those different units meaningfully comparable.

Build a butterfly chart in Excel

Select the category names, headers, and both value columns.
On the Windows ribbon, choose Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar.
Check that each category has two bars, one on each side of the center. If Excel placed the series and categories the wrong way around, select the chart and choose Chart Design > Switch Row/Column.
Click the horizontal value axis and choose Format Axis. Under Axis Options, set the minimum and maximum bounds to equal values on opposite sides of zero. For the example, use -50 and 50.
Use the Chart Elements button, shown as a plus sign beside a selected chart, to add a chart title, data labels, and gridlines as needed.

The result is a conventional clustered bar chart whose negative series runs left and positive series runs right. On Excel for Mac, the chart types are also available from the Insert tab’s bar-chart controls, though the ribbon grouping and formatting pane can look slightly different by release. Excel for the web supports basic chart creation, but some detailed axis and label-format controls may be more limited than in the Windows desktop app.

Center the scale and make the labels readable

Symmetric axis limits are important. If Excel chooses the minimum and maximum automatically, one side may get more room than the other, making equal values look different in length. A minimum of -50 and maximum of 50 gives each side the same span. Choose bounds that cover the largest value on either side and leave a little room for the longest bar and its label.

To set them, right-click the horizontal value axis, select Format Axis, and enter the desired values under Bounds. Set the minimum to a negative number and the maximum to the corresponding positive number. You can also adjust the Major unit to control the spacing of tick marks; a unit of 10 suits a 0-to-50 range better than many closely spaced labels. These fields may display as Minimum, Maximum, and Major under Axis Options.

Negative values may appear with minus signs on labels, even though the negative sign was only used to position the bars. To show magnitudes without minus signs, select the data labels for the left series, open Format Data Labels, and look under Number. Turn off Linked to source if shown, then apply a custom number format such as #,##0;#,##0;0 for whole numbers. The three sections of this format represent positive values, negative values, and zero. For decimals, use a format such as 0.0;0.0;0.0. Check the displayed labels after applying a format, since options can vary by Excel release and chart-label setup.

Be careful to format the left series’ labels rather than changing the actual worksheet values back to positive numbers. Changing the data would send both series to the right and remove the mirrored comparison. If labels sit too close to the center or overlap a bar, try placing them at the outside end of the bars, widening the chart, or reducing the label font size.

Order categories and distinguish the groups

Bar charts often display the first worksheet category at the bottom. If you want the first item at the top, right-click the vertical category axis, choose Format Axis, and enable Categories in reverse order. Excel may then move the horizontal value axis to the top. In the same axis options, adjust where the horizontal axis crosses the category axis if needed, so the zero line or tick labels are positioned where they are easiest to read.

Use contrasting but coordinated colors for the two groups, and make the legend names specific. New and returning customers is clearer than Series 1 and Series 2. A descriptive chart title should say what is being compared and, when useful, identify the period or unit. If the chart is intended to show survey percentages, state whether the values are percentages of respondents, percentage points, or another measure.

Gridlines can help readers estimate bar lengths, but too many create visual clutter. A useful starting point is a light set of major vertical gridlines at regular intervals, with a clearly visible zero line. Avoid relying on color alone to identify groups if the chart may be printed in grayscale or viewed by someone with color-vision differences; retain a readable legend and consider labels or patterns where appropriate.

When this chart is a good choice

A butterfly chart is a strong choice when the goal is a compact, category-by-category comparison between exactly two groups. It is especially effective when the categories are shared and the same metric is measured for each group. Sorting categories by the larger of the two values, or by the difference between groups, can help readers find the most important comparisons quickly. Keep the sorting rule consistent and explain it if the order is not natural.

It is less suitable when there are many groups, many categories, or values that cannot be placed on one meaningful scale. A third group does not naturally fit on the opposite side of the center, and adding more series can make a clustered chart hard to interpret. The chart also emphasizes magnitude, not the direction of change over time; if the main question is how values move across months or years, a line chart may communicate that more directly.

Remember that mirrored bars show two values, not automatically the difference between them. If one side has a value of 42 and the other 38, the bars show 42 and 38; the four-unit gap is inferred from their endpoints. If readers need to focus on the gap itself, add a clearly explained difference column or use a separate chart designed to show differences.

Common problems and practical fixes

Both series appear on the same side. Confirm that only the values intended for the left side are negative. A minus sign stored as text will not behave like a numeric negative value.
The two sides have unequal visual scales. Set the axis bounds manually to matching negative and positive limits, rather than relying on automatic bounds.
Category names appear in an unexpected order. Use Categories in reverse order on the vertical category axis, then check where the value axis and its labels have moved.
Left-side labels include minus signs. Apply a number format to those data labels that displays negative values without the sign. Do not change the underlying negative helper values.
Excel creates the wrong chart layout. Try Chart Design > Switch Row/Column, and verify that the categories are in the first column and the two measures are in adjacent columns.
Bars are difficult to compare. Check that the values use the same units and that the chart has a zero center. If the data includes extreme outliers, explain the scale rather than quietly truncating it.

A related misconception is that a butterfly chart is always the same as a population pyramid. A population pyramid is a familiar example of the mirrored-bar design, often showing age bands for two populations. The general butterfly chart can compare other paired groups and measures, but the same requirements for matched categories and a shared scale still apply.

Alternatives and export considerations

Use a simple clustered bar chart when the values are all positive and a central split would add confusion rather than insight. A dumbbell chart can emphasize the distance between two values per category, although Excel usually requires additional formatting or a chart construction method beyond the standard clustered-bar setup. A difference bar chart is another option when the gap, rather than each group’s magnitude, is the main message.

For a report or presentation, keep the worksheet data and chart together where practical, and include a note that the left-hand values are plotted as negatives if the underlying measure is positive. When copying the chart to another Office app, check that labels, axis limits, and category order remain intact. If exporting to PDF or printing, preview the output: small labels and pale gridlines can become hard to read on paper.

Frequently Asked Questions

Does Excel have a built-in butterfly chart?

No. Excel does not list Butterfly Chart as a dedicated chart type. You create the effect with a clustered bar chart by entering one series as negative values and the other as positive values, then formatting the scale and labels.

Why do I need negative numbers for the left side?

A horizontal bar chart places negative values to the left of zero and positive values to the right. The negative values are usually helper data for the chart, not a claim that the measured quantity is below zero. Use clear labels or a note to avoid confusion.

Can I make a butterfly chart without minus signs on the labels?

Yes. Keep the left series negative in the worksheet, then use a custom number format on its data labels that displays negative magnitudes without a minus sign. Verify the chart labels after formatting; changing the worksheet values themselves would alter the bar direction.

How do I make both halves the same width?

Set the horizontal value axis to symmetric bounds, such as -50 and 50, and use the same major-unit spacing on both sides. Equal limits ensure that the same magnitude has the same visual length on the left and right.

Can a butterfly chart compare more than two groups?

It is designed primarily for two groups, one on each side of the center. For three or more groups, a clustered bar chart, small multiples, or another comparison design is usually clearer than trying to add extra series to the mirrored layout.

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.