Indzara

How to Create a Column Chart Comparing Three Measures in Excel?

This article in our Data to Decisions series walks you through a step-by-step process to effectively display Actual vs Budget vs Forecast data, complete with percentage variances, to bring clarity to your financial narratives and empower stakeholders with the insights needed for strategic planning and review.

Actual vs Budget vs Forecast

In Excel, with simple modifications to a column chart, this unique visualization can be built within minutes.

The concept for this chart is originally from an article from excelcampus.com.

Let’s get started!

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Check our detailed YouTube video on the same here:

Step 01:

Our sample data contains the list of products, their sales actual, budget, and forecast data.

Actual vs Budget vs Forecast sample data

Select all the data and insert a 2D column chart.

Actual vs Budget vs Forecast insert 2D cluster

This inserts a default chart based on our data.

Default clustered column chart

Step 02:

Since our final chart consists of a single column, right-click on any of the series, and go to Format Data series. This opens the format pane.

Actual vs Budget vs Forecast format data series

Under the series options, Adjust the series overlap to 100%.

Actual vs Budget vs Forecast set series overlap to 100%

After this, our chart is modified like this:

Actual vs Budget vs Forecast after formatting data series

Step 03:

i. Click on the Actual series and modify the fill color to a lighter blue (choose the color based on your theme or the emphasis you want to build) from the format pane.

Actual vs Budget vs Forecast format data point

Note: If the format pane is not present to the right of your screen, click anywhere on the chart and press  CTRL+1.

ii. From the format pane, select the budget series:

Actual vs Budget vs Forecast choose budget series

Remove the fill color while keeping the borders thicker and in a different color (a darker blue).

Actual vs Budget vs Forecast remove fill color

 

iii. Similarly, choose the Forecast series, remove the fill color, choose a different border color (green), and most importantly, choose the exact width as that of the budget series.

After this step, our chart looks like this:

Actual vs Budget vs Forecast modified chart

Step 04:

The aim is to only have the horizontal blue and green lines to show the budget and the forecast respectively. We’ll look at the technique to “remove” these vertical lines.

i. Add series to the data which will hide (or superimpose) the vertical border lines. Call it “Background”. This series will have the maximum of the actual, budget, and forecast and increase this value by 10% or any value based on the magnitude of your data.

Cells C, E, and F have the actual, budget, and forecast respectively.

Actual vs Budget vs Forecast add background column

ii. Right-click on the chart, and Select data

Actual vs Budget vs Forecast select data

In the pop-up that pens, add a new series

Actual vs Budget vs Forecast add new series

Add the background series, and the horizontal axis remains the same.

Actual vs Budget vs Forecast add background series

With this addition, our chart gets modified a bit:

Actual vs Budget vs Forecast after adding new series

Step 05:

Let’s format this Background series in such a way that the vertical border lines from the budget and the forecast series disappear.

i. Choose the background series from the format pane. In the Fill & line, remove the fill color and include a solid border line with the same thickness as that of the budget and forecast series (i.e. 2), and most importantly, the border color is chosen as white.

Actual vs Budget vs Forecast remove fill color

ii. From the format pane, choose Chart area and modify the fill to white color

Actual vs Budget vs Forecast choose chart area

Actual vs Budget vs Forecast fill color

iii. Repeat the above step for the Plot area as well.

Actual vs Budget vs Forecast format plot area

iv. Click on gridlines and remove them

Actual vs Budget vs Forecast remove gridline

After this step, our chart is getting into shape:

Actual vs Budget vs Forecast after formatting

Step 06:

Click on the actual series and from the “+” icon from the top right corner of the chart, choose to add data labels.

Actual vs Budget vs Forecast add data labels

Click and position the labels to the inside base from the format pane.

Actual vs Budget vs Forecast position labels

At this stage, this is our chart:

Actual vs Budget vs Forecast after formatting background series

Step 07:

Before we proceed further, let us format this chart.

i. Click on the legends and move them to the top.

Actual vs Budget vs Forecast legend position to top

Click and delete the Background legend as it adds no meaning.

ii. Add a suitable title, say “Product sales – actual vs budget vs forecast” and format the same as well.

Actual vs Budget vs Forecast add chart title

Step 08:

Create an additional series for the percentage variance from the budget.

That is (Actual-Budget)/Budget

Actual vs Budget vs Forecast create additional series for % variance

Similarly, we can arrive at the % versus forecast, but the recommendation here is to display only what is the crux of your analysis and hence, not clutter your visual.

To add this as data label, follow the steps:

i. From the format pane drop-down choose Budget series. In the “+” icon from the chart, choose to add data labels.

Actual vs Budget vs Forecast add data labels

This adds the budget values as the labels, but we need the % vs Budget value. To get this, click on the chart, and from the format pane, select “Values from cells” from the label options.

ii. Add the values that we’ve just calculated.

Actual vs Budget vs Forecast add calculated values

Adjust the font size according to the importance these values carry to your analysis or the emphasis you want to create using this Actual vs Budget vs Forecast chart.

iii. As a final step of formatting, change the chart border by clicking on the chart and modifying the following in the format pane:

Actual vs Budget vs Forecast format chart area

With this, our final chart is ready to be included in your reports!

Actual vs Budget vs Forecast

With this visual, we can quickly analyze like, that product B is about -6% behind the budget value etc.

Check our 1-page, downloadable illustrative guide explaining all the steps for a quick reference.

If you have any feedback or suggestions, please post them in the comments below.

To get our FREE downloadable Illustrative Guide for about 32 unique Column Charts, enter your email here!

Leave a Reply

Your email address will not be published. Required fields are marked *