This isn’t exactly a SQL Server post, but it does fall in the realm of BI and analytics so I figured it was fair game. In this post, I will show you how to put some logic into your Excel column charts by adding a percent difference between data values. In our example we have some annual numbers and we want to know the annual growth percent. To accomplish this, we will create a chart like the one here.
Organize your data with the years across the top, and your numeric value directly below. On the 3rd line, create a new value called ‘% Change’. The first year % change will be 0, but the rest of the years will be Current Year / Last Year.
Drag your formula across all of the columns. Then highlight all 3 rows of data and click on Insert and insert a 2-D Column chart. One word of caution here is that it has to be a 2-D chart, and can’t be 3-D.
Now we have a new chart added. However, as you can see some of our numbers are very large, and some are very small. The ‘% Change’ numbers are hardly visible.
We will correct this by changing the ‘% Change’ on data to be on its own axis. To do this click once on the tallest blue bar on the right, wait about 1 second and then click it again. This should highlight just that one blue bar. Next, click the right arrow button on your keyboard and you should have the red values (% Change) highlighted. This is the best way to highlight this data series as the numbers are so small.
Now when the data series is highlighted, carefully hover your mouse over the data series until you see the pop-up window say ‘Series % Change…’. Next, right click and select the last option which is Format Data Series.
On this screen we want to select Plot Series on Secondary Axis and then click close.
Next, while the data series is still highlighted click on the Design tab, Change Chart Type, and select the first line chart option.
Now you can right click the data series and do some formatting such as Add Data Labels, and changing the Data Label Number type to Percentage. You can also right click and choose Format Axis on our new axis, and also set the Number to Percentage. Now you have a nice chart and can easily tell the annual growth percentage!
Hi, Jeff, thanks for posting this.. Very helpful.. ^_^
This was super helpful!! Thank you
Cool. Always wanted to know how/when to use the secondary axis, and this seems to be a pretty good use. Now you just need to label the vertical axis, so you know what you are counting 🙂 For those of you who dont know how to do this, http://www.excel-aid.com/excel-chart-label-adding-removing-positioning-chart-labels.html gives an introduction. Hope that helps.
Exactly what I have been searching for! Thank you!
Thanks Jeff extremely helpful. Wish there were more helpful posts like this out there!
Hi Jeff, thanks for posting. I am trying to show the percentage change between two data points only. When changing the % data point into a line graph it disappears behind my two columns. Is there anyway to bring my % change line to the front?
I’m having a hard time re-creating your scenario. However, I would try editing the chart type editing the chart types of each axis. Check the screenshot below for an example. This was done in Excel 2013. Hopefully it helps.
you forgot to subtract 1 from your percent change formula! %change =(post-pre)/pre; or simply %change =(post/pre)-1
thank you very helpful