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 !!!!!
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.
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!
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.
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
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
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!!!!
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! :-)
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.
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.
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*
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
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.
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!
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!
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! :-)
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.
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 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.
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!
Sir really grateful , I'm learning a lot from your lectures in a very simple way.. Stay Bless Sir.!
Hi Jon, you're just the best! Through you short tutorials, i have come to appreciate the power of Excel.
Very nice. Liked it a lot. Simple and crisp explanations.
You just solved my problem. Thank you so much.
That was awesome! I never knew about Error Bars. THANK YOU!
Thank you Jon, really helpful tutorials, clearly and simply explained !
Excel chart magic at work. Thx for the help!!
Thank you for this really helpful video - I would never have figured out how to do this myself!
Great Explanation, as usual
Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I can't express my grattitude honestly
Great chart! I really like it. Thanks for sharing
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.
Very good example. Thank you for explaining so well.
Amazing video .. wonderful explanation. Great work .. Thank Jon
Great presentation of useful tool that visualizes the % changes.
Thanks for the feedback! 😀
Perfect -> value from cell is all i needed
This is Fantabulous...great Job
That was a neat trick. Thank you :)
Great tutorial.
Amazing Jon! That trick w =rept function...damn :D
Just Excellent!
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!
Nice job Jon
Thank you. That's help a lot.
cool... just had a great idea to use this, thanks bro
Really good tutorials....Going to try them all :) Thanks, Jon
Great video Jon, thanks for sharing!
Thanks Fernanda!
Thank You!
Thank you very much for sharing your knowledge! Great video.
Excellent!
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.
This helped me a lot! Thank you so much
Glad it helped! 😊
Super! Thanks Jon!
My pleasure! 😀
Súper cool functions
Great Video - thanks!
Thank you! that was really helpful.
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
Thank you. Great tips.
Please, how did you calculate the positive and negative variances ??
Job well done Jon, except on the bottom chart both 2011 & 2012 are +61% but the bar height are
different
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.
thank you
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
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.
Thank you Jeromy! :-)
Thanks so much, Jon. I am going to learn this invisible tricks and apply in my annual report. :)
Muy bueno... como ponemos los porcentajes en negativo con otro color?
Great video. Thanks
Thanks Joshua! :-)
Hi Jon, thank you!
Awesome
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! :-)
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
Thanks Jon
How did you change the color of the error bar to seperate color? Greeb and red?
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!
Thanks Jon, jw but for work would you recommend this or the bar chart with a line more useful? Thanks!
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*
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
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.
Hi Jon. Can i use this kind of chart for expenses? Like actual, BP and LY? Can you help me. Please and thank you.
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, 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!
This is great! But how to make negative value to RED automatically?
my error bar is always sitting on top of the bar so it's taller than the next bar when the error is positive
For the percentage change how do I get the +-sign in variance %
How would this work with stacked column charts?
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! :-)
Very useful & helpful. It's cool! However, is there a tutorial where you have instructed on how to create year label and further columns?
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
Not your fault but, this is massively frustrating when using Excel on a Mac; none of the controls are readily available!
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
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 found the issue. I needed to Reset the Label Text'" Txs
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.
Hi
America uses in medicine with petcentaged please get a answer
That was awesome! I never knew about Error Bars. THANK YOU!
Thanks a lot!