Conditional formatting Excel budget vs actual

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:

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:

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:

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:

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

No ratings yet. Be the first to rate!