22

Column Chart That Displays Percentage Change or Variance

Bottom line: Learn how to create a column chart that displays the percentage change or variance between the columns.

Skill level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Video #2 – Dynamic Color Bars

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download

Download the Excel file to follow along or use in your own projects.  The file has been update with the chart from video #2.

Column Chart With Percentage Change.xlsx (129.5 KB)

The Column Chart with Percentage Change

This post was inspired by a chart I saw in an article on Visual Capitalist about music industry sales.

streaming-subcriptions-visual-capitalist-column-variance-chart

Source: visualcapitalist.com

I like how they displayed the variances between years, and decided to recreate it in Excel.

When creating simple column charts for trends, we almost always want to see the amount or percentage change between each column. There are many ways to go about this, including displaying the variances on a separate chart.

Column Chart with Percentage Change Variance Error Bars in Excel

For this solution we will use error bars and invisible columns to display the percentage change between each column.

Here is an alternate solution where the positive and negative error bars use a separate series.  This allows us to change the formatting of the positive and negative bars individually.

Column Chart with Percentage Change Conditional Format Error Bars GIF

I created this alternative based on a suggestions from Conor and Wayne on the YouTube video.  I've included it in the example file that you can download above on the ‘Conditional Format (2)' sheet.  I believe this will be the most popular choice.  You do NOT have to change the label position for the negative bars for this solution.

The 2nd video above explains more about this solution.  I will update the post in the future to explain this in more detail.

Creating the Chart

The chart isn’t too difficult to setup. It does require a few columns of formulas to create the source data for the chart columns, error bars, and data labels.

I explain the steps to create the chart below, which will also help you learn more about the different chart elements and techniques available to us. However, you don’t have to create the chart.  You can just download the example file and plug in your own data.

Here are the steps to create the chart.

1. Source Data Formulas

The chart uses a few columns of formulas to calculate the amounts for the invisible columns and variances used for the error bars and labels.

Source Data and Formulas for Column Chart with Percentage Change

Here is a quick explanation of each formula starting in column C:

  • Year Label: Uses the REPT function to join the year in column A with a repeating number of blank spaces. This makes the label longer and moves the text to the left to align with the visible bar for the amount. Otherwise, the label would align under the center of the visible and invisible bars.
  • Revenue $K: Reference to the amount in column B with thousands ($#,###,K) number formatting applied.
  • Invisible Bar: Reference to the amount of the next period (year). This is used for the invisible bars, that are the top/base of the error bars.
  • Variance: The variance between the current year and next year.
  • Var +: Uses the IF function to return the variance if it is positive.  Used for the error bars.  Returns a negative number to start at the top of the invisible bar and go down.
  • Var -: Uses the IF function to return the variance if it is negative. Used for the error bars.  Returns a negative number to start at the bottom of the error bar and go up.
  • Variance %: Used for the labels on the invisible column under the error bars.

I explain each of the formulas in more detail in the video above.

2. Create the Column Chart

The first step is to create the column chart:

  1. Select the data in columns C:E, including the header row.
  2. On the Insert tab choose the Clustered Column Chart from the Column or Bar Chart drop-down.
    Insert Clustered Column Chart for Percentage Change Chart in Excel
  3. The chart will be inserted on the sheet and should look like the following screenshot.
    Step 2 - Create the Clustered Column Chart in Excel

3. Remove Extra Elements

The default chart formatting includes some extra elements that we won’t need.

You can left-click each of the following elements and press the Delete key.

  • Legend
  • Horizontal gridlines – This is optional.  You can keep the gridlines or make them a lighter shade of gray.

This cleans up the chart and allows the viewer to focus on the important elements.

4. Add Error Bars

The Error Bars draw the connection between the tops of the amount columns between each bar.

Here are instructions for inserting the Error Bars:

  1. Left-click one of the columns in the Invisible Bar series (orange columns).
  2. From the Elements menu, choose Error Bars > More Options.
    Step 4 - Add Error Bars to Column Chart Excel
  3. The task pane will display the Format Error Bars options.
    Step 4 - Custom Error Bar Options Specify Value
  4. The Direction should be set to Both.
  5. For the Error Amount option, select the Custom radio button, then click the Specify Value button.
  6. Select the Positive and Negative error value ranges.
    1. G5:G11 for the positive values
    2. H5:H11 for the negative values
  7. Click OK to add the Error Bars to the chart.

The error bars start at the top of the Invisible series (orange) columns to create the variance element. The Invisible Bar is the amount from the next period.  The error bars go down for positive changes and up for negative changes.  This connects the top of the column from the current period to the top of the column for the next period.

Step 4 - Error Bars Show Variance Between Amount Columns in Excel Chart

5. Change the Series Overlap and Gap Width

The next step is to make the bars wider and remove any overlap.

  1. Left-click any of the amount bars (blue) to select the entire series.
  2. Right-click > Format Series
  3. Change the Series Overlap and Gap Width amounts to 0%.

Step 5 - Change Gap Width and Series Overlap on Column Chart Excel

6. Format the Invisible Bars with No Fill

The orange bars are just used as the base of the error bars. We do not need to display them on the chart, and can make them invisible.

  1. Left-click any of the orange bars in the Invisible Series to select the entire series.
  2. On the Format tab, choose No Fill from the Shape Fill drop-down menu.

Step 6 - No Fill Invisible Bars Column Chart Excel

7. Add Data Labels

Finally, we need to add data labels to the columns and error bars.  Here are the instructions for Excel 2013 and later.  See my note below if you are using Excel 2010 or earlier.

  1. Left-click any of the invisible bars (orange) to select all of the bars.
    Note: If you have trouble clicking on the bars.  Select the chart, go to the Format tab in the ribbon, and select Series “Invisible Bar” from the drop-down on the left side.
  2. Choose Data Labels > More Options from the Elements menu
  3. Select the Label Options sub menu in the Format Data Labels task pane.
  4. Click the Value from Cells checkbox.
  5. Select the range I5:I11 and press OK.
  6. Uncheck the Value and Show Leader Lines.
  7. The Label Position should be set to Outside End by default.
  8. For any negative variances, select each data label and change the position to Inside End.  See the video above for details.
    Step 7 - Add Data Labels for Percentage Variance - Value from Cells

The labels will now be displayed for both the amount and error bars.

Final Chart with Percentage Change Variance Error Bars in Excel

You can change the text formatting and colors as needed to match your report or dashboard.

Excel 2010 & Earlier

If you are using Excel 2010 or earlier you will not have the Value from Cells option for the data labels. However, you can use the free XY Labeler add-in from AppsPro to create the labels.  This will save you a lot of time.  Here is the link to download the add-in.

http://www.appspro.com/Utilities/ChartLabeler.htm

Alternate Solutions

I also have an older post that explains how to create the following chart with the variance between two series. This is great for actual versus budget reports.

Variance on Column Chart

Check out the article on Variance on Clustered Column or Bar Chart: Budget vs Actual.

I also have a 3-part video series on pivot tables and dashboards that explains more about formatting charts.

And checkout my free Chart Alignment Add-in to help get all of the elements in your charts lined up to perfection.

Conclusion

I think this chart is a nice way to display the percentage change/variance between columns.

It will work best when you have a smaller number of columns, maybe 12 or less.  Otherwise, I think the chart can become too cluttered looking. You will definitely want to remove as many extra elements (chart junk) as possible to give it a clean look that is easy to read.

I hope you are able to put this one to good use. Please leave a comment below and let us know what you think, or if you have any questions.

Thank you! 😊

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
Lemma YILMA - November 8, 2018

Hi Jon,

Thank you for this video – Creating bar chart with the percentage change between each column. This tutorial is impressive I thought. I use Excel 2010 version, I hope it will work.

Excellent presentation.

Best,

Lemma

Reply
    Jon Acampora - November 8, 2018

    Hi Lemma,
    Thanks for the nice feedback. You should be able to create it in 2010. You will need to use the XY Labeler add-in to create the data labels from cell values. I mention that in the section on 2010 above.

    Reply
Chuck - November 8, 2018

Excellent tip Jon! I love it.

Reply
Tytus - November 7, 2018

Jon, great podcast like always. I am looking forward to find out how you did these arrows as an alternate to the error charts.

Reply
    Jon Acampora - November 8, 2018

    Hi Tytus,
    Thanks so much! I just posted another video that explains how to create the arrows with the error bars. The video is the 2nd one at the top of this page.

    I hope that helps.

    Reply
Tahj Bomar - November 6, 2018

Hey Jon –

How would this work in Excel 2010?

I don’t have the option on Add Data Labels, step 4 – ‘Click the Value from Cells checkbox.’

Therefore, I can’t display the percentage variance.

Thank You

Reply
    Jon Acampora - November 7, 2018

    Hi Tahj,
    You can use the free XL Labeler add-in for Excel 2010. I added a link and more info in the section under step 4.

    I hope that helps.

    Reply
Dickson - November 5, 2018

Thank you! am getting a lot from you and it is really working. Am an expert in excel because of you Woooh!

Reply
    Jon Acampora - November 7, 2018

    Thank you Dickson! I’m so happy to hear that, and appreciate your support. 🙂

    Reply
Sandeep Kothari - November 4, 2018

Great post Jon – comprehensive, illustrative and well explained.
Why are error bars named so?

Reply
    Jon Acampora - November 7, 2018

    Thank you Sandeep!

    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. I show a quartile plot on that post where I also used error bars.

    Microsoft has a more simple example and explanation here.

    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.

    Reply
Danos Shukuroglou - November 2, 2018

Excellent. Thank you. Much appreciate your guidance. I found it very helpfull on many occasions. Look forward to further Excel education from you.

Reply
    Jon Acampora - November 4, 2018

    Thanks Danos! I appreciate your support. 🙂

    Reply
John - November 2, 2018

Another excellent post, keep up the good work.

Reply
Jordi Roig Domingo - November 2, 2018

Excellent work. Keep it up

Reply
Matteo Giovannini - November 2, 2018

Great post !!!
i would like to use your idea in my reports
Regards from Italy

Reply
    Jon Acampora - November 2, 2018

    Awesome! I’m happy to hear you will be using this chart in your reports. Thanks Matteo! 🙂

    Reply
DC - November 2, 2018

This looks like a great way to add value to charts! Thanks Jon!

Reply

Leave a Reply: