The “Quick-n-Easy” Approach
Take a look at the following survey data we wish to visualize.
We see that 4 people strongly disagreed that the team’s work was evenly distributed, while 32 people felt that the work was evenly distributed.
To begin the visualization process, we click in the data and request a Stacked Bar chart from the chart library.
The default chart places the answers along the y-axis and the segmented bars represent the questions.
We wish to reverse this arrangement. We can do this by selecting Chart Design (tab) -> Data (group) -> Switch Row/Column.
This is a quick and simple way to visualize the collected survey data.
Although the chart is easy to create, it has its issues.
Diverging Stacked Bar Charts
A more elegant approach would be to create a Diverging Stacked Bar Chart as posted by Jon Peltier. This chart centers the neutral responses. This provides a clearer representation when comparing the categories with one another.
This technique makes it instantly apparent which feature has the most positive emotional impact and which has the most negative emotional impact.
Creating the Chart from Scratch
First, we will see how to create the stacked bar chart from scratch in Excel. After that, we will examine a tool that will create the chart for you without any effort.
In order to get the “Neutral” category to be centered in the chart, we need to have the “Disagree” categories represented as negative values and the “Agree” categories represented as positive values.
As an additional requirement, we need to have half of the “Neutral” values to be negative and the other half of the values positive. This will allow the center line to “split” the neutral responses right down the middle.
This will require the creation of a data preparation table which will serve as a translator between the original data and the visualization.
Creating the Data Preparation Table
To reduce development, we will copy the original data from cells B5:G10 and paste as links in cells B13:G18 and make the adjustments in the linked version of the data.
We need the “Strongly Disagree” and “Disagree” values (columns C and D) to be negative. To achieve this, we will place a negative symbol in front of the cell references in cells C14:D18.
Next, we will add an additional “Neutral” category by shifting the data in cells F13:G18 to the right by one column.
Create a link in cell F13 to cell E5 to repeat the “Neutral” title.
The “Neutral” values in column E not only need to be negative but they also need to be divided by 2.
The “Neutral” values in column F need only to be divided by 2.
Creating the Diverging Stacked Bar Chart
Select the data in cells B13:H18 and insert a stacked bar chart as done previously in the post.
Just as we did earlier, switch the row and column positions to get the questions to appear on the y-axis.
To better read the chart, we will place the y-axis labels to the far left of the chart. To format the y-axis labels, click one time on one of the y-axis labels and press
CTRL-1In the Format Axis panel, under Axis Options -> Labels -> Label Position select “Low” from the dropdown.
Examining the Stack Arrangement
If we look closely at the colors of the bar segments and their order, we see that “Neutral”, “Agree”, and “Strongly Agree” and in the correct left-to-right order.
However, the order of the segments representing “Neutral”, Disagree”, and “Strongly Disagree” are in a left-to-right arrangement which is the opposite of what we want.
We also have two different colors for the negative and positive “Neutral” segments.
We will eventually set the both to the same color and place them beside one another; this will visually blend them together into a single segment.
We can either rearrange the items in the Select Data Source dialog box…
…or we can adjust our data preparation table. Adjusting the data preparation table is usually the easier option because it doesn’t require any complicated adjusting of the chart properties.
In the data preparation table, rewrite the formulas for cells C13:C18 with the original formulas from cells E13:E18. Next, rewrite the formulas for cells E13:E18 with the original formulas from cells C13:C18.
We now can see the negative “Neutral” category (dark blue) has moved directly to the left of the center y-axis and the “Strongly Disagree” category (gray) has moved to the far left side of the stacks.
Updating the Color Scheme
For our stacked bar, we will use the following colors for the segments:
- Neutral (both negative and positive) – light gray
- Agree – light blue
- Strongly Agree – dark blue
- Disagree – light orange
- Strongly Disagree – dark orange
To give the chart a cleaner look, remove the gridlines. Click on a single gridline and press Delete on the keyboard.
Updating the Legend
Move the legend to the top of the chart and remove the far-left instance of the “Neutral” reference by clicking on the reference two times (not a double click but 2 single clicks; pause between each click) and pressing the Delete key on the keyboard.
We still have a problem with the legend. The reference for “Disagree” is displayed first in the list but “Strongly Disagree” is displayed first in the chart.
We achieve the corrected legend order by way of a sneaky trick.
Shift the contents of cells F13:H18 to the right by one column.
Enter the label “Disagree” into cell F13.
This empty column will not supply any values to the chart but provide a mechanism to correct the order of our legend.
Add the new series to the chart by highlighting the new data (cells F13:F18) and pressing Copy. Click in a corner of the chart and press Paste. The newly added column in the data preparation table will now appear in the chart. Although you won’t see any new segments in the stacked bars, you will see a new entry for “Disagree” in the legend.
The next step is to rearrange the order of the legend. Right-click on the chart and select Select Data.
In the Select Data Source dialog box, scroll to the bottom of the Legend Entries list (left side) and click the newly added “Disagree” entry.
Using the “up/down” adjustment buttons, move the “Disagree” option up the list until it rests between “Strongly Disagree” and “Neutral” and click OK.
To synchronize the colors of the “Disagree” bar segments and the legend reference, click the legend to select it then click a second time on the middle “Disagree” reference to focus your selection to that single item.
Set the Fill Color to the same light orange you selected when customizing the chart segments.
To remove the original “Disagree” reference in the legend (left-most version), click the legend to select it then click a second time on the left-most “Disagree” reference to focus your selection to that single item.
Press the Delete key on the keyboard to remove the redundant reference.
We now have our finished Diverging Stacked Bar Chart.
Using the Peltier Tools Excel Add-In
The great thing about Jon Peltier’s utility is that it allows us to create the same chart without worrying about creating the data preparation table.
Once you have downloaded and installed the Peltier Tech Chart Utility, the process for creating the chart begins the same way we create most charts; click anywhere in the data and proceed to Peltier Tech 3.0B (tab) -> Custom Charts (group) -> Diverging Bars.
We will accept the default settings and click OK.
The output is a new Excel sheet containing both the completed data preparation table and the finished Diverging Stacked Bar Chart.
All the values in the data preparation table are linked to the original data; this retains the dynamic nature of the chart for updates.
One of the best features of Jon’s utility is that is uses the same tools and options normally used to create charts. This means that you can freely customize any and all chart options just as you would any chart you create.
Another great feature of the utility is that it doesn’t hide the underlying data that drives the chart.
This allows you to gain a greater understanding of how the chart was created. You can “reverse-engineer” the logic and apply it manually in other situations where you only need to implement a singular feature.
Adding Our Own Visual Tweaks
Because the chart created by Jon’s utility is just a regular Excel chart, we can enhance the output to suit our specific needs.
One popular enhancement is to display the data label values for the stacked bar segments. This is done by right-clicking on a segment and selecting Add Data Labels.
Let’s display the data labels for all the segments except the “Neutral” segment.
The only issue here is that we don’t wish to display our disagree categories as negative values.
Custom Number Formatting to the Rescue!!!
To format the negative values as positive values, click one of the negative value data labels and press
CTRL-1In the Format Data Labels panel to the right, expand the Number category and enter the following code pattern into the Format Code field and click the Add button.
#;#
The first “#” tells Excel how to display a positive number, the second “#” tells Excel how to display a negative number. By leaving out the negative symbol, Excel will display negative values as if they were positive values. Don’t forget to separate each “#” symbol with a semi-colon.
Repeat this custom number formatting for any segments you wish to display as positive values.
Don’t be afraid to do it yourself.
If you follow my tutorials you know I’m a big fan of advanced chart techniques in Excel from scratch. I find that it’s important to understand how to do these things in Excel without an add-in.
Once you understand the process and you want to have a quicker way of creating your reports, especially those that use a lot of the more complex, non-standard Excel charts, then you’re going to find this add-in extremely helpful.
This add-in will save you HOURS of time!
Additional Links and Information
Peltier Tech Chart Utility for Excel:
https://peltiertech.com/Utility30/
Learning Custom Number Formatting
https://www.xelplus.com/excel-custom-number-formatting_1/
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
