To create a variance chart

[Ecel Dumbo]

Hello Friends,

Could you please kindly refer the attached file and kindly help advise how to build a variance chart as shown in the sheet ?

Any help will be greatly appreciated.

Regards,
Excel Dumbo

 

[Hui]

See attached
Variance is a Named Formula = Actuals-Budget
Then add a new series to the chart and select what each series should be displayed as

 

[Ecel Dumbo]

Thank you very much HUi. This is exactly what i wanted. But I have one more question in an example who had solved for me. Graph you had solved yesterday (link in your post). The month of July is a forecast. How Will i change the column bar just for the month of July to Dotted lines

 

[Hui]

Do you mean Forecast instead of Budget?
If so add a new Series

 

[Ecel Dumbo]

HI Hui, I have posted in the other topic, please see when you get some time. One last question regarding the file you solved above. I would like the Variance to be (Actual/Budget)-1 and to appear as %, when I did this, variance bars are refering to Dollar values on Y axis, how can i create another Y axis probably on the right side to show %

 

[Hui]

Select the Chart
Up/down arrow and select the variance series
Ctrl 1
Select Secondary Axis
Change Axis Max/Min
Change number format to Percentage

 

[Ecel Dumbo]

Thank you very much HUi for your wonderful help with my questions

 

[Centralpark]

Hi Hui, Can you please help me understand how to develop this chart? I have pretty simple data from various countries - i have list of projects that different countries are working on, their forecasted budget and actual spent. I can use the variance chart to show that. Having countries in drop down will be an added advantage to the graph.