Column Chart That Displays Percentage Change in Excel - Part 1

The Opinion Lighthouse
32 followers
3 Video Views·Nov 8, 2022
Column Chart That Displays Percentage Change in Excel - Part 1
Welcome to Excel Campus. My name is John 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 Capitalist.
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 error 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 drop down 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 it 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 and delete. And then the next step
is to add the error bars. So we're going to left click on this column here, the orange column. We'll
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 error 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 error bars, choose more options. That'll bring up the task pane and
then under the error 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 error values and negative error 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 error bar is going to
go down, but those are our positive variances year over year. Click this button and then for the
negative error 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 okay and then over here on our chart, we can now
see that our error bars are extending down from these bars. So in positive change years, year over
year, we have the error bar going down and that's because we made it that negative number. And then
when we have a negative change, we have the error bar sitting on top of this bar here going up. So
that's the error 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 zero percent. So just type a zero
in there, hit enter. Same here, type a zero, 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. 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 error 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 then 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 we'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 checkbox that will open this window
asking us to select the data label range and that is right here in column I. So we'll select
all those data labels there. That just shows the percentage change. Hit okay. 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 checkbox. 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 in the outside end of the invisible bars.
If we go down here, we can 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 to just select a single data label. And then over here for the label position, we'll
choose inside end. And that'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. We'll choose inside end 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 grid lines are optional. You can delete or remove the grid lines 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 grid lines right here. And then you can go over here to the
fill in line options. And you can 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, the labels in the horizontal axis, I used a trick here with some spacing to move those
or align them to the left. As you can see here in this column that's used for our labels, I'm 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 one or zero. 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 error 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're 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.
