Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Add Excel Chart Percentage Differences

Posted by jeffprom on September 5, 2012

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!

Advertisements

7 Responses to “Add Excel Chart Percentage Differences”

  1. Linda said

    Hi, Jeff, thanks for posting this.. Very helpful.. ^_^

  2. Robin said

    This was super helpful!! Thank you

  3. marot90 said

    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.

  4. Stephanie said

    Exactly what I have been searching for! Thank you!

  5. Alex said

    Thanks Jeff extremely helpful. Wish there were more helpful posts like this out there!

  6. Nicola said

    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?

    • jeffprom said

      Nicola,
      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.

      Excel Chart with a secondary axis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: