Wasim Akram
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.
- Second, you will see a chart created in your worksheet.
- 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”.
- Next, in the right pane, click the “Secondary Axis” to overlap.
- In summary, your chart will look just like the following screenshot.
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%”.
- 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.
- 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.
- To furnish with, change the “Chart Title” according to your demand.
- Finally, we have successfully created a budget vs actual chart in Excel.
Read More: How to Create Actual Vs Budget Variance Reports in Excel
Similar Readings
- How to Create an Operating Budget in Excel (with Detailed Steps)
- How to Prepare Annual Budget for a Company in Excel
- How to Create Renovation Budget in Excel (2 Simple Methods)
- Create Retirement Budget Worksheet in Excel (with Easy Steps)
- How to Create a Department Budget in Excel (with Detailed Steps)
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.
- 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.
- 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.
- 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.
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.
- 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.
- 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’
- In conclusion, our final budget vs actual chart is in our hands without any hesitation. Simple isn’t it?
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
- How to Create a Personal Budget in Excel (With Easy Steps)
- Make a Family Budget in Excel (2 Effective Ways)
- How to Make a Household Budget in Excel (2 Smart Ways)
- Prepare a Vacation Budget in Excel (with Easy Steps)
- How to Make a Budget in Excel for College Students (with Quick Steps)
- How to Make a Wedding Budget in Excel (2 Suitable Methods)
