Clubbing blocks of Data for two entities in a Column Chart

Clubbing blocks of Data for two entities in a Column Chart

I need to show graphically the comparison between two LOAN amounts for a Housing LOAn..

I am almost done with the MATH part part of it, however need to compare the results Graphically...

I have two Blocks of DATA primarily:

OLD LOAN:
Prepayment Fees :
Processing Fees :
Service Tax :
Total Interest Due :
Total Cost Due :

NEW LOAN:
Prepayment Fees :
Processing Fees :
Service Tax :
Total Interest Due :
Total Cost Due :

I need to use Column Charts with only two entities and not 10 when seperated as there are 10 elements for Graph...

The reason of showing only two is that OLD as well as NEW LOAN is comprised of same elements as shown above..and is shown in one scale...

Please find the attachment for reference:

Something like this stacked column chart?

Originally Posted by Andy Pope

Something like this stacked column chart?

Hey Thanks Andy, that exactly what I wanted and I think it was data design which should be corrected..

In concentrating on the Main topic about Loans , almost forgot about removing redundancy in the Labels

How did you get the Labels : TOTAL DUE? on TOP with the Values and in the Legends table on the RIGHT its the only entry without the small squarenext to it..
This is still an unsolved mystery for me?
Andy , can you please throw some light on this..

I have got a solution for sure but when I do it my actual sheet I dont get it the way , with TOTAL DUES on top with the VALUES....

I would also welcome any suggestions/inputs to be added in the Comparison concept if any... for introducing FLOATING % as the EMI is always calculated based on the first RATE , TENURE and AMOUNT....however with the changein Interest rates , the No of EMIS, changes ( Increase or Decrease) so if anyone has better template or a suggestion..
Please feel free to share with me and the forum...

The totals is done by changing the chart type of that series from stacked column to line chart.

You can then format the line to have no marker or border. Apply data labels and position above with Series and Value displayed.

You will only get the ABOVE option if the series is a line.

See new attachment for step by step

I used line but you could have used a column chart on the secondary axis.

The criteria is that you want to display information about the total of the data in the chart without the total series distorting the rest of the charts data.
If you left it as a stacked column then all the other values would be squashed and the maximum value of the Y scale would be twice as big as necessary.

So changing the chart type or axis will get around this problem.