Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Budget vs Actual Chart in Excel

The most important task done in Microsoft Excel is creating a chart visualizing display of the actual vs budget or forecasted values. Even after creating a chart, you have to make it lucrative so that you can collect an appraisal from the audience. Today in this article, I am sharing with you how to create a budget vs actual chart in Excel. Stay tuned!


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What Is Budget vs Actual Chart?

In order to determine the difference or variance between the forecasted and actual value, we need a budget vs actual chart. With this chart, you can easily visualize the differences between two comparative data tables. Mostly, business organizations make this chart using Excel to see if the forecasted amount is less or much than the actual amount.


2 Quick Methods to Create a Budget vs Actual Chart in Excel

In the following, I have described 2 quick and simple methods to create a budget vs actual chart in Excel.

Suppose we have a dataset of some Budget Amount and Actual Amount month wise. Now we will create a budget vs actual chart in our worksheet.


1. Use Bars to Create a Budget vs Actual Chart

In this method, you will learn about making a budget vs actual chart in a quick and easy way so that you can save a lot of time. Simply, use bars to create charts with proper information. Follow the steps below-

Step 1:

  • First, selecting data choose a “2-D Column” from the “Insert” option.

Use Bars to Create a Budget vs Actual Chart

  • Second, you will see a chart created in your worksheet.

Use Bars to Create a Budget vs Actual Chart

  • It’s time to make it presentable. Start with, choosing an “Actual Value Bar” and clicking the right button of the mouse.
  • From the appeared option press “Format Data Series”.

Use Bars to Create a Budget vs Actual Chart

  • Next, in the right pane, click the “Secondary Axis” to overlap.

  • In summary, your chart will look just like the following screenshot.

Use Bars to Create a Budget vs Actual Chart

Step 2:

  • This time we will edit the “Budget Bars” by selecting it and choosing “Format Data Series” from the appeared options.

  • Similarly, from the right pane change the “Gap Width” to “100%”.

Use Bars to Create a Budget vs Actual Chart

  • Thus, our budget variance chart will get a mature look just like the below screenshot.

Step 3:

  • If you want you can remove the horizontal axis value. Here I have selected some values and removed it by pressing the DELETE key from the keyboard.

Use Bars to Create a Budget vs Actual Chart

  • Thereafter, we will change the format of the chart. For that, select the chart and click “Format Data Series” from multiple options.

  • Hence, choose the “Fill” feature and select a color of your choice from the pane.

Use Bars to Create a Budget vs Actual Chart

  • To furnish with, change the “Chart Title” according to your demand.
  • Finally, we have successfully created a budget vs actual chart in Excel.

How to Create a Budget vs Actual Chart in Excel

Read More: How to Create Actual Vs Budget Variance Reports in Excel


Similar Readings


2. Use Marker Lines to Create a Budget vs Actual Chart

In order to make your chart more appealing to your boss, you can utilize the marker lines to see the actual trend of the table. Follow the instructions below-

Step 1:

  • To start with, selecting the table from the worksheet choose a “2-D Column” from the “Insert” feature.

Use Marker Lines to Create a Budget vs Actual Chart

  • Now, a chart will be created automatically with the chosen data.
  • Hence, selecting the chart click “Select Data” to add more data inside the chart.

  • From the newly appeared window click on the “Add” option.

Use Marker Lines to Create a Budget vs Actual Chart

  • In the “Edit Series” dialog box provide a “Series Name” of your choice and then in the “Series values” part choose “Budget Amount” values from the table.

Use Marker Lines to Create a Budget vs Actual Chart

  • As you can see, we have added a new series of data into the chart.

  • In the same fashion, we will add another data series with the “Actual Amount” value.
  • To finish with, press OK.

Use Marker Lines to Create a Budget vs Actual Chart

Step 2:

  • Here we will edit the newly added series by choosing a bar from the chart and clicking the “Change Series Chart Type” feature.

Use Marker Lines to Create a Budget vs Actual Chart

  • In the new popped-up dialog box select “Line” for the “S1” and “S2” series.
  • Gently, hit the OK button to continue.

  • From the “Chart Elements” feature add “Up/down Bars” by check marking it.

Use Marker Lines to Create a Budget vs Actual Chart

  • Finally, we will add and edit the marker in our chart by moving to the “Format Data Series” option.

  • Next, from the right pane, choose “Marker” from the “Fill” option.
  • Hence, choose your desired icon from the “Type” option and change the size from the “Size’

Use Marker Lines to Create a Budget vs Actual Chart

  • In conclusion, our final budget vs actual chart is in our hands without any hesitation. Simple isn’t it?

Use Marker Lines to Create a Budget vs Actual Chart

Read More: How to Make a Budget Line Graph in Excel


Conclusion

In this article, I have tried to cover all the methods to create a budget vs actual chart in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo