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!