How do I create a diverging stacked bar chart in Excel?

In modern data visualization, presenting survey results that quantify agreement or disagreement often requires a specialized visualization technique. One of the most effective methods for clearly illustrating polarized responses is the Diverging stacked bar chart. This type of chart centers the neutral responses (or the midpoint) and extends positive responses to one side and negative responses to the other, offering immediate visual clarity regarding overall sentiment.

While Excel offers powerful charting tools, creating a true Diverging stacked bar chart requires careful data preparation and manipulation of chart properties. This comprehensive tutorial provides a rigorous, step-by-step methodology for constructing this specific visualization, ensuring that your data tells a nuanced and accurate story.

By following the precise instructions outlined below, you will learn how to structure your underlying data using helper columns to transform a standard visualization into the advanced, insightful chart displayed in the following example:

This process is highly reliant on meticulous data entry and formula application, setting the foundation for the graphical output. Let us begin the detailed steps required to master this technique in Excel.

Step 1: Structuring and Entering the Core Dataset

The foundation of any successful visualization is clean and accurately structured data. For this tutorial, we will use a hypothetical dataset representing the percentages of public responses to a single statement: I would recommend this movie to a friend. The responses are captured across a five-point Likert scale ranging from Strongly Disagree to Strongly Agree. It is essential that your data is entered exactly as percentages, as this will inform the subsequent calculations for the stacked bar chart.

Begin by setting up your worksheet with clear headings. The first column should list the items or entities being rated (in this case, Movie Titles), and the subsequent columns should represent the five response categories. Ensure that the percentages in each row sum to 100% for an accurate representation of the distribution across all categories. This step is critical because the final chart we create—the 100% stacked bar—requires that each category’s contribution be relative to the whole.

The initial data structure should look precisely like the following image. Note the arrangement of the five response categories, which is central to our subsequent calculation of the mid-point for the diverging visualization. We must treat ‘Disagree’ and ‘Strongly Disagree’ as the leftward, negative components, and ‘Agree’ and ‘Strongly Agree’ as the rightward, positive components, leaving ‘Neutral’ as the central pivot point.

Step 2: Calculating and Inserting Helper Columns for Centering

Creating a true diverging chart in Excel necessitates the introduction of special calculation columns, often referred to as helper columns. These columns do not represent actual data but are temporary visual placeholders used to push the chart’s central category (Neutral) to the exact vertical midline of the plot area. We must insert two separate helper columns: one to the left of the negative responses and one to the right of the positive responses.

The first step involves creating the left helper column, which must precede the Strongly Disagree column. This column will aggregate the negative responses and the neutral responses, effectively calculating the total width required to shift the chart’s axis origin. In cell B2 (assuming your data starts in A1), insert the following summation formula. This formula adds the values in the ‘Disagree’ (C2) and ‘Neutral’ (D2) columns:

=SUM(C2:D2)

Next, we must insert the second helper column to ensure the resulting stacked bar chart maintains its 100% scale while accommodating the centering strategy. This second column is placed immediately after the Strongly Agree column. This column will aggregate the positive responses and the neutral responses, which is necessary for the initial 100% stacked configuration before we visually adjust the chart. In cell H2, input the following formula, which sums the ‘Neutral’ (F2) and ‘Agree’ (G2) responses:

=SUM(F2:G2)

After applying these two formulas and dragging them down for all rows in your dataset, your structured data should now include seven core percentage columns plus the two new helper columns. This comprehensive data matrix is what we will use to generate the initial chart visualization, facilitating the graphical separation required for the diverging effect. Observe how the data is now laid out in preparation for chart creation:

Step 3: Initial Chart Creation Using 100% Stacked Bar

With the data meticulously prepared, including the necessary helper columns, the next step is to insert the base visualization. We will utilize the 100% Stacked Bar chart type, which is ideal for displaying the proportional breakdown of categories within each row, irrespective of the absolute quantities.

To initiate the chart creation, begin by highlighting the entire expanded cell range, which should include the row headers, the movie titles, and all nine data columns (A1:H6 in our example). Once the range is selected, navigate to the Insert tab located on the top ribbon of Excel. Within the Charts group, locate the bar chart icons and select the option titled 100% Stacked Bar. This action will generate the preliminary visualization.

Upon insertion, the chart will likely display the data with the response categories plotted along the vertical axis and the movie titles represented by the colored segments within the bars. This initial display is often incorrect for our purpose, as we intend to visualize the proportional survey responses for each movie horizontally. The resulting chart will appear visually clustered and require immediate reconfiguration before customization can occur, as shown in the output below:

Step 4: Structuring the Data Display by Switching Rows and Columns

As observed in the previous step, the default output of the 100% Stacked Bar chart typically misrepresents the categorical structure by assigning rows to the category axis and columns to the series. To correctly display the movie titles along the primary axis (Y-axis) and the response categories as the stacked segments, we must switch the row and column assignments within the chart data source.

To execute this necessary rotation, right-click anywhere within the generated chart area to open the contextual menu. From the available options, select Select Data. This action will open the dialog box where you can manage the data ranges and chart series configurations. Within the Select Data Source window, locate and click the button labeled Switch Row/Column. This single click transforms how Excel interprets the data table, reversing the plotting axes.

After clicking Switch Row/Column, click OK to confirm the changes and close the dialog box. The chart will immediately update. The survey response categories (Strongly Disagree, Disagree, Neutral, etc.) will now be correctly displayed along the horizontal X-axis, while the movie titles will be positioned along the vertical Y-axis, which is the standard layout for a horizontal bar chart visualization. This is a crucial step in preparing the chart for the final diverging appearance.

The revised chart structure now accurately maps the movie titles to the vertical axis, allowing for clear comparison of the distribution of survey responses across the different films. The chart now visually represents the data in a usable format, although the presence of the helper columns still skews the visual output, which will be addressed in the subsequent step:

Step 5: Visual Cleanup and Removal of Helper Column Visibility

Although the helper columns were essential for shifting the neutral responses to the center of the chart area, they must not be visible in the final visualization. If left visible, they would confuse the audience by appearing as data segments and distorting the perceived proportional distribution of the actual survey data. The objective here is to maintain their positional function while rendering them invisible.

To achieve this, we need to selectively format the two segments corresponding to the helper columns. Carefully right-click on the first helper bar segment (the leftmost segment, usually a solid color that represents the first helper column calculation). In the contextual menu that appears, select Format Data Series.

Within the formatting pane, navigate to the Fill & Line options (usually represented by a paint bucket icon). Under the Fill category, select No Fill. Crucially, also ensure that the Border setting is set to No Line. Repeat this exact process for the second helper bar segment (the rightmost segment, representing the second helper column calculation). By setting both the fill and line properties of these segments to ‘None,’ we effectively remove them from sight.

The chart will now visually present the negative responses extending leftward from the central axis (where the Neutral responses begin) and the positive responses extending rightward. The invisible helper segments provide the necessary white space on the left and right sides, ensuring the Neutral category is perfectly centered, thereby completing the transformation into a Diverging stacked bar chart structure.

Step 6: Customizing the Chart Appearance for Readability

The final step involves enhancing the visual aesthetics and informational clarity of the chart. A well-designed Diverging stacked bar chart should use contrasting colors to distinguish negative from positive sentiment and eliminate superfluous elements that might distract the viewer.

First, address the chart legend. Since the helper columns are now invisible, their entries in the legend are redundant and misleading. Select and delete the corresponding legend entries that refer to the helper columns (e.g., Series 2 and Series 8, depending on your column naming). This cleans the legend, focusing only on the five actual response categories.

Next, focus on the X-axis (the horizontal axis). Since the proportional representation is implicit in the bar lengths and should ideally be complemented by data labels (which can be added separately), the percentage values along the axis are often unnecessary and clutter the visualization. Select the X-axis labels and delete them or format them to be invisible. This removal emphasizes the diverging structure rather than the underlying 100% scale.

Finally, apply a thoughtful color scheme. Use warm colors (e.g., shades of red/orange) for the negative sentiment categories (Strongly Disagree and Disagree) and cool colors (e.g., shades of blue/green) for the positive sentiment categories (Agree and Strongly Agree). A neutral color (e.g., gray or light yellow) should be used for the Neutral category. Add a descriptive chart title and ensure all data labels and axis titles are clear and concise. The resulting polished visualization will be the professional Diverging stacked bar chart ready for presentation:

Summary of Diverging Chart Creation

The process of creating a Diverging stacked bar chart in Excel transforms standard survey responses into an immediately interpretable visual asset. By strategically employing helper columns to manipulate the chart’s origin and then masking those columns, we successfully achieve the visual divergence centered around the neutral response. This technique is invaluable for analyzing Likert scale data and presenting polarized opinions effectively.

Key takeaways from this detailed procedure include:

• Data Integrity: Ensuring the original dataset is proportion-based (sums to 100%).
• Strategic Calculation: Using helper columns to aggregate categories required for offsetting the chart axis.
• Chart Selection: Utilizing the 100% Stacked Bar chart as the base for proportionality.
• Aesthetic Focus: Removing unnecessary visual elements, like the helper column bars and X-axis labels, to maximize visual impact and clarity.

Mastering this approach allows data professionals to move beyond basic charting capabilities and produce sophisticated, customized visualizations that communicate complex data distributions with precision and elegance.