Yes. In Excel for Microsoft 365 on Windows, make a diverging stacked bar chart by splitting neutral responses into equal negative and positive halves, coding the disagreement series as negative values and the agreement series as positive values, then inserting a 2-D Stacked Bar chart. This places neutral responses at the center and lets readers compare response patterns across questions or groups.
What a diverging stacked bar chart shows
A diverging stacked bar chart is a horizontal stacked bar chart arranged around a central reference point, usually zero. Values on one side represent one direction, such as disagreement, and values on the other represent the opposite direction, such as agreement. It is especially useful for Likert-scale survey results, where each question has ordered answers such as Strongly disagree, Disagree, Neutral, Agree, and Strongly agree.
The chart makes the balance of responses easier to scan than a conventional stacked bar chart. A reader can quickly see whether answers lean positive or negative, how large the neutral share is, and whether two questions have different response shapes even if their overall totals are similar. It is not a special Excel chart type: the effect comes from preparing the data so that a standard stacked bar chart plots values on both sides of zero.
These charts are sometimes called diverging Likert charts. They are most meaningful when categories have a natural order and opposite meanings. They are less suitable for unrelated categories, such as product types, because placing those categories on opposing sides would imply a relationship that may not exist.
How the data is arranged
Start with one row per survey question and one column per response option. For a Likert question, the original values might be counts of people who selected each answer. To compare questions with different numbers of responses, convert those counts to percentages of each question’s total. The percentages in a row should add to 100% (allowing for small rounding differences if the source values were rounded).
For the diverging layout, code disagreement as negative, agreement as positive, and divide Neutral into two equal parts. One neutral half is negative and the other positive. This split is a visual device, not a claim that neutral respondents are half in favor and half opposed: together, the two halves still represent the full neutral share and keep its midpoint at zero.
For example, suppose a question has 10% Strongly disagree, 20% Disagree, 20% Neutral, 30% Agree, and 20% Strongly agree. The charted series become -10%, -20%, -10% for the left half of Neutral, 10% for the right half of Neutral, 30%, and 20%. The negative side totals 40%, and the positive side totals 60%. The bar still represents 100% of responses across both sides; the difference between side lengths shows the direction of the balance.
Prepare a small example in Excel
Enter response counts in columns B through F, with Strongly disagree in B, Disagree in C, Neutral in D, Agree in E, and Strongly agree in F. Put each question in a separate row, beginning on row 2, and use column A for the question label. Add a total column in G and percentage columns in H through L. The following formulas assume the response counts for the first question are in B2:F2.
- With counts in B2:F2, enter this in G2 to calculate the total. For the example counts 10, 20, 20, 30, and 20, the result is 100:
=SUM(B2:F2) - With the Strongly disagree count in B2 and its row total in G2, enter this in H2 to calculate its share. For 10 out of 100, the result is 10%:
=B2/$G2
Copy the percentage formula across H2:L2, then copy the total and percentage formulas down for the remaining questions. Format the percentage cells as Percentage using Home > Number. The dollar signs in the total reference keep the formula tied to column G as you copy it across; the row number can still adjust when you copy down.
Next, make six helper columns, M through R, for Strongly disagree, Disagree, Neutral-left, Neutral-right, Agree, and Strongly agree. The following formulas assume the percentages are in H2:L2 in that same order. Each formula creates one chart series; copy each one down for other questions.
- With Strongly disagree in H2:
=-H2 - With Disagree in I2:
=-I2 - With Neutral in J2, this is the left half:
=-J2/2 - With Neutral in J2, this is the right half:
=J2/2 - With Agree in K2:
=K2 - With Strongly agree in L2:
=L2
In the example, those helper values are -10%, -20%, -10%, 10%, 30%, and 20%. Keep the two Neutral helper columns separate so the chart can place one half on each side of zero. You can label them Neutral (left) and Neutral (right) in the worksheet, then tidy the legend after creating the chart.
Create the chart in Excel for Microsoft 365
- Select the question labels in column A and the six helper series in columns M:R. If those ranges are not adjacent, select the first range, then hold Ctrl while selecting the other range on Windows. Alternatively, select the helper table and add question labels through the chart’s data selection controls.
- Choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. Do not choose 100% Stacked Bar for this method: the positive and negative values already encode the direction, and the standard Stacked Bar preserves their magnitudes around zero.
- If Excel uses the series names or question labels incorrectly, select the chart and use Chart Design > Select Data to adjust the chart data range, series names, or category labels. The six helper columns should be six series, and each survey question should be a category.
- Right-click the horizontal value axis and choose Format Axis. Under Axis Options, set the minimum bound to -1 and maximum bound to 1 if the input values are decimal percentages. This creates a consistent scale from -100% to 100% across questions, including when one question has a strongly lopsided result.
- With the axis selected, set its number format to Percentage. Add a descriptive chart title and, if needed, a source note stating the number of responses or the survey period.
The result is a conventional Excel chart whose negative values extend left and positive values extend right. The neutral halves meet at the center. Fixing the axis bounds is important when comparing multiple charts or updating the data: automatic scaling can change the apparent size of differences between versions.
Format the chart for clear reading
Use a color sequence that reinforces the response order. For example, use two related colors for disagreement, a subdued neutral color, and two related colors for agreement. A restrained palette is usually clearer than assigning every series an unrelated bright color. Check that the colors remain distinguishable for readers with color-vision deficiencies, and do not rely on color alone: keep a readable legend and use meaningful labels.
Excel may show the two neutral halves as separate legend entries. You can reduce confusion by editing the legend entries or removing the legend and adding a small explanatory note, while retaining the two underlying series. Avoid deleting a neutral half from the chart, because that would remove half of the neutral responses and distort the totals. If you need data labels, select a series and use Chart Design > Add Chart Element > Data Labels, then choose a position that does not crowd the center. Labels on short segments can overlap; it is often better to label only larger segments or provide the exact values in a companion table.
Question labels can be hard to read when they are long. Widen the chart, shorten labels without changing their meaning, or make the chart taller so each question has room. If the questions appear in the opposite order from the worksheet, right-click the vertical category axis, choose Format Axis, and adjust Categories in reverse order. Check the result after changing the order because Excel may also move the horizontal axis crossing point.
Common problems and how to fix them
- The entire bar goes in one direction. Check that the disagreement helper columns contain negative numbers, not text that looks like a negative number. Confirm that the chart type is Stacked Bar rather than Clustered Bar.
- The midpoint is not centered on Neutral. Verify that Neutral is split into equal negative and positive values. If only one half is included, or the halves do not add back to the original neutral percentage, the visual balance is wrong.
- Bars have different apparent scales. Set the value-axis bounds explicitly, typically -1 and 1 for decimal percentages. Automatic bounds may differ between charts or after the data changes.
- Percentages do not add to 100%. Check that each row’s denominator includes the intended responses and that blank answers or excluded responses are handled consistently. If percentages were rounded before charting, calculate from unrounded counts where possible.
- Negative values show minus signs in labels. The axis should show negative tick values to identify the left side, but labels may be easier to scan without a minus sign. Consider omitting individual labels or using labels linked to a separate, positive-value display table rather than changing the underlying chart data.
- The chart changes when new questions are added. Make sure the chart data range includes the new rows. Converting the source into an Excel table with Insert > Table can help source data expand, though you may still need to check that the chart includes the new helper rows.
When to use another chart or method
A diverging stacked bar is a strong choice when the order and opposing direction of answers matter. A regular 100% stacked bar is simpler when the goal is only to show the full category composition and there is no meaningful center. A clustered bar chart can be clearer for comparing one response category across many questions, although it takes more space. A heat map can reveal patterns across a large matrix of questions and answer choices, but it depends more heavily on readers interpreting the color scale.
For a single question, a simple bar chart of response percentages may be easier to understand than a diverging chart. For a large survey, avoid squeezing dozens of long question labels into one plot; split the results into related groups or show an interactive report. The best design depends on what readers need to compare, not on whether a chart looks more elaborate.
Excel for the web and Excel for Mac can display and edit many standard chart features, but ribbon labels and the location of formatting controls can differ from the Windows steps above. If a control is not where expected, select the chart or axis first and look for its chart-formatting pane. The data preparation itself is the same: negative values, positive values, and two halves of the neutral category.
Frequently Asked Questions
Can Excel make a diverging stacked bar chart automatically?
Excel does not provide a separate chart type with that name. Prepare negative and positive helper series, split the neutral category if applicable, and insert a standard 2-D Stacked Bar chart.
Should neutral responses be split in half?
For a centered Likert chart, yes: divide the neutral share equally and put one half on each side of zero. This centers the neutral segment without changing its total. Do not interpret the two halves as separate groups of respondents.
Do I need percentages, or can I chart counts?
You can chart counts when every question has the same number of valid responses and comparing raw counts is appropriate. Use percentages when response totals differ, so the bar lengths reflect proportions rather than sample size.
Why does my chart not fill the full width?
A fixed axis from -100% to 100% reserves room for the full possible range on both sides. A particular response distribution may use only part of that range. This is expected and helps keep scales consistent when comparing questions.
Can I make this chart in Excel for Mac?
Yes. The same helper-data method works in Excel for Mac, and you can insert a stacked bar chart. Ribbon names or the placement of axis settings may differ slightly from Microsoft 365 for Windows, so use the chart and axis formatting controls available in your version.
Related
- Comparison Table Excel Template: Build, Format, and Use
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
- Comparison Plot in Excel With R²: Steps and Meaning
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Vertical Bullet Chart in Excel: Build One Step by StepTo create a vertical bullet chart in Excel, make a stacked column chart from your performance-range bands, place…
- Excel Waterfall Chart Data Format: Examples and SetupFor an Excel waterfall chart, use one column for category labels and one column for numeric changes, with…
- Excel Pivot Chart Stacked Column: How to Create OneTo create a stacked column PivotChart in Microsoft Excel for Microsoft 365 on Windows, click inside a PivotTable,…
- Excel to Org Chart: Free Options and How to Build OneYes—if you already have access to desktop Excel, you can make an org chart at no extra charge…
- Google Sheets Pivot Table Auto Refresh ExplainedYes. Google Sheets pivot tables update automatically when the cells in their source range change; there is no…
- DAX Formula in Pivot Table: Measures and ExamplesA DAX formula in an Excel PivotTable is usually a measure: a calculation stored in the workbook’s Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.