Create A Column Chart That Shows Percentage Change In Excel - Part 1
HTML-код
- Опубликовано: 15 июл 2024
- Learn how to create a column chart that displays the percentage change between each column. This is an Excel chart that uses error bars and some formulas to show the variance between each time period (month, quarter, year, etc.)
Download the Excel file:
www.excelcampus.com/charts/co...
-- LINKS --
RELATED LINK ► This chart was inspired by a chart in an article on music industry trends from Visual Capitalist: www.visualcapitalist.com/music...
** JOIN OUR COMPREHENSIVE EXCEL TRAINING PROGRAM**
www.excelcampus.com/join-elev...
~ Learn my BLUEPRINT for Excel including when to use what tool (FREE TRAINING SESSION) www.excelcampus.com/blueprint ~
~Become an Excel Campus Insider (100% free) to access advanced workshops, bonus training, and weekly Excel tips: www.excelcampus.com/newsletter/
~I've also published another video with a 2nd iteration that includes color bars & data labels (conditional formatting), slicers, and is more dynamic. Here is a link to that video:
Part 2: • Percentage Change in E...
Part 3: • A Column Chart That Di...
One request we almost always get with our column or bar charts is to see the variance or percentage change between each column. This is especially true when the chart displays a trend over months, quarters, years, etc.
The solution uses a regular clustered column chart in Excel. The second series is made invisible with No Fill on the column, and then uses error bars to display the variance between amount columns.
*Excel 2010 & Earlier*
If you are using Excel 2010 or earlier you will not have the Value from Cells option for the data labels. However, you can use the free XY Labeler add-in from AppsPro to create the labels. This will save you a lot of time. Here is the link to download the add-in.
www.appspro.com/Utilities/Char...
Related articles & videos:
Part 2 with colored error bars & labels: • Percentage Change in E...
Variance on Clustered Column for Actual versus Budget: www.excelcampus.com/charts/va...
3-part Video Series on Pivot Tables & Dashboards: • Introduction to Pivot ...
Free Chart Alignment Add-in: www.excelcampus.com/keyboard-...
00:00 Introduction
01:08 Source Data
02:05 Error Bars
04:38 Subscribe
04:50 Add Data Labels
06:44 Formatting Tips
09:31 Outro
Hi Jon, you're just the best! Through you short tutorials, i have come to appreciate the power of Excel.
Thank you Jon, really helpful tutorials, clearly and simply explained !
Sir really grateful , I'm learning a lot from your lectures in a very simple way.. Stay Bless Sir.!
Thanks so much, I watch your Excel everyday during lunch time to learn new things and also refresh my memory for previous learning from your website, thanks again !!!!!
Thanks so much Sara! I really appreciate your support and happy to hear you are learning so much about Excel. Awesome!
That was awesome! I never knew about Error Bars. THANK YOU!
Really good tutorials....Going to try them all :) Thanks, Jon
Thank you very much for sharing your knowledge! Great video.
Thank you for this really helpful video - I would never have figured out how to do this myself!
Very good example. Thank you for explaining so well.
Very nice. Liked it a lot. Simple and crisp explanations.
Excel chart magic at work. Thx for the help!!
Thank you! that was really helpful.
Amazing Jon! That trick w =rept function...damn :D
Amazing video .. wonderful explanation. Great work .. Thank Jon
Thank you. Great tips.
nice tutorial. Itried to do that with stacked lines but not able to. Would be nice if you can share a video in which we have quarter totals as part of the stacked bar and having the same "anual" variation %. thanks Jon
You just solved my problem. Thank you so much.
Hi Jon, thank you!
This is Fantabulous...great Job
Great chart! I really like it. Thanks for sharing
Perfect -> value from cell is all i needed
Really, this process should be easier -- This isn't an uncommon need for financial reviews. Thanks for making it clear, but shame on Microsoft for not building this feature into Excel. It should be as easy as adding in a trend line.
cool... just had a great idea to use this, thanks bro
Great Video - thanks!
Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I can't express my grattitude honestly
Great Explanation, as usual
love this chart. Any improvement for negative/positive percentages? Values may be updated and as a result, changed from + to - and vice-versa, but still be locked to their initial custom position at the top or bottom.
Great video Jon, thanks for sharing!
Thanks Fernanda!
Thanks Jon, jw but for work would you recommend this or the bar chart with a line more useful? Thanks!
Thanks so much, Jon. I am going to learn this invisible tricks and apply in my annual report. :)
That was a neat trick. Thank you :)
Hello John, Its a gr8 informative video. I was struggling to make it in pivot chart, is it possible to do it? Thanks in advance.
Thanks Jon
Muy bueno... como ponemos los porcentajes en negativo con otro color?
Thanks for the chart information. really helpful. Can u please confirm how to conditional format (+) values as green and (-) values as red. Thanks in advance.
Thank you. That's help a lot.
This helped me a lot! Thank you so much
Glad it helped! 😊
Hi Jon. Can I do this kind of analysis, but with stacked bars? The variance would only need to show the % from one bar to another, not from each element in the bar... Thanks, and I appreciate allot your videos
Just Excellent!
Nice job Jon
Great tutorial.
Jon, this graph is GREAT. I used the file you provided to capture month over month data that I had from my company and it went ok. The issue I had, a month went by and I needed to add an extra month to my file all my graph updated fine but the last variance percentage label is not showing. It is showing the air bar but not the percentage. I tried several things, I followed the video steps over and over again and for some reason still not showing. Do you have any idea why? What am I missing?
Regards
Súper cool functions
Hi Jon, thanks for the video
Unfortunately the option "Value from Cells" is not available in Excel 2016 for mac!!! how to show then all percentages? Thanks!
Great presentation of useful tool that visualizes the % changes.
Thanks for the feedback! 😀
Excellent!
Job well done Jon, except on the bottom chart both 2011 & 2012 are +61% but the bar height are
different
Very useful & helpful. It's cool! However, is there a tutorial where you have instructed on how to create year label and further columns?
Thank You!
Hi Jon, this is great, but I am using a pivot chart, not a regular chart. And I need to make a lot (probably around 20 or so of the variance charts) in the same workbook so I can't spend time making a lot of individual tables with a negative var column and positive var columns and lots of invisible columns. How can I do this chart using a pivot chart? Please help!!!!
Great video. Thanks
Thanks Joshua! :-)
Thanks a lot!
Hi Jon. Can i use this kind of chart for expenses? Like actual, BP and LY? Can you help me. Please and thank you.
This is great! But how to make negative value to RED automatically?
Please, how did you calculate the positive and negative variances ??
This was very useful along with the excel example sheet in the link. However, I wish you were more in-depth with the short commands so that the example excel file was unnecessary. Many thanks regardless!
thank you
Super! Thanks Jon!
My pleasure! 😀
thanks I learn it in 20 sec that what is column chart.
comment me if you also learn what is column chart and also like
Great video.
Thank you Jeromy! :-)
great!
Jon, how about if I have stacked bar? Can you show me how to do it? So for my column D in your example, it would be about 4 different columns combined in 1 stacked bar. *fingers crossed*
How would this work with stacked column charts?
Awesome
How did you change the color of the error bar to seperate color? Greeb and red?
Great
How do you do this when you have multiple criteria? For example, in year 2011, you have total assets and net income.
Hi Jon.. nice trick. Is it possible to make the error bar data labels dynamic so they appear positive above and or negative below automatically as the data changes? I think to do that you would need two overlapping series with #NAs for the series data points that you don't want to show if not either the desired positive or negative value. The position of the data label goes with the positive or negative series. The appearance of the series is dictated by a helper column that shows #NA where you do or don't want the data point to appear, since the chart engine ignores the plotting of #NA data points. That would also allow for Conor Foley's question below to make the error bars red if negative and green if positive, since they would be coming from two series. I've seen Leila Gharani use this technique which makes the chart dynamic to changing data, regardless of positives or negatives. Might be worth an addenda to this video to add that functionality. Either way.. great video.. Thumbs up!
Hi Wayne, Great question! And yes, you are on the right track. You could use an overlapping series on the secondary axis. Or, you could keep everything on the primary axis and then just have more gap between the revenue bars. I'll post a followup video/article on this. Thanks!
Great.. looking forward to it!
Hi Wayne,
Here is an example where I also made the error bars different colors for positive and negative change, and used arrows instead of the flat caps at the end points. Here is a link to a screenshot.
www.dropbox.com/s/j5o3m5ot2diadk0/Column%20Chart%20Percentage%20Change%20Conditional%20Format.png?raw=1
I also added this solution to the example file you can download on the blog post page. Here is a direct link to the download. www.excelcampus.com/filedownload/charts/Column-Chart-with-Percentage-Change.xlsx
I have not updated the article with instructions on this yet, but will do so in the next few days. Let me know what you think. And thanks again for the suggestion! :-)
Hi Jon,
I like it.. thanks for sending. Question.. the upward green error bars appear to emerge from the right side of the revenue bars while the downward red error bars appear between the revenue bars. Is there any way to get the upward green error bars to appear between the revenue bars also? I tried to tinker with your settings, but could not make it happen. I seem to remember Leila Gharani employing a combo chart with the secondary axis being a scatter plot and the primary being a column chart and then she somehow adjusted the scale of the scatter to move the dots to the desired position and then ran the error bars off the scatter plot dots.. kind of like how you moved the "Years" label on the x axis. I'll have to dig out her video to see how she set it up. Admittedly, I'm not that skilled at charting. But, I'm intrigued to make it work. Thanks for taking the time to respond. I look forward to your updated video and solution. As always.. Thumbs up!
Yes, we can achieve that with an additional invisible bar. Here is a screenshot.
www.dropbox.com/s/9xtxcu5n6a3ln35/Column%20Chart%20Percentage%20Change%20Conditional%20Format%202.png?raw=1
It's not perfect, but does move the green bar off the revenue bar. With this solution I just added an additional invisible bar that creates more space between each bar. The bars overlap and you just have to play with the overlap and gap width, depending on the size of your chart. I updated the download file and this one is on the sheet named "Conditional Format (2)".
I believe I remember seeing Leila's video where she was using a separate chart to display variances. Not sure if that is the same one you are referring to.
I usually try to avoid the secondary axis whenever possible because it can present other challenges. In this case it would probably work ok, but you have to be careful that both of the axis have the same min and max. If the chart and underlying data is static, then you can set this when creating the chart. However, if you have some filtering or slicers on the chart, or are updating the source data frequently, then it requires more maintenance to check those parameters. It's really just something to be aware of if you do use a secondary axis to plot the same data point/type.
I hope that helps. Thanks again! :-)
I found the issue. I needed to Reset the Label Text'" Txs
For the percentage change how do I get the +-sign in variance %
I have a pivot table that is showing market values for each category for the current month and the previous month. I have another column showing the "percentage difference from" off of the previous month. How can I take the pivot table data and show it a column chart with the error bars reflecting the increase/decrease from the previous month? I do not want to recreate a date table that is not in the form of a pivot table.
Dear Jon. Great. This is a hack of an error bar. What is the original purpose of an error bar & why is it named so?
Great question! The error bars are typically used to display the standard deviation on a data point. They can also be used to show other types of variances or a range from the data point.
Here is a post on Comparative Distribution Chart - Histogram or Box Plot Alternative (www.excelcampus.com/charts/comparative-distribution-chart-histogram-box-plot-alternative/). I show a quartile plot on that post where I also used error bars.
Microsoft has a more simple example and explanation here (support.office.com/en-us/article/add-change-or-remove-error-bars-in-a-chart-e6d12c87-8533-4cd6-a3f5-864049a145f0).
In this case I’m using the error bars as a bit of a hack to create the variance bars. I think there are a lot of possibilities with these and allows us to get creative. 🙂
I hope that helps.
Thanks Jon for your comprehensive reply
Is there a workaround for Excel 2010?
Hi Nicholas, Yes, you can use the free XY Chart Labeler utility to create the labels from cell values. Here is a link to the download page.
www.appspro.com/Utilities/ChartLabeler.htm
The labels from cell values are really the missing piece for this in Excel 2010. I believe everything else should be compatible. I hope that helps.
I do have a question, why the error bar on the music industry trends is on the edge of the bar, do you know how to do that? thank you in advance
Hi Sara, Great question! I'm editing/deleting this comment. See my new comment below. Thanks!
Hi Sara,
You can do this by just changing the series overlap amount to a positive percentage. That will move the error bars to the end of the revenue bar. The only issue is when you have negative variances. The error bar will overlap the revenue bar.
Hi Sara,
I updated the Excel file that you can download on the blog post with an example. It's on the sheet named "Positive Only". I also updated the file and article with a version that has different colors for the positive and negative error bars, based on Wayne's comment above.
Here is a link to the post. www.excelcampus.com/charts/column-chart-percentage-change/
I hope that helps.
How to get negative and positive variance
my error bar is always sitting on top of the bar so it's taller than the next bar when the error is positive
Hi
Can this be done in google sheets?
I don't believe so. At least not with the default chart types in Google Sheets right now. I don't believe there is a way to set the values for the error bars, values from cells for data labels, or even change the overlap & gap width of the columns.
America uses in medicine with petcentaged please get a answer
Not your fault but, this is massively frustrating when using Excel on a Mac; none of the controls are readily available!
That was awesome! I never knew about Error Bars. THANK YOU!
Jon, I liked this video. Is it possible to colour the error bars to show red for decline and say green for increase?
thanks
Conor
Thanks Conor! Great question. Yes it is possible. There are a few ways to go about it. We can do this manually by selecting the bars, then changing the formatting of each.
Another option is to put the positive and negative changes on a separate series. This would require a bit more setup work and also create more space between each revenue bar.
We could also use a macro to color the bars. I will look into that solution and post a video or add it to the article. We could also use the macro to move the label position for negative variances...
I hope that helps.
Hi Conor, I wanted to let you know that I updated the post and example file with this solution. Here is a screenshot.
www.dropbox.com/s/j5o3m5ot2diadk0/Column%20Chart%20Percentage%20Change%20Conditional%20Format.png?raw=1
The example Excel file that you can download on the post also contains the solution on the "Conditional Format" sheet. This solution uses an additional invisible series to create the positive and negative error bars on separate series. This allows us to format the positive and negative individually, and also place the data labels automatically. You shouldn't have to make any manual changes to this chart as the data changes. Which means we could also add slicers to it.
I'm planning to do a followup video to explain the solution. Let me know what you think. Thanks again for the suggestion! :-)