Dietmar P. came up with a nice cell based chart at (external link!) Controller Akademie.

I applied some changes and enhancements like introducing auto-scaling and adding two parameters:

  • A number format to allow for a general format change if necessary

  • A Boolean parameter to control whether to switch the green and red colouring of the chart. If you hire a contractor then you might like to show staying below the budget with a green colour, but if you like to present sales figures then it might be the other way round

Budget_vs_Actual_Chart

Formula in H9:

=REPT(TEXT(F9-D9,Format)&" ",F9-D9<0)&REPT(Symbol,(F9-D9<0)*ABS(ROUND(F9-D9,0))*Scaling_Factor)

In I9:

=REPT(Symbol,(F9-D9>0)*ABS(ROUND(F9-D9,0))*Scaling_Factor)&REPT(" "&TEXT(F9-D9,Format),F9-D9>0)

In sheet Param all parameters have been collected:

Cell_based_charts_Param_Screen

These parameters have been named as shown in the Name Manager window:

Cell_based_charts_Name_Manager

A possible switch between colours green and red is implemented via a conditional format, for example in column I:

Cell_based_charts_Conditional_Format

Please read my Disclaimer.

Cell_based_charts.xlsx [20 KB Excel file, open and use at your own risk]