In this Formula Friady blog post, let’s take a look at a the universally used percentage change formula, we will be also plotting it on an Excel chart along with the original or actual values. We will then be be using two data sets and two types of chart to visually represent this data. Phew lets get started!…..

Percentage Change ( % change) really is one of the most frequently used and asked about formulas in my experience in working with Excel users.

So let’s as ever work with an example data set set. Below is my monthy sales report of Beannie hat volume sales for the past 6 months.

The math behind the percentage change is really simple. It has 3 steps.

1. Calculate the change – most recent value minus the original value
2. Divide that change by the old value- this gives you a decimal
3. Convert the number to a % ( either format as % or multiply by 100)

So in the example above if we calculate the difference between January and February 2015 the formula will look like this =(C6-C5)/C5

In the example we can see that the % difference between sales in February 2015 compared to January 2015 were an increase of 13 %. If we then drag the formula down the column of data we can easily populate the subsequent cells with the continuing % difference in sales during the first seven months of the year.

That was fairly straightforward wasn’t it? Let’s go ahead and plot the actual monthly sales volumes and % change on a chart.

• Highlight the cell D4 to D11 (in my example)
• Insert Tab- Chart Goup- Select 2D Clusered Column Chart
• You can see we can hardly see the % change data in this chart. (It’s right at the bottom of the chart just above the horizontal axis in red).

We need to represent this % change data on the chart in a meaningful way. Excel does not know that we really want to show the % change data as a line on the chart rather than another column so we need to deal with that.

First we need to actually highlight the % change data set on the chart, just go ahead and try this, it is really difficult as the data set set is so small. So what we can do is toggle through all of the chart elements one by one until we get to the % change data set.

Hold down the CTRL kep and using the up or down arrow you toggle though until you reach the right data set.

You can see that I have the % change data set selected. So I can now get Excel to change the chart type for this data set.

There are a couple of ways to to this.

1. Right click on the selected data set on the chart and hit Change Series Chart Type (Excel 2013) or

2. Chart Tools- Design-Type Change Chart Type

3. Then you just need select the Chart Type and Axis settings from the options below.

4. Select the % change data series and change the chart type to Line and click the Secondary Axis tick box as below and hit ok. In Excle 2013 onwards you will see a preview of your chart.

Now its just time to tidy up the chart a little. In my example I did the following

1. Removed The Gridlines

• Click on your chart gridlines
• In Major Gridline Options Change Selection from Automatic To No Line

2. Change The Secondary Axis Formatting

• Click and make active the Secondary vertical axis
• We want to ensure that this is clearly expressed as a percentage
• At the Axis Options scroll down to Number- Category- Change To Percentage
• I also want the number of decimal places to be none, so also under Axis Options – Number- Decimal places set to Zero
• I added a title for clarity and there were good to go with our more meaningful chart