#Monthly expenses chart how to
#Monthly expenses chart series

#Monthly expenses chart series

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).Ĭlick the Change Chart Type button on the design tab and change the chart type to a Clustered Column chart. Change the chart type for series 1 & 2 to a Clustered Column Chart.Repeat this for the Budget Series (series 2). Select the “Secondary Axis” radio button from the Series Options tab. Right-click on the Actual series column in the chart, and click “Format Data Series…” Series 1 (Actual) and Series 2 (Budget) need to be plotted on the secondary axis.The first step is to create a Stacked Column Chart and add the five series to it.The chart is not too difficult to create, and provides an opportunity to learn some advanced techniques. Creating the bar chart is the exact same process with stacked and clustered bars instead of columns.

#Monthly expenses chart how to

The example file ( free download below) contains step-by-step instructions on how to create the column version of this chart. 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. The negative variance needs to be plotted as a positive value to bridge the gap between the two series. 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 blank value will not be plotted on the chart, and no data label will be created for it. An IF statement is used to return a blank value if the variance is negative. Positive Variance – The variance is calculated as the variance between series 1 and series 2 (actual and budget).The bar in the chart is actually hidden behind the clustered chart. This gives you the value for plotting the base column/bar of the stacked chart. Variance Base – The base variance is calculated as the minimum of the two series in each row.The first step is to add three calculation columns next to your data table. This puts the stacked chart (variance) behind the clustered chart (budget & actual). 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.

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 utilizes two different chart types: clustered column/bar chart and stacked column/bar chart. The number of categories is only limited to the size of the chart, but typically you want to have five or less for simplicity. This can include a comparison of any data type: budget vs. This technique only works when comparing two different series of data. It is a simple report style with a column for the category names (regions) and two columns for the series data (budget & actual data). The following image shows an example of how the data should be organized on your sheet. With any chart, it is critical that the data is in the right structure before the chart can be created. Variance On Column Or Bar Chart Guide For Excel 2013.xlsx Download Data Requirements It can be Actual versus Target, Forecast, Goal, Milestone, etc. This chart works when comparing any two numbers. A sample workbook is available for download below so you can follow along. However, the variance can be added to the chart with some advanced charting techniques. This requires the reader to calculate the variance manually for each category.

The basic clustered chart displays the totals for each series by category, but it does NOT display the variance. In the example above, we are looking at the Actual versus Budget (series) across multiple Regions (categories).

The clustered bar or column chart is a great choice when comparing two series across multiple categories. Actual vs Budget or Target.Ĭlustered Bar Chart with Variance Overview This post will explain how to create a clustered column or bar chart that displays the variance between two series. Great for displaying budget vs actual variances. Includes a step-by-step tutorial and free workbook to download. Learn how to display the variance between two columns or bars in a clustered chart or graph.