Variance on Clustered Column or Bar Chart - Budget vs Actual - Excel Campus
51

Variance on Clustered Column or Bar Chart – Budget vs Actual

This post will explain how to create a clustered column or bar chart that displays the variance between two series.

Clustered Column Chart with Variance

Actual vs Budget Variance Column Chart Excel

Clustered Bar Chart with Variance

Actual vs Budget Variance Bar Chart Excel

Overview

The clustered bar or column chart is a great choice when comparing two series across multiple categories.  In the example above, we are looking at the Budget versus Acutal (series) across multiple Regions (categories).  The basic clustered chart displays the totals for each series by category, but it does NOT display the variance.  This requires the reader to calculate the variance manually for each category.

However, the variance can be added to the chart with some advanced charting techniques.  A sample workbook is available for download below so you can follow along.

Data Requirements

With any chart, it is critical that the data is in the right structure before the chart can be created.  The following image shows an example of how the data should be organized on your sheet.  It is a simple report style with a column for the category names (regions) and two columns for the series data (budget & actual data).

Data Structure for Variance Chart

This technique only works when comparing two different series of data.  This can include a comparison of any data type: budget vs. actual, last year vs. this year, sale price vs. full price, women vs. men, etc.  The number of categories is only limited to the size of the chart, but typically you want to have five or less for simplicity.

Chart Requirements

The chart utilizes two different chart types: clustered column/bar chart and stacked column/bar chart.  The two data series we are comparing (budget & actual) are plotted on the clustered chart, and the variance is plotted on the stacked chart.

The chart also utilizes two different axes: the comparison series is plotted on the secondary axis, and the variance is plotted on the primary axis.  This puts the stacked chart (variance) behind the clustered chart (budget & actual).

How-to Guide

Data Calculations

The first step is to add three calculation columns next to your data table.

  • Variance Base – The base variance is calculated as the minimum of the two series in each row.  This gives you the value for plotting the base column/bar of the stacked chart.  The bar in the chart is actually hidden behind the clustered chart.
    Base Variance Calculation
    _
  • Positive Variance – The variance is calculated as the variance between series 1 and series 2 (actual and budget).  This is displayed as a positive result.  An IF statement is used to return a blank value if the variance is negative.  The blank value will not be plotted on the chart, and no data label will be created for it.
    Positive Variance Calculation
    _
  • Negative Variance – This is the same basic calculation as the positive variance, but we use the absolute function (ABS) to return a positive value for the negative variance.  The negative variance needs to be plotted as a positive value to bridge the gap between the two series.  Calculating this in a separate column allows us to assign the negative series a different color, so the reader can easily differentiate it from the positive variance.
    Negative Variance Calculation

How to Create the Chart

The example file (free download below) contains step-by-step instructions on how to create the column version of this chart.  Creating the bar chart is the exact same process with stacked and clustered bars instead of columns.

The chart is not too difficult to create, and provides an opportunity to learn some advanced techniques.

  1. The first step is to create a Stacked Column Chart and add the five series to it.
    Step 1 - Stacked Column Chart_
    _
  2. Series 1 (Actual) and Series 2 (Budget) need to be plotted on the secondary axis.  Right-click on the Actual series column in the chart, and click “Format Data Series…”
    Step 2 - Format Axis - Secondary Axis
    _
    Select the “Secondary Axis” radio button from the Series Options tab.
    Step 2 - Secondary Axis
    _
    Repeat this for the Budget Series (series 2).
    _
  3. Change the chart type for series 1 & 2 to a Clustered Column Chart.  Select the Actual series in the chart, or in the Chart Elements drop-down on the Layout tab of the Ribbon (chart must be selected to see the Chart Tools contextual tab).
    Step 3 - Select Series 1
    _
    Click the Change Chart Type button on the design tab and change the chart type to a Clustered Column chart.
    .Step 3 - Change to Clustered Column Chart
    _
    We can now start to see the chart take shape.  The Acutal and Budget data are displayed in side-by-side columns for comparison.  The Variance series are displayed in the background as a stacked column.
    Step 3 - Clustered Column Chart
    _
  4. Adjust the Gap Width property for both charts.  The gap width can be changed in the Series Options tab of the Format Data Series window.  This controls the width of the columns.  A smaller number will create a larger column, or smaller gap between categories.
    Step 4 - Gap Width
    _
  5. Format the chart.  The chart is just plain ugly with its default formatting options.  We can make a few adjustments to make it more presentable.
    – Move the legend to the top and delete the 3 variance series.
    – Add a Chart Title.
    – Delete the Axis Labels.
    – Change the border and fill colors for the columns.
    – Delete the horizontal guidelines.
    _
    Step 5 - Formatting the Actual vs Budget Chart Excel
    _
  6. Add the data labels.  The variance columns in the data table contain a custom formatting type to display a blank for any zeros:
    _(* #,##0_);_(* (#,##0);_(* “”_);_(@_)
    These blanks also display as blanks in the data labels to give the chart a clean look.  Otherwise, the variance columns that are not displayed in the chart would still have data labels that display zeros.
    Step 6 - Data Labels on the Actual vs Budget Chart Excel
    _
    The data labels for a stacked column chart do not have an option to display the label above the chart.  So you will have to manually move the variance label above, and to the left or right of the column.

Summary

This chart is a great way to display the series data and the variance amount in one chart.  The guide is meant to help you understand how to create and edit these charts to tell your story.  The source data table is simple in structure, and the chart can be re-used with different data so you do not have to go through this process every time.

Please click here to subscribe to my free email newsletter to receive more great tips like this.  You will also receive a free gift.  It’s a win-win! 🙂

Download

Variance on Column or Bar Chart Guide.xlsx (185.7 KB)

The file below uses a slightly different technique by using a clustered column chart to display the variance, and then uses the Value from Cells option to display the data labels.  This only works in Excel 2013.  The advantage is that you can automatically display the variance label above the bar, and you don’t have to move it manually as the numbers change.

Variance On Column Or Bar Chart Guide For Excel 2013.xlsx (193.1 KB)

What do you think?  Do you use another type of chart to display variances?

Please leave a comment. 🙂

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 51 comments
jagadhesh - April 13, 2017

Hi,
i added new data column in the same chart but the axis is not aligning good it goes left and right somewhere. can somebody help me with that.

Reply
jagadhesh - April 13, 2017

Hi,
i added new data column in the same chart but the axis is not aligning good it goes left and right somewhere. can somebody help me with that

Reply
Nathan - January 27, 2017

Asking this to see if it might be possible, didn’t see a comment that was trying to quite do this.

I would like to stack another column on top of the Actual bar to represent forecasted expenditures. Then show the variance between Actual + Forecast vs. Budget. The only way I can think to do that is to have some type of third axis, which I have no idea how to do and doesn’t sound possible (or at least manageable).

Reply
    Jon Acampora - February 4, 2017

    Hi Nathan,
    I don’t believe it’s possible with this technique. I never like to say things are impossible, but getting the width of the variance bars to line up is going to be really challenging. It might be possible to overlay two charts with a clear background, but I have not tried that. It would still require a lot of maintenance. Sorry, I wish I had better news.

    Reply
    Rajashree Munoli - March 27, 2017

    Hi Nathan,

    I recently built a similar chart i.e.( Actual Type-A + Actual Type-B) v/s Budget. Sharing the same.

    This is the method that I used:

    Use Jon Acampora’s above mentioned method to calculate Base Variance, Positive Variance and Negative Variance.(Thanks Jon!)

    Plot Actual & Forecast as Stacked Bar Chart on Primary Axis.
    Plot Budget as Clustered Bar Chart on Secondary Axis.

    To eliminate overlapping of Primary and Secondary Bars, add required no. of ‘Dummy Series’ with zero values on Secondary Axis.

    Then use ‘Error Bars’ to show the Variances.

    Steps to get the Error Bars in place:

    Plot ‘Positive Variances’ on the ‘Budget’ Bar:
    Click on Layout>Error Bars>More Error Bar Options> Select your’Budget’series
    After you select the series, the ‘Format Error Bars’ window opens up.
    In the Format Error Bars Window: Under Error Amount section, select ‘Custom’ option and click on ‘Specify Value’.
    Then, select the cell range having ‘Positive Variance’ values as ‘Positive Error Value’. Enter 0 as ‘Negative Error Value’

    Plot ‘Negative Variances’ on the stacked ‘Actual+Forecast’ Bar:
    Click on Layout>Error Bars>More Error Bar Options> Select ‘Actual’ or ‘Forecast’, whichever is on topside of the stacked bars
    After you select the series, the ‘Format Error Bars’ window opens up.
    In the Format Error Bars Window: Under Error Amount section, select ‘Custom’ option and click on ‘Specify Value’.
    Then, select the cell range having ‘Negative Variance’ values as ‘Positive Error Value’. Enter 0 as ‘Negative Error Value’

    Now you should have the error bars in place. You can then format the Error Bars to have width equal to the Bars in the chart & to have different colors for Positive and Negative Variances.
    Hope this helps.

    Reply
Hunter - January 24, 2017

How can I show a % variance based off of this model?

Thanks

Reply
Andrea - June 22, 2016

Hello Jon,

First thanks for the explanation ! 🙂
I want to know, how can i put another indicator like PY (previous year) in the same series of Budget and Actual? now to see the GAP vs. PY

thank you very much

have anice day

Itahí

Reply
    Jon Acampora - June 30, 2016

    Hi Andrea,
    It’s pretty challenging to add a third bar to this. There is a lot more complexity with the positive and negative bars lining up.
    Thanks for the question though. 🙂

    Reply
Ameer - May 27, 2016

Hi Jon,
thanks for this excel, but i am really having trouble when the change is a small percentage. for example if i haev only one row with the following entries budget =1000 and actual = 1050, the graphs representation doesnt really show the right picture. the actual and budget looks like its in a different axis. Appreciate your help in fixing this

Reply
    Jon Acampora - May 30, 2016

    Hi Ameer,
    The axis max values might have changed to automatic. You will need to manually change the max and min axis values for both the Primary and Secondary axis of your chart. That should get everything lined up. I hope that helps. Thanks!

    Reply
      مهرجانات 2017 - May 19, 2017

      What’s up,I read your blogs named “Variance on Clustered Column or Bar Chart – Budget vs Actual – Excel Campus” daily.Your humoristic style is awesome, keep it up! And you can look our website about مهرجانات 2017.

      Reply
mark - May 18, 2016

Thanks for this Jon,

An excellent guide- and I’m now the proud owner of my own cluster chart with variances!

Love it!

Reply
Marie - March 2, 2016

Hi Jon, this is great thank you! I just have one question: I’m trying to compare consumption data across 3 years (2014 to 2016) – Is it possible to change the data table so that the graph includes 3 columns with consumption figures, and shows the variance between 2014 and 2015, and also 2015 and 2016? Let me know if this isn’t clear, thank you for your help!

Reply
    Jon Acampora - March 3, 2016

    Hi Marie,
    Great question. That is going to be pretty tough to do. I don’t want to say it’s impossible, but will require quite a few more data series and logic. I will give it some thought.

    That might actually be a good example/use for a post I recently did on dynamic chart data labels. In that post I explain how to create data labels with different metrics. One of those metrics is variance to prior period.

    I’m probably going to update that post with examples for different chart types. I used the stacked bar, but it is a bit cluttered and I’m not a big fan of stacked bar charts. The technique could be used for bar, column or line charts too.

    I hope that helps. Thanks!

    Reply
Emma - January 28, 2016

Brilliant guide, thank you! (Was a bit trickier to “translate” this into Excel 2013 language, but I figured it out eventually – they actually make it easier to do this with 2013).

Now my graphs look as impressive as the data is 🙂

Reply
Nelson - April 21, 2015

Hi, this excel is extremely great!
I have some issue to arrange the variance position..for example..In North, the variance is 300. If let say I increased the budget to 2000, the budget bar is overlapping the variance..I am trying to fix the position so that the variance will always on top of the budget or actual bar…but I couldn’t find this kind of setting…anyway to fix it?

Reply
    Jon Acampora - April 23, 2015

    Thanks Nelson! This is a great question. Are you using Excel 2013 by chance? If so, this is actually possible using Clustered column charts and the Value from Cells label option. Let me know if you are interested and I’ll explain how to do it.

    Reply
    Jon Acampora - April 23, 2015

    I added a file in the download section above for the technique using Excel 2013.

    Reply
      Nelson - April 23, 2015

      Thanks Jon! It is really helps!
      Yes i am using excel 2013, but at the label option > Label position, i couldn’t see the “Outside End” option..mine only have center, inside end and inside base..any idea?

      Reply
Amelia - September 30, 2014

Jon,

Thank you very much for the info! I do have one question. Here you are comparing just two different series of data. If I need to compare year over year data for 6 years but don’t want to show each year twice on the graph. How can I do it? Thanks a lot for your help!

Reply
    Jon Acampora - October 1, 2014

    Hi Amelia,

    Are you looking for something like the following image?

    Column Chart Variance Between Bars

    This chart doesn’t have labels yet, but it can be done. Let me know what you think.
    Jon

    Reply
Teresa - April 15, 2014

Never mind! I figured it out — it had do with the quotes when I cut and paste. Thanks so much!

Reply
Teresa - April 15, 2014

Hi, thank you for providing this! The formulas listed for looking for negative and positive numbers, don’t seem to work. The second one specifically. Any thoughts?

Thank you so much!

Reply
Sinead - April 15, 2014

This is great thanks. I do have one additional problem I’d love to solve with this – my actuals are budget numbers are made up of say sales of apples and oranges as well as north/south/east/west. I’d love to still show one column each for the actuals and budgets but each column shows the breakout of apples and oranges (a stacked column I think). thanks.

Reply
Vitor - April 13, 2014

The download is missing, can someone help me? Thankss! Great job Jon, awesome!

Reply
    Jon Acampora - April 13, 2014

    Hi Vitor,

    Thanks for the comment! The download should be available now. I switched hosting providers over the weekend and there was a little down time. Please let me know if you are able to download the file now.

    Jon

    Reply
sarah - April 11, 2014

Hi John,
I have used this graph to show actual v budget data for hours. I want to now add in another column to compare attandence time v actual v budget hours, and also i want to show 3 line graphs, productivity, efficiency and booking efficicency, which are all in %. I assume i would need a third axis and this would not be possible?
Any help greatly appreciated

Reply
    Jon Acampora - April 11, 2014

    Hi Sarah,

    For your first question about comparing the three different series (attendance v actual v budget), you will have to create a “clustered and stacked” column/bar chart on the primary axis. Jon Peltier has an article that explains this technique, and it also shows you how to add a line to the chart.

    http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/

    For your second question, the three line charts that are based on percentages would be difficult to add to this chart. It is also going to complicate the chart quite a bit, which is my biggest concern with it. I understand that the metrics are related, but it is probably best to think about what story you want to tell with the data. You might need to break the analysis into multiple charts, with each chart telling a story that leads to the overall conclusion. It is not always easy to do, but it will help you communicate your findings to the reader.

    Please feel free to email me your chart. I would be happy to take a look and possibly do a case study on it.

    jon@excelcampus.com

    Thanks

    Reply
Andrea - March 31, 2014

This was great information! I was able to graph exactly what I needed. Another question for you….I have 3 different groups of data that I would like to show on one graph. The first is a whole number between 500-600, the second is a whole number that is between 1-10, and the third is a percentage. Would it be possible to show all this information on a line graph? Thank you for your advice!

Reply
    Jon Acampora - April 2, 2014

    Hi Andrea,

    My apologies for not getting back to you sooner! I am glad you found the information useful. In regards to your question, I would recommend creating a separate chart for each of these metrics. You could stack the charts on top of each other in what is called a panel chart. This will allow you to keep the vertical axis relevant to the data being plotted and display the changes in the trend. If you have them all on one chart, the smaller numbers will just look like a flat line.

    The following article shows an example of a panel chart.

    https://www.excelcampus.com/charts/dynamic-stacked-column-bar-chart-find-the-missing-trends/

    Please let me know if you have any questions.

    Thanks!

    Reply
P.Flowers - March 24, 2014

Thanks, this is great!
Is there a way to do a stacked chart for the plan and actual along with the variance? For example, the plan consists of 3 groups and we want to show the breakout for each group for the overall plan and same for actual.
Thanks!

Reply
Paul - March 4, 2014

I think the above is excellent

I am looking to graphically represent the % movement from a baseline target on a graph.

I have 10 departments. Each has a split of Permanent and Non Permanent staff. This gives a total of say 1000 people per department. Each department has a separate unique target of the % of non permanent staff they want to hit.

I am looking to show in a graph the movement from the base to their position now. Ideally i would like to show the movement in overall numbers but also in the % of non permanent staff change in that time period.

So the graph you have displayed is great, i just need to understand how to use it to best represent my problem

Any help would be gratefully appreciated!!

Reply
    Jon Acampora - March 9, 2014

    Hi Paul,

    Great question on how to add the percentage variance to the data labels.

    If you are using Excel 2013 there is a new feature that allows you to display data labels based on a range of cells that you select. It is the “Value From Cells” option in the Label Options menu.

    To display the percentage variance in the data label you will first need to calculate the percentage variance in a row/column of your data set. In the example file on this post, the percent variance is calculated in cells I6:I9 of the ‘Examples’ tab.

    Next you will right click on any of the data labels in the Variance series on the chart (the labels that are currently displaying the variance as a number), and select “Format Data Labels” from the menu.

    On the right side of the screen you should see the Label Options menu and the first option is “Value From Cells”. Click the check box and it will prompt you to select a range. Select the cells that contain the percentage variance, and click OK.

    You should now see both the percentage and number variance displayed in the data label. Checkout the screenshot on the link below.

    Display Percentage Variance on Excel 2013 Chart Screenshot

    You can then uncheck the Value option if you only want to display the percentage variance.

    If you have an older version of Excel this process is still possible, but a bit more time consuming. You can set each data label to a cell value.

    1. Click on a data label in the chart. Make sure only one data label is selected, NOT all labels in the series. You typically have to click the data label twice for the single selection.
    2. In the formula bar, type the equals symbol = then select the cell that contains the percentage variance value.
    3. Click Enter. The variance will be displayed in the label.
    4. Repeat steps 1-3 for each data label in your chart.

    Check out this screenshot for details.

    Set Data Labels to Cell Values Screenshot Excel 2003-2010

    The nice part about either of these methods is that the data labels are linked to the values in the cells. If your numbers change or you update the data, the labels will automatically be refreshed and display the correct results.

    Please let me know if you have any questions.

    Reply
Lizz - December 18, 2013

Thanks for the great explanation so far but I have a further question. Is there a way to do these bar graphs if the targets are negative numbers? For example, Budget was -100, actual was -50 so we be the budget by 50 positive. When I tried it the variance was on the positive side of the graph and the budget and actual bars were on the negative side. Since my base variance was negative, the actual variance did “add on” to it.

Any suggestions?

Thanks. Lizz

Reply
    Jon Acampora - December 18, 2013

    Hi Lizz,

    Great question! You will need to change a few formulas to get this to work. On the ‘Examples’ sheet of the file, make the following changes:

    – Cell F6: =MAX(C6:D6)
    – Cell G6: =IF(E6>0,-E6,””)
    – Cell H6: =IF(E6<0,E6,"")

    Use the formulas above when both numbers being compared are negative.

    If your chart contains both positive and negative sets then you will need to wrap the formulas in another IF statement to first check if the numbers are positive or negative.

    Here are the formulas to use when you have some rows that contain positives and some rows contain negatives:

    - Cell F6: =IF(MIN(C6:D6)>0,MIN(C6:D6),MAX(C6:D6))
    – Cell G6: =IF(MIN(C6:D6)>0,IF(E6>0,E6,””),IF(E6>0,-E6,””))
    – Cell H6: =IF(MIN(C6:D6)>0,IF(E6<0,ABS(E6),""),IF(E6<0,E6,""))

    You could also use the CHOOSE function instead of nesting the formulas in the IF statement. This would calculate faster if your workbook is very large and slow. I can provide more explanation on that if needed.

    Please let me know if you have any questions.

    Reply
      Lizz - December 19, 2013

      Thanks, that solved my problem! My data isn’t too complex so the IF statements were fine.

      Reply
Prabin - December 2, 2013

It works…Thanks John for your prompt response.

I like to visit other posts as well to find some new tricks.

Thanks Again!

Reply
Prabin - November 29, 2013

Hi john,

i want to show % variance instead of Absolute number. followed this method but did not find similar result as primary and secondary axis are different units.

can u please suggest…how to show % variance in same graph.

Reply
    Jon Acampora - December 1, 2013

    Hi Prabin,

    Great question. You can show the % variance by linking each data label in the variance series of the chart to a cell that calculates the variance.

    I updated the file (the one available for download on this page) with an additional chart on the ‘Examples’ tab that displays the variances.

    Here are some instructions on how to do this.
    1. In column I of the ‘Examples’ tab I added the % variance calculation.
    2. Click on the first variance label in the chart. This will select all the variance labels for that particular series (positive/negative).
    3. Click on the same label again. This will select only the single label, and all other labels in the series will be deselected.
    4. In the formula bar, type the equals sign (=), then select the cell that contains the % variance (cell I6 in the example).
    5. Click Enter. This will set the data label value equal to the cell value and keep chart label linked to the cell value through the formula. So if the numbers change in your source data the chart will automatically be updated.
    6. Repeat steps 2-5 for each variance label in the chart.

    If you are displaying the positive and negative variances in separate series to display in different colors, then you can add an additional column to calculate both the positive and negative variances. Then repeat the steps above for all value labels in both variance series.

    Excel 2013 has a built-in feature that makes this process much easier.
    1. If you are using 2013, right click on any one of the data labels and select “Format Data Labels” from the menu.
    2. In the Label Options menu on the right side you will see an option named “Values From Cells”.
    3. Click this selection and you will be prompted to select the range that contains the labels you want to display.
    4. Select the cells that contain your calculated percentage variances (column I in the example) and click OK.

    This is much faster than selecting each label and creating a formula to point to the variance calculation cell.

    Please let me know if you have any questions.

    Thanks!

    Reply
Ken Witchel - August 19, 2013

Is the download missing?

Reply
Diana - August 19, 2013

Haven’t tried it yet but this is exactly something my boss would be interested in. Can it be done in 2007? Based on your screenshots you’re using 2010 or higher. Thanks Jon!

Reply
    Jon Acampora - August 19, 2013

    Hi Diana,

    Yes, this technique will work in Excel 2007 and even 2003. I think your boss will be impressed. 🙂 Thanks!

    Reply

Leave a Reply: