Learning to Create Diverging Stacked Bar Charts in Excel: A Step-by-Step Guide

Data analysts frequently encounter scenarios requiring the visualization of sentiment or agreement scales. The ideal solution for effectively presenting this type of categorical data is often a diverging stacked bar chart.

This comprehensive guide provides a detailed, step-by-step methodology for constructing this complex visualization using Microsoft Excel. Our goal is to replicate the professional-grade chart displayed below, which elegantly separates positive and negative responses around a central neutral axis:

Understanding the structure and purpose of this chart type is crucial before beginning the process. Unlike a traditional stacked bar chart, the diverging format is specifically engineered to highlight the differences in distribution relative to a neutral midpoint, making it highly effective for survey analysis and Likert scale data. Let us begin the technical implementation!

Step 1: Preparing the Raw Data

The first critical step involves structuring the input data appropriately. We will utilize a hypothetical dataset that measures the percentage of people responding to the statement: I would recommend this movie to a friend. This data follows a typical agreement scale, ranging from Strongly Disagree to Strongly Agree.

For the chart to function correctly, the percentages for each movie must sum to 100%. This adherence to percentage distribution is fundamental for the subsequent application of the 100% Stacked Bar chart type in Excel. This initial organization ensures that our visualization accurately reflects the distribution of responses across categories.

Ensure your dataset is entered precisely as shown below, with response categories structured horizontally and the corresponding movie titles listed vertically:

This raw data forms the foundation, but it is insufficient on its own to create the divergence effect. To visually separate the “Disagree” sentiments from the “Agree” sentiments, we must introduce specialized calculation columns.

Step 2: Implementing Helper Columns for Divergence

The creation of two strategic helper columns is the most nuanced phase of this procedure. These columns are designed to act as invisible spacers, effectively shifting the visible data series (Strongly Disagree, Disagree, Neutral, Agree, Strongly Agree) to create the gap required for a true diverging effect. Without these placeholders, the chart would remain a simple, non-centered 100% stacked bar.

First, insert a new column (which will become Column B) directly before the Strongly Disagree column. This helper column will be used to calculate the necessary offset for the negative responses. In cell B2, enter the following summation formula. This formula calculates the total percentage of the negative side responses, which will later be hidden:

=SUM(C2:D2)

Next, insert a second new column (which will become Column H) immediately after the Strongly Agree column. This second helper column is crucial for symmetry and proper scaling of the 100% chart. In cell H2, input the corresponding formula, which sums the positive sentiment responses:

=SUM(F2:G2)

After applying these formulas and dragging them down for all rows in the dataset, your expanded data table should now include both helper columns, appearing as follows. Note that these helper columns are integral to the chart structure, even though they will eventually be rendered invisible:

It is vital to understand that the inclusion of these two extra columns temporarily increases the total percentage represented in the dataset beyond 100% for each movie. This is acceptable because the chart type we select next automatically standardizes the visual representation to 100%, treating the helper columns as just another data series that contributes to the total bar length.

Step 3: Generating the Initial 100% Stacked Bar Chart

With the data and the necessary helper columns prepared, the next step is to insert the base visualization. This specific chart type is essential because it guarantees that every bar extends precisely across the entire plot area, which allows the helper columns to act as symmetrical, hidden margins.

To initiate the chart creation, highlight the entire cell range, including the movie titles, response categories, and the newly calculated helper columns (e.g., A1:H6). Once the range is selected, navigate to the Insert tab located in the top ribbon of Excel.

Within the Charts group, locate and click on the icon corresponding to the 100% Stacked Bar chart. This action will generate the initial chart, which will appear messy and incorrectly oriented, but contains all the foundational elements we need to manipulate.

The resulting visualization will look something like the image below, with the data series (the response categories) currently displayed along the vertical axis, and the individual movies acting as the clustered bars:

This initial output confirms that all data, including the helper columns, has been successfully mapped into the chart object. The following steps will focus on reformatting this output to achieve the desired clarity and divergence.

Step 4: Reorienting the Data Visualization Axes

The current configuration of the chart places the data categories (Strongly Disagree, Neutral, etc.) on the vertical axis, which is not conducive to comparing the movies horizontally. To correctly visualize the survey results, we must swap the roles of the rows and columns.

To execute this switch, right-click anywhere on the newly created chart area. From the context menu that appears, select Select Data. This action opens the data source dialog box.

In the resulting window, locate and click the button labeled Switch Row/Column. This command instructs Excel to reinterpret the data series, moving the movie titles to the vertical axis and the response percentages to the horizontal axis. Confirm the change by clicking OK:

Upon returning to the worksheet, observe that the chart has been reoriented. The response categories (Strongly Disagree, Disagree, Neutral, Agree, Strongly Agree) are now represented along the x-axis, displaying the percentages, while the movie names are correctly positioned along the y-axis for easier comparison:

Although the chart is now correctly oriented, it still appears as a single continuous bar, lacking the central divergence. The next step addresses this by utilizing the helper columns for their intended purpose: creating the invisible centerline.

Step 5: Concealing Helper Series to Achieve Divergence

The core mechanism that transforms the continuous 100% stacked bar into a diverging visualization is the concealment of the two helper data series (Helper 1 and Helper 2). By making these segments transparent, we create an optical illusion where the neutral response category appears to be centered on the vertical axis, with negative and positive responses extending symmetrically outwards.

To hide the helper columns, you must format them individually:

1. Right-click on the first helper bar segment (typically the far left segment of any bar).
2. Select Format Data Series from the context menu.
3. In the Format Data Series pane, navigate to the Fill & Line bucket.
4. Under the Fill options, select No Fill.
5. Under the Border options, select No Line.

Repeat this exact process for the second helper bar segment (typically the far right segment of any bar). This action removes the visual presence of the helper columns, leaving a gap that visually centers the neutral response and forces the negative and positive stacks to diverge.

The chart will immediately take on the desired diverging appearance, as the invisible segments push the remaining data outward while still respecting the 100% total allocation of the underlying chart structure:

This step completes the structural transformation of the chart. The final phase involves standard visual clean-up to make the visualization easily interpretable for any audience.

Step 6: Final Customization and Chart Polish

The final step involves customizing the appearance to maximize readability and professionalism. Several cosmetic changes are necessary, primarily because the 100% scale on the x-axis is now misleading (it represents 100% including the hidden helpers, not 100% of the visible data).

The key customization actions include:

• Deleting the X-Axis Values: Remove the percentage labels from the horizontal axis. Since the chart is now divergent, these numerical values are confusing and should be replaced by data labels on the bars themselves, if needed, or simply understood as a relative comparison.
• Cleaning the Legend: The legend still contains entries for “Helper 1” and “Helper 2.” These must be right-clicked and deleted to prevent confusion. Only the five sentiment categories should remain.
• Adding a Chart Title: Insert a clear, descriptive title (e.g., “Movie Recommendation Sentiment Analysis”).
• Customizing Colors: Apply a distinct, divergent color palette. Typically, the negative sentiments (Strongly Disagree, Disagree) should use colors like red/orange, the neutral category should be a subtle gray, and the positive sentiments (Agree, Strongly Agree) should use green/blue. This color scheme reinforces the sentiment visualization.

By executing these final adjustments, the visualization is perfected, resulting in a clean, informative, and highly effective diverging stacked bar chart ready for presentation and analysis:

Additional Resources for Excel Visualization

Mastering advanced chart types in Excel is essential for high-level data reporting. The following tutorials explain how to perform other common visualization tasks in Excel, further expanding your analytical toolkit: