A nice way of comparing a budget against the actual outcome is using column charts where you see the actual outcome as dark bars and the “unused” budget as a more transparent bar. This is easy done by creating several data series and stacking them ontop of each other.

In this example we’ll start with some data where we have months, a budget per month and actual outcome per month. In this example we’ll use the same budget amount every month (100), but this can also be done where you have a budget that’s varying between the months (for example if the amount is depending on the number of available days in a month or if the amount perhaps is depending on seasonal variations). But as mentioned, to make this an easy example we’ll use 100 for each month.

The actual outcome has been randomized so it’s some months below the budget and some months above. We want the difference between the actual and budget to be a light blue (transparent blue) color for the months when we’re below budget and for the months where we’re above budget we want the bar colored red.

We need to add three columns to create the chart we want. The first column is the dark blue bar we want for the actual outcome when it’s below the budget.

In the next column we want the “unused” budget to show up (the transparent blue bar).

The last and third column is the red bar for when the actual outcome is above the budget.

Now create a bar chart using the data. You can use both 2D and 3D stacked charts, but don’t use the 100% stacked column charts because they won’t work in this case.

Now you should have something like this. Click on the blue (Budget) and the red (Actual) series and press delete, because we don’t want those two series in this chart (all the other series we have in the chart are based on those two, so that’s why we don’t need them).

Right click on each of the data series and select Format Data Series. On the first series we want to change color to Solid and blue under the fill option.

For the second series we want to set the color to the same blue solid color, but we also need to change the transparency to about 60%.

For the third series we set the color to solid red. Now we have something like this, where the “unused” budget is transparent and the months where we have spent more than budget have a red bar above the budget amount.

If you would like to check out the excel file I worked in during this example, you can download it from here.