Combination Area and Column chart

[McKenny]

Hi all

I currently present a number of KPI measures using a combination line (to display quarterly results) and area (to display the two-year average) chart.

Is it possible to substitute the line chart for a column chart?

I've tried and am having difficulty in getting the area chart to fill to the to the edges.

I'm sure I haven't explained this too well, so please see the attached.

Any help appreciated.

 

[Hui]

McKenny

Firstly, Welcome to the Chandoo.org Forums

Select the chart
Right click on the Line Chart
Change Series Chart type
Select column

 

[McKenny]

Thanks Hui

Only issue is, that when I try and change the series chart type to Column, it impacts the Area chart and it doesn't fill to the sides of the plot area (see figure 2 of my attachment). It's largely an aesthetic issue and I've attempted to find a solution by exploring the Axis Options but to no avail. I was hoping there was some sort of trick that I've missed.

 

[Hui]

Can you post the file so we can work on real data?

 

[McKenny]

Hi Hui

Thanks again for looking into this. Please see attachment.

 

[Nebu]

Hi:

This is a cheeky way of doing it, not perfect but will give you desired results.I have created a separate graph for area and made it 50% transparent and gave an illusion of bar submerged in the area graph You will have to update both the graphs separately if a new year is added , if you want to implement this method use named ranges to make the graph dynamic so that you do not have to manipulate the graphs manually if new data points get added.( Give a search in Google for dynamic charts for making the graphs dynamic)

Thanks

 

[r1c1]

Good solution @Nebu

Another variation is to use 2 dummy values both at start and end of series to cheat the area chart and stretch it all the way. See attached workbook.

 

[Hui]

How's this in a single chart:

I added blank columns before and after the data
see attached file:

 

[McKenny]

Many thanks Hui