Create a customized trapezium chart

**Helios86** · 04-03-2012, 06:40 PM

Hello to everyone and thanks in advance to anyone who will help me on the following.

I'd like to create a trapezium chart to observe some variances about data (I attach a simple example; the reality is much more complex, but I'd just like to know how to build it). The bottom base represents the sum of initial data (budget), while the upper base will represent the sum of final data (actual); this way, you can immediately understand which costs have been increased/emphasized during a period and which ones, on the contrary, have been reduced.
The chart I would like to get (and to make automatic) is like the following image:

Trapezium_paint.jpg

Can anyone help me to understand whether is this chart possible to build or not (and, if yes, how to do it)?

Thank you very much.

Helios

How to create a trapezium chart.xlsx

**pimpmychart** · 04-04-2012, 09:42 AM

Hi Helios86,

"XY scatter with lines" is the chart type you should be using in order to build such a chart.
I quickly tried to reproduce your sample image (see attached).

Regards

Holger

**Helios86** · 04-04-2012, 05:25 PM

Dear pimpmychart,
it's exactly what I was looking for and I thank you very much.
But I have two more questions to make the chart perfect for my purpose (I tried to make it on my own but I didn't manage to achieve my result):

1) is there a way to equalize the colours for each part? I mean, for example: 100 (blue, bottom base) which becomes 300 (blue, upper base); 100 (red, bottom base) which becomes 300 (red, upper base) and so on. This way I could better show the effect of an increase/decrease of an item;
2) is there a way to make appear the name of the item below/above each part of line, as per my image? 100 (Cost 1, bottom base) which becomes 300 (Cost 1, upper base); 100 (Cost 2, bottom base) which becomes 300 (Cost 2, upper base) and so on.

Thanks in advance.

Helios

**pimpmychart** · 04-06-2012, 05:24 AM

Hi Helios,

I am not quite sure how you want the colors, but you can change the color of each line with a right-click.
Or do you want the colors change automatically based on current values? That's a bit more tricky.

I added the labels as per your sample (see attached).
I used XY scatters for those, so you can easily move them around by changing the values in cell range C25:D35.

Regards

Holger

**Helios86** · 04-08-2012, 01:54 PM

Dear Holger,
the labels are perfect and I'll use this method for my analysis.

About the colours, in my analysis I would like to stress on the concept that each cost in budget becomes higher/lower in actual, applying a colours' differentiation on the two horizontal lines only. Therefore: the piece of line on Lower Horizontal which corresponds to "Cost 1 (bdg)" shall have the same colour of the related piece of line on Upper Horizontal which corresponds to "Cost 1 (actual)", and so on with the other costs, in order that every cost has its own colour which is the same on both the lower and the upper side.
I'm not sure this is possible, as I can't change the colour of each part of horizontal lines corresponding to different costs. Do you have any other idea about this? Would you need another image which can help you?

Thank you very much for your support.

Regards.

Helios

**pimpmychart** · 04-09-2012, 02:31 AM

Hi Helios,
I think now I understood.
This did not work in my previous version because both horizontal lines represent one data series which can only have one color.
In order to be able to assign different colors, you need to split the lines in 3 different series (see attached).
Regards
Holger