Let's take a look at a practical way of visualizing survey results in Excel, especially those that follow
a typical Likert format such as employee surveys
that have responses going from strongly
disagree to strongly agree or even simpler ones with just
disagree, neutral, and agree. This is sample survey data
that we want to visualize. So we have statements like
I feel valued in my team, The work is distributed
evenly in the team. In this case, for
example, 4 people said they strongly disagree. 32 people agree with that. So based on this, I want to
create a quick chart to visualize this. One option is to insert
a stacked bar chart. By default, it's giving me
these categories on the axis. I actually want to see
my questions on the axis. I have to go to Select Data and switch the row and the column. That's one way of
visualizing the survey data. Now, another method, as
specified by Jon Peltier, is to use a diverging stacked bar chart. It centers the neutral
responses in the middle. This makes it easier to
compare the categories with one another, and we can easier see which
of these features here generates the most
positive emotional impact and which ones have the
most negative impact. (hip-hop music) So first, I'm gonna show you how to make the stacked bar chart
from scratch in Excel, and then, I'm gonna show you a tool that does it automatically
for you without any effort. It's from Jon, so let's jump in. If you want to get these
centered in the middle, we need to have minus
values and plus values. Remember that our Neutral values is split in the middle, so we actually need to divide
those responses by two, and half of them are gonna be negative, the other half positive. Strongly Disagree and Disagree are gonna be negative for the chart. This means we can't
just use this data set. We actually need to create
a data preparation table in between. To help us get a headstart on that, I'm gonna copy, paste this here, but I'm going to paste this as links and then tweak what we need to tweak. I know that I want to get Strongly Disagree and Disagree as negative, so I'm just going to put a minus sign in front of the cell
reference for these two. Push these down. Neutral is something I
need to split to two. So I'm going to push them over here. Add two Neutrals here. One is for the minus side, and the other one is the plus side, but I also need them as minus here, so minus the value divided by two. I'm going to push this down. The other one is on the positive side, value divided by two. Okay, so this looks good. I'm not exactly sure about
the order of the stacks, how Excel is gonna stack them, but it doesn't matter. We can get started, and then
we can fix things as we go. So let's highlight this,
insert the stacked bar chart. Let's switch, so Select
Data, Switch Row and Column to get our questions on the correct side. For me to be able to better read this, let's put the labels on the side because currently, they're
covering the minus values. So I'm going to highlight the axis, press Control + 1 to
bring up the Properties. Under Axis Options, for Labels, instead of Next to Axis, we need Low because that pushes them out of the way. Now, let's take a look at our stacks. That's the Neutral stack. That's the Agree and Strongly Agree. They look fine, but this one, that's Strongly Disagree. I don't want that here. I want that all the way at the end. This one is Neutral. I want it to be stuck to
the other Neutral, right? Because now for Neutral,
I get two different colors because I have two different series. Ultimately, I'm going to
make them the same color. They're going to look like one series, but for me to be able to do that, I want to bring this
Neutral beside the axis, which means I need to switch the position of these two with one another. Now, there are different
ways of doing this. I could right mouse
click, go to Select Data, and switch the positions in here, or I can do it in my
data preparation table. Now, whenever I can, I try to fix things in
the data preparation table to keep my chart formatting simpler, so let's just fix it here. This means my Neutral
category is gonna come here, and my values here,
I'm going to push it here. Okay, so now, we can
see the Neutral category shifted to the axis. Let's bring back my Strongly Disagree. Okay, so now this looks good. Strongly Disagree is at the end, then Disagree and Neutral. Now, let's take a look at
fixing the colors here. For Neutral, I'm going to
go with a light gray. This is going to get the same color. Okay, so this looks good. Let's take away the grid lines from this. Let's take a look at the legend. First off, I'm gonna push it to the top instead of the bottom. I have Neutral two times. I just need it once. Click on it two times
to only highlight this and press Delete. Now, what I don't like is the
fact that Disagree comes first and then Strongly Disagree whereas on the chart, I have
them in the correct order. The legend is not correct. So before we take a look
at how we can fix this, let me show you Jon's utility
that helps us get this done without us needing to worry about our data preparation table. So I have the original data set here. All I'm gonna do is to highlight this, go to Peltier Tech, so this
is the add-in from Jon. You get a lot of different
functionalities here, so a lot of difficult charts in Excel that take a lot of data preparation are made automatically by this tool, okay, just like what we did which called the diverging bars, so I'm just gonna click on this. Gonna go with the default. Not going to worry about colors or anything. It creates a new sheet for me with my data preparation table, so it did that on the fly for me. It's all linked to the original data set, and it gave me the chart, and it gave it to me in the right order. So Strongly Disagree's here. Then, it's Disagree,
Neutral, Agree, and so on, and this is a normal chart, so you can change these colors just like you can do any
other type of formatting to the normal charts. One of the great things I like about this is that it doesn't hide the work from you. It shows you the data preparation table. It links it to the original data set, and we can actually test this quickly. Go to the survey results. Here, I have five people. Let's change it to 10. Let's change this to nine. 'Kay, let's go back here, and we can see this got
bigger and this got smaller. Now, one thing you might
wanna do with this chart is to add data labels, and we can do that really fast. Just right mouse click, Add Data Labels, and Neutral one, I'm just
gonna out the labels. I'm gonna add them here as well, but I don't wanna show
minus people, right? I actually want to show
these negative numbers as positive numbers. This is where I can use
custom number formatting. So from the formatting options here, for Number, I'm gonna give my own format, and I'm gonna say, well, positive numbers should be formatted as a positive, so the hash sign is just
a placeholder for number. I'm now going to use a thousand
separator in this case. The second argument is
how negative numbers should be formatted, and if I omit the negative sign from this, they're going to be shown as positive, right? So remember custom number
formatting has four arguments. First is positive. Then is negative. Then is zero, and then is text. I just need positive and negative here, and I'm going to add a hash sign for both so that my negative numbers
look like positive numbers. I'm going to do the same for this so that's easier to read the data. You can change the color of the labels, and we can delete this. Now, remember what I said before that what I like about this is it doesn't hide the work for you. What you can do is learn a lot from this. How did this add-in manage to
get Strongly Disagree first and then Disagree? We can see it from the
data preparation table. Notice we have an extra Disagree column that we didn't have in our
data preparation table. Well, the purpose of this
is purely for the legend. Let's go back to our
data preparation table, shift these out of the way, add a dummy Disagree
series that has no numbers. The pure purpose of this is
to get the legend correct. So now, I'm gonna add this
new series to my chart, Select Data, Add. For series name, it's Disagree. Series numbers, they're
just going to be these. They're going to be empty. I get a new legend. It has no numbers. I can't see the series here, and now, I'm gonna push it where I want, so notice when I push this up, it moves in the chart as well. I'm gonna position this
right where I want. I want it after Strongly Disagree. So I'm going to go with OK. I'll click on it, click on
it again just to select this, and go to Format and change the color to the Disagree series. Right, 'cause I want this
to be identical to this. I'm going delete this, click click, and then, remove this from view. Right, so the great
thing about Jon's utility is not only that it does the work for you, but it allows you to learn from it because it doesn't hide the
data preparation from you. I've added the link to the
description of this video. Please note that this is
not an affiliate link. If you follow my tutorials, you know I'm a big fan of
advanced chart techniques in Excel from scratch because I find that it's
important to understand how to do these things in
Excel without an add-in, but once you do, and you wanna 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 gonna find
this add-in really helpful. It's gonna save you a ton of time. Thank you, Jon, for creating this. If you enjoyed this video,
give it a thumbs up, and if you'd like to
improve your skills at work, do subscribe to this channel. (upbeat music)
