Here are five of the easiest to create and cleanest budget vs. actual (target) Excel charts. I spent three hours searching through different Excel techniques and methods on the web and picked these five for being easy to create and cleanly showing the differences between budget and actual values.
The world uses Excel’s budget vs. actual charts to monitor how far ahead or behind they are on budgets and projects. These charts are invaluable for prodding us to meet our goals.
Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.
I hope this list saves you time and boosts your productivity.
1. Use Floating Markers to Show Budget vs. Actual on Excel Charts
This is the basic actual vs. budget (target) chart we published in Critical to Success years ago. You should master this one before trying any others. It’s the basic black of Excel charts.
This is one of our most popular articles and ranks first on a couple of Google search result pages. It is quick and easy to create and people understand it naturally; the blue columns are actual values and the red markers are the budgeted or target values.
Here’s how to create a basic budget vs. actual chart in Excel.
2. Create Excel Charts and Dashboards with Color Alerts for Actual and Budget (Target) Values
When you want to show the variance between actual and budget (target) then use this Excel chart. This chart shows the difference between actual and budget (target), but with just a few mouse clicks it colors the negative values with a different color so they pop out.
Here’s how to show color alerts for budget vs. actual Excel charts.
3. Six Top Budget vs. Actual In-Cell Excel Charts
Chandoo has one of the most awesome all-around websites for Excel. He did a great job here showing six different ways of charting actual vs. budget in a limited amount of space. If you need column reports to show a lot of data these in-cell charts or icons allow managers to quickly see which line items are not meeting target. Chandoo uses Excel’s Sparklines to fit a chart in a cell.
Techniques he includes are,
- Conditional icons
- Conditional data bars
- In-cell bar charts
- Pie charts
I really don’t like to use these, but Consumer Reports seems to make this idea work well. 🙂
- Color scale or heat maps
This formatting trick can be expanded across a grid to create heat maps like those you see in financial and market reports. If you want to see how to do this let me know.
- Thermometer charts
As usual Chandoo, you’ve done a very thorough job!
Here’s how to show budget vs. actual Excel charts within a cell.
4. Show the Difference Between Budget vs Actual on a Clustered Column or Bar Excel Chart
I had never been to Jon Acampora’s site until I came across his method of putting the variance between budget and actual in an appropriately sized box. It’s a very straightforward and puts the variance box and numeric value right where your eye would expect to find it.
This is a quick and easy tip that can be used with columns or bars. I think a lot of managers would like to see this. Thanks Jon.
Here’s how to show variance on a clustered column or bar chart.
5. Show Budget vs Actual Variance with Eye Popping Color on Excel Line Charts
This is one of those “So cool” tricks that is a real head slapper. It looks like it takes some complex work, but it’s actually very easy. Although Chandoo is comparing productivity of a company to the national average in this example, his technique works just as well to compare budget vs. actual. In this example, Acme Inc.’s productivity is the black line. Above national average productivity produces green shading and below national average productivity produces red shading.
At the end of the same article Chandoo hints at four other ways of making similar budget vs. actual charts that use color to pinpoint the variance.
Here’s how to make a shaded Excel line chart that shows budget vs. actual and more.
- 1 1. Use Floating Markers to Show Budget vs. Actual on Excel Charts
- 2 2. Create Excel Charts and Dashboards with Color Alerts for Actual and Budget (Target) Values
- 3 3. Six Top Budget vs. Actual In-Cell Excel Charts
- 4 4. Show the Difference Between Budget vs Actual on a Clustered Column or Bar Excel Chart
- 5 5. Show Budget vs Actual Variance with Eye Popping Color on Excel Line Charts