Butter Bars in Excel: Create a Butterfly Chart

To format butter bars in Excel, create a mirrored bar chart: plot one group’s values as negatives and the other group’s values as positives, then set the series to overlap and format the labels to show the original positive values. Excel does not have a chart type called butter bars; the phrase usually refers to a butterfly chart, which compares two groups across the same categories.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. You’ll prepare a small table, insert a clustered bar chart, and adjust its axis, labels, colors, and spacing so the comparison is easy to read. If you meant a different kind of chart by butter bars, the same formatting ideas may not apply.

What You’ll Need

Excel for Microsoft 365 on Windows, plus a worksheet containing category names and two sets of comparable values.
A decision about which group should appear on the left and which on the right. Use the same units and scale for both groups.
A few spare columns for chart values and labels. The chart uses negative numbers to point the left-hand bars left; those numbers do not have to change your original data.

Step-by-Step

Arrange the source data. Put one category per row, with the two groups in separate columns. For example, use A1 for Category, B1 for Group A, and C1 for Group B. Enter names such as Apples, Pears, and Plums in A2:A4, with each group’s values beside the matching category. Check that the two groups use the same units. A category should have a value for each group; fill missing values appropriately or decide to omit that category.
Create a chart-ready table. In a clear area, make four columns: Category, Left bar, Right bar, and Left label. Copy the category names into the first column and the right-hand group’s positive values into Right bar. Copy the left-hand group’s original positive values into Left label. The Left bar column will contain negative plotting values, while Left label preserves the positive figures you want readers to see. Keeping these columns separate avoids changing or obscuring the original data.
Make the left-hand values negative. With the original Group A values in B2:B4 and the chart-ready Left label values in D2:D4, put this formula in the first Left bar cell, such as F2, and fill it down: =-D2. Here D2 contains the original left-group value; if it is 72, the formula returns -72. Use the negative results for the Left bar chart column. The right-hand values remain positive. If you prefer, you can type negative plotting values directly, but formulas make the chart update when the source changes.
Insert the clustered bar chart. Select the chart-ready Category, Left bar, and Right bar columns, including their headers. Choose Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar. Do not choose a column chart: horizontal bars make the category labels easy to scan and give the groups room to extend in opposite directions. If the chart puts the categories or series in the wrong arrangement, select the chart and choose Chart Design > Switch Row/Column. Confirm that each category has one negative and one positive bar.
Set the bars to meet at the center. Click a bar from either series, right-click, and choose Format Data Series. In the pane, open Series Options and set Series Overlap to 100%. Since one series extends left and the other right from zero, overlapping their positions aligns the pair on the same category row. Adjust Gap Width to around 50%–80% to make bars thicker or thinner. A lower gap width makes the bars thicker; avoid reducing it so far that neighboring categories look crowded.
Make the value axis balanced. Click the horizontal value axis, right-click, and choose Format Axis. Under Axis Options, set the minimum and maximum bounds to equal values in opposite directions, such as -100 and 100 when all values fit within that range. Choose bounds that include the largest value from either group, with a little room at the ends. A symmetric scale is important: if Excel chooses different-sized positive and negative ranges, one side can look larger even when the values are equal. Set the axis to cross at zero if the crossing setting is available in the pane.
Show left-side labels as positive numbers. Click the negative (left) series, choose Chart Design > Add Chart Element > Data Labels, and add labels. Right-click one of those labels and choose Format Data Labels. Select Value From Cells, select the chart-ready Left label cells, and confirm. Clear the Value checkbox so Excel shows the positive source labels rather than the negative plotted values. Add labels to the right-hand series too, leaving Value selected so it displays its positive values. If your Excel build does not show Value From Cells, use the plotted values and set their number format to display negatives without a minus sign, or label the bars manually.
Put categories in a natural reading order. If the first category appears at the bottom, click the vertical category axis, choose Format Axis, and select Categories in reverse order. In a bar chart this can move the value axis to the top, so check its crossing setting afterward and place the zero line where it is easiest to interpret. Keep category labels readable; widen the chart rather than shrinking the text until it is difficult to read.
Format color, title, and gridlines. Click a series and use Format Data Series > Fill & Line to choose a solid fill. Give the groups contrasting but coordinated colors, and make the chart title describe the comparison and measurement period. Keep a light zero line or a few subtle gridlines to help readers judge lengths. Remove unnecessary borders and legend entries, but retain a legend or label the two sides clearly if the colors alone do not identify the groups.
Check the finished chart against the data. Verify at least one category with known values on each side. Confirm that the left bar length matches the original left value, its label is positive, and the right value is correctly plotted. Check that the axis bounds cover every observation and that the chart title, units, category order, and legend are clear. Save the workbook after the layout is final.

Common Mistakes

Plotting both series as positive numbers. Both sets then extend to the right, so the chart is not mirrored. Negate only the plotting values for the group assigned to the left; preserve the source values for labels.
Letting the axis scale differ by side. Butterfly charts depend on a shared zero and symmetric bounds. Set equal positive and negative limits, or the visual comparison can be misleading.
Leaving series overlap at its default. The two bars may sit slightly above and below one another instead of forming one aligned pair. Set Series Overlap to 100% and inspect multiple categories.
Showing minus signs on left labels. Negative values are a charting device, not the underlying measure. Use positive label cells or an appropriate label number format so readers do not mistake the sign.
Sorting each group separately. Categories must remain in matching rows. Sort the full source table together, not just one value column, or the chart will compare unrelated categories.
Using different units or totals without explanation. A mirrored display does not make unrelated scales comparable. Normalize data first if that is the actual analytical goal, and clearly state the method and units.

Tips

Use a short, consistent category label and limit the chart to the categories that matter. Long labels make the chart tall and harder to scan.
Choose a symmetric axis range based on the largest absolute value. For example, if one side reaches 86 and the other 93, bounds of -100 and 100 give both sides equal space.
Keep the original data intact. Put negative chart values and positive display labels in helper columns, and name those columns clearly so another person can understand the setup.
For a large number of categories, consider reducing the font slightly or increasing the chart height before removing labels. If labels still collide, show fewer data labels or display values in a nearby table.
Use a useful title such as “Customer ratings by product, 2026” and add units to the axis or title when values could be misunderstood.
On Windows, Ctrl+1 opens the Format pane when a chart element is selected; on Mac, use Command+1. Ribbon wording and pane placement can differ slightly on Mac and Excel for the web, but the same chart concepts apply.

Troubleshooting

Both groups appear on the same side: inspect the chart-ready values. The left-hand series must be negative and the right-hand series positive. If the wrong group is left, swap which series is negated or change the series assignment.

One side looks much longer despite similar values: open Format Axis and set equal bounds around zero. Also confirm that the values use the same unit and that the axis has not been manually set to a range that clips one series.

Labels show negative numbers or do not match the bars: use Value From Cells for the left series and select the positive label range for the same rows. Make sure you select only the label cells, not the header, and uncheck Value. If a label range has shifted after sorting, rebuild or reselect it.

Bars are offset, too thick, or hard to distinguish: set Series Overlap to 100%, then increase Gap Width to create more space between categories. Pick contrasting fills and check that both series are included in the legend.

The chart order is upside down: format the category axis and toggle Categories in reverse order. If the value axis moves to the top, change the axis crossing option or accept that placement if it remains clear.

The chart changes unexpectedly when data is edited: check the chart’s source range through Chart Design > Select Data. Extend the range to include new rows, and confirm that helper formulas and label cells were filled down. For recurring additions, convert the source range to an Excel table with Home > Format as Table, then verify the chart includes new rows.

Frequently Asked Questions

Does Excel have a built-in butterfly bar chart?
No. Make one with a clustered bar chart by plotting one series as negative values and the other as positive values, then formatting the axis and labels.

Do I have to change my original values to negative?
No. Keep the original data positive and use a helper column for negative chart values. Use the positive source values for left-side labels.

Can the left bars display positive values?
Yes. Add data labels and use Value From Cells to point to the positive label cells, then clear the option that displays the plotted value.

Can the two sides use different axis scales?
They can, but that usually undermines the visual comparison because equal bar lengths no longer mean equal values. For a standard butterfly chart, use the same units and symmetric bounds.

Can I use this chart for percentages?
Yes, if both groups’ percentages are calculated on a comparable basis. Plot one set as negative values, retain positive values for labels, and use symmetric bounds such as -100% to 100% when appropriate.

Will it update when I edit the source data?
It will update when the chart source and helper formulas include the edited cells. If you add new categories, extend the chart range and copy the helper formulas and label values to the new rows.

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.