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

Video #2 – Dynamic Color Bars

Video #3 – Macro + Combo XY Scatter Chart

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.

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.

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.

Chart Iterations

Updated November 19, 2018

This chart has gone through several iterations since I first published it.

Iteration #1

We originally had some great feedback from Conor Foley and Wayne Edmondson.

The original solution used an invisible series between the data columns, with error bars that sat above the column.  This allowed us to display both positive and negative change between periods.

I explain it in more detail in the first video above.  The article below also explains how to create this chart step-by-step.

Iteration #2

For the 2nd iteration the positive and negative error bars use a separate series.  This allows us to change the formatting of the positive and negative bars individually.

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' 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.

Iteration #3

The 3rd solution came from Wayne Edmondson and uses a macro to move the data labels above/below the positive/negative error bars.  The macro also changes the font color of the data labels.  This macro can be called with the Worksheet_Change event to update the chart every time the source data changes.

Iteration #4

The 4th solution came from my good friend and charting master, Jon Peltier.  This solution uses a combo chart with an XY Scatter for the error bars.

This is another great solution because we don't need the invisible error bars.  Instead, he used an invisible data point for the XY scatter and 0.5 increments for the X-axis data.  This puts the data points and their vertical error bars between the data columns for revenue.

You can read Jon's full article on this combo chart solution over at the PeltierTech blog.

Keep Improving Your Charts

I'm showing all the chart iterations to help you see the process for improving our charts.  We don't always get it perfect the first time we create a chart, and that is OK!

You will get new ideas as you gather feedback from your audience (boss, co-workers, colleagues).  The look of a chart is a highly subjective matter anyways, so don't be afraid to publish.  You will learn a lot and improve your skills faster when you do. 🙂

Creating the (Original) Chart

The rest of the article explains how to create the original chart.  Even though we have made several iterations, this should still help you learn how to use the different chart elements to get creative with your charts.

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.

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.
   
3. The chart will be inserted on the sheet and should look like the following screenshot.
   

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.
   
3. The task pane will display the Format Error Bars options.
   
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.

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%.

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.

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.
   

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

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.

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! 😊