Skip to main content

Create Budget vs Actual Variance chart in Excel

In Excel, a Budget vs Actual Variance chart is used to compare two sets of series data, and display the difference or variance of the two data series. If the differences are negative values, one colored bars or columns are displayed, if positive values, another colored bars or columns are displayed as below screenshot shown. This article, I will talk about how to create the comparison variance chart and a mini in cell comparison variance chart in Excel workbook.


Create budget vs actual variance chart in Excel

To create the budget vs actual variance chart, please do with the following steps:

1. Enter the below formula into cell D2 to calculate the difference between the budget and actual values, and then drag the fill handle down to the cells you need, see screenshot:

=C2-B2

2. Then, select the data in column A and Column D, and then click Insert > Insert Column or Bar Chart > Clustered Column, see screenshot:

3. And a column chart is inserted, then you can delete the unwanted elements of the chart as you need, such as delete the chart title, vertical axis, gridlines, and you will get the result as below screenshots shown:

4. Then, right click the horizontal axis, and choose Format Axis option from the context menu, see screenshot:

5. In the opened Format Axis pane, under the Axis Options tab, click Labels to expand this section, and then select Low from the Label Position drop down list, and the horizontal axis will be placed at the bottom of the chart, see screenshot:

6. And then, right click any one of the bars, and choose Format Data Series option, see screenshot:

7. In the opened Format Data Series pane, under the Fill & Line tab, from the Fill section, please do the following operations:

  • Select Solid fill option;
  • Check Invert if negative check box;
  • Specify two colors for the positive and negative values separately from the Color drop down.

8. After setting the colors for the bars, then, you should add the data labels, please click to select the bars in the chart, and then click Chart Elements icon, then check Data Labels from the Chart Elements list box, see screenshot:

9. The column bars are so narrow that looks not beautiful, in this case, you can adjust the bar width to your need, please right click any one of the bars, and then choose Format Data Series, in the Format Data Series pane, under the Series Options tab, change the Gap Width to your need, this case, I will change it to 80%, see screenshot:

Tips:

After creating the chart, sometimes, you may want to show the data labels with different colors, for example, the positive data labels in the green color, and the negative data labels in orange color, please do as this:

Select the data in the Variance column from the original data table, and then press Ctrl + 1 to open the Format Cells dialog box, in the Format Cells dialog box, click Custom from the Category list box, and then type the below code into the Type text box:

[Color10]General;[Color53] -General

Then click OK button, and you will get the result as you need, see screenshot:

Note: If you need other colors, please view the following color index table:


Create mini budget vs actual variance chart in cells

If you want to insert a mini budget vs actual variance chart in cells, the following steps may do you a favor:

1. Enter the below formula into cell D2 to calculate the difference between the budget and actual values, and then drag the fill handle down to the cells you need, see screenshot:

=C2-B2

2. Then, display the negative values of the variance, please apply the following formula into cell E2, and then copy the formula to other cells you need, see screenshot:

=IF(D2<0,D2,"")

3. Display the positive values of the variance, please type the below formula into cell F2, and then copy the formula to other cells you need, see screenshot:

=IF(D2>0,D2,"")

4. And then, you should insert a specific symbol which will be used as the chart bars, please click Insert > Symbol, and in the Symbol dialog box, select Block Elements that you can find in almost every font from the Subset drop down list, and click the Full Block symbol, then click Insert to insert the symbol, see screenshot:

5. After inserting the special symbol, then, apply the below formula into cell G2, and drag this formula to other cells, and you will get the below result:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Note: In the above formula, E2 is the cell contains the negative variance value, A16 is the cell contains the specific symbol you are inserted, and the number 100 is a variable data which is used to calculate the number of the symbol based on the variance value, you can change it to your own.

6. Go on applying the below formula into cell H2, and then drag this formula to other cells, and you will get the below result:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Note: In the above formula, F2 is the cell contains the positive variance value, A16 is the cell contains the specific symbol you are inserted, and the number 100 is a variable data which is used to calculate the number of the symbol based on the variance value, you can change it to your own.

7. And then, select the cells in column G, and then click Home > Align Right to align the data right on the cells, see screenshot:

8. At last, you can format the font colors for the two columns separately as you need, and you will get the result as below screenshot shown:


Create budget vs actual variance chart with an amazing feature

Kutools for Excel provides dozens of special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy tool- Difference Compararison Chart, you can create a budget vs actual variance chart in worksheet or mini budget vs actual variance chart within cells quickly and easily. Click to download Kutools for Excel for free trial!


Download Budget vs Actual Variance Chart sample file


Video: Create budget vs actual variance chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations