welcome to Excel campus my name is Jon
and in this video I'm going to explain
how to create this column chart that
displays the percentage change. So here
we have this regular column chart with
the annual revenue trend and between
each of the bars we have this percentage
variance that's displayed with this error
bar here that allows us to see the
percentage change between each year and
so this chart was inspired by a chart on
an article I saw over at the visual
capitalists this chart here was an
article on music industry trends and
they have the percentage change
displayed here on top of each of these
revenue bars and one problem we
typically face with column charts like
this is that we want to display the
amount in this case the total revenue
and we also want to display the
percentage change and this solution here
I think does a pretty good job of that
without cluttering the chart too much so
in the solution I've created up here
it's very similar the only difference is
that it also allows for negative change
or negative variance year over year like
we can see in this column here so let's
take a look at how to create this chart
I'm going to jump over to the example
sheet and I'll make this file available
for free download and I'll put a link in
the description below this video so
here's the source data for the chart now
don't let this overwhelm you really we
just have two columns with the original
source data here the year and the amount
we then have a column for the year label
with a little bit of formatting and I'll
explain that we also have the revenue
number formatted here and then we have
some columns for the bars and the
variances that will create our air bars
and data labels so let's go ahead and
create the chart and I'll explain more
about these data points so we're just
going to select column c through e here
these first three columns and then i'm
going to go to the insert tab on the
ribbon and from the column chart
dropdown we're just going to select this
standard clustered column chart so i'll
insert that on the sheet i'm going to
move it over here a bit and we'll just
move over here resize a a bit and then
the first thing i'm going to do is just
delete the legend here we don't need the
legend so left click it
delete and then the next step is to add
the air bars so we're going to
left-click on this column here the
orange column will eventually make this
column invisible or change it to a no
fill but we're going to use this column
for the top of the air bar so as you can
see it just represents or is equal to
the revenue for the next year in each of
these years so we'll left-click that
column we're going to go to the elements
menu here and under air bars choose more
options that'll bring up the task pane
and then under the air bar options we'll
have the direction set to both scroll
down here click the custom radio button
and then choose specify value that'll
bring up this window here and ask us for
positive air values and negative air
values so we're going to go ahead and
select a range and that first one will
be this column right here and this is
just displaying any positive variances
now I've made those negative numbers
because of the way that the air bar is
going to go down but those are our
positive variances year over year click
this button and then for the negative
air value we're going to choose this
column with the negative variances and
those are also negative numbers so we'll
go ahead and click OK and then over here
on our chart we can now see that our air
bars are extending down from these bars
so in positive change years year over
year we have the air bar going down and
that's because we made it that negative
number and then when we have a negative
change we have the air bar sitting on
top of this bar here going up so that's
the air bars the next step is to change
the width of the bar so I'm just going
to left-click any of the bars here we'll
right-click format data series over here
on the series options under series
overlap we're going to change both of
these to 0% so just type a 0 in there
hit enter same here type of 0 hit enter
and as you can see that will make our
bars wider now the next step is to make
these bars these orange bars invisible
so we're just going to left-click one of
those bars so they're all selected we'll
go to the format tab on the ribbon and
then for the shape fill
we're going to change that to no fill as
you can see now we don't see the bars
and it's just invisible there and all
we're seeing is the air bars between
each revenue bar if you're enjoying this
video please click that big red
subscribe button below the video to
subscribe to our Channel and also click
the notification bell icon there to get
notified when new videos are published
and that the next step is to add the
data labels so we're going to left click
here to select our invisible bars if you
have trouble selecting those for any
reason you can go to the format tab with
the chart selected and then from this
drop-down here you can just choose
series invisible bar and that will
select all the bars so we're going to
add data labels so I'll go to our
elements menu here data labels expand
that and more options and then over here
on the label options tab right here
we're going to choose value from cells
click that check box that will open this
window asking us to select the data
label range and that is right here and
column I so we'll select all those data
labels there that just shows the
percentage change hit OK if we go back
over to our chart we can see that those
data labels have been added now we have
an extra label here for the amount we
don't need that so we're going to
uncheck this value check box we can also
uncheck show leader lines and now we
just have our percentage change label
above the air bars and those data labels
are actually outside or on the outside
end of the invisible bars if we go down
here we could see that right here this
outside end is our label position now
you'll notice for the years with
negative variance that the data label is
overlapping the air bar we don't really
want that it doesn't look great so one
thing we can do is left-click the data
label again just select a single data
label and then over here for the label
position we'll choose inside end and
that I'll actually put the data label
inside of that invisible bar there and
move it below the air bar so you can do
that for just the years where you have a
negative variance I'll left click and
select this one will choose inside and
for that as well and now when we look at
the chart we have the data labels either
below
or above the air bars so that's really
everything there is to creating this
chart I also want to explain a few
formatting tips first of all the
gridlines are optional you can delete or
remove the gridlines if you don't like
them if it makes your chart look too
cluttered I just made them a really
light color so you can left-click them
right click format gridlines right here
and then you can go over here to the
fill and line options and you could
change the color to maybe a really light
gray if you just barely want to see
those there just to make it not look too
cluttered and then you can also move the
axis the vertical axis labels to the
right if you'd like like that original
chart showed to do that you actually
select the horizontal axis and then over
in the task pane we're going to choose
axis options and then expand this out
and under vertical axis crosses we'll
choose at maximum category so that'll
move it over to the right side and then
finally the horizontal labels here that
labels in the horizontal axis I used a
trick here with some spacing to move
those or align them to the left as you
could see here in this column that's
used for our labels I am using the
repeat function here and repeating the
space character that's a space in
between those two double quotes
repeating that 13 times and that just
adds space to the end of the year and
moves it over to the left and so those
will line up underneath the bars here if
we didn't have that space there maybe we
just change this to 1 or 0 so there's no
space you can see that then these labels
are centered underneath both bars the
revenue bar and the invisible bar and it
just doesn't look as clean so that's one
little trick to just get those labels
moved over to the left of course that'll
depend on what's in your labels the
length of the text there and also the
width of your chart but you can use that
technique to get those lined up
underneath each bar and then of course
you can also change the colors of the
bars the formatting and the colors of
the data labels as well you can even
change the color of the air bars if
you'd like or the line style there so
there's a lot of options you can do with
this
to get it to look maybe something like
this original that we have here but the
point is just keep it clean try and keep
it as simple as possible and hopefully
you find this one useful so of course
let me know in the comments below if you
like this chart and I also have a
similar chart that I'll pull up here
from a previous article that shows the
variance on a clustered column this is
great if you're comparing budgets versus
actuals or something like this so I'll
put a link to the article that explains
how to create this in the description
below the video as well so I hope that
helps of course if you have any
questions please leave a comment below
if you enjoyed that video there are a
few simple things you can do to help me
out if you are watching this video on
youtube click the like button below the
video and leave a comment with any
questions or feedback and please don't
forget to subscribe to my free email
newsletter to get more tips and tricks
that will help you learn Excel thanks
again for watching and I'll see you soon
