Scatter Chart & Bar Chart

[eoinymc]

Hi,

I want to amalgamate a scatter chart and a bar chart.

I have the following data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]09:00[/TD]
[TD]09:15[/TD]
[TD]10:00[/TD]
[TD]10:35[/TD]
[TD]11:00[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Sold[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

So basically, I want to see the following two graphs amalgamated...pictures attached... (View image: Total Sold & View image: London)

I was trying to do it on secondary axis on a scatter graph, but it just pushed all the data to one side for the primary axis and all the data to the other side for the secondary axis...anyone got any ideas?

Thanks,

Eoin

 

[cyrilbrd]

Hi,

Excel 2013
	A	B	C	D	E	F
1		9:00	9:15	10:00	10:35	11:00
2	London		5		4
3	Total	10		20		15
4	Sold	8		17		13
eoinymc

it might not look exactly the same as your Horizontal axis should contain the following 9:00, 9:15, 10:00, 10:35 and 11:00.
given so the range for the horizontal axis is =Sheet1!$B$1:$F$1
Series Total is =Sheet1!$B$3:$F$3
Series Sold is =Sheet1!$B$4:$F$4
Series London is =Sheet1!$B$2:$F$2
Chart type is at first a Clustered Column chart (sold and Total) then once London is added change the type to Combo and set london as Stacked Line with Marker and set line color to 'no line'.
Adjust Plot series Overlap to 0% and Gap Width to 0%.

 

[eoinymc]

Thanks, but that's not exactly what I want...I want to only see hours at the bottom,

i.e. 09:00, 10:00, 11:00

and then have the total and sales with zero gap, but see the London part at 09:15, i.e. in between 09:00 & 10:00...

Does that make sense? So literally, put the first chart on top of the second chart in my initial pictures.

Thanks,

Eoin

 

[Jon Peltier]

You need to use two different data ranges, one for the column chart, one for the XY scatter points.

Select the first range, insert a column chart (top left). Format the columns with a lighter fill so the added points will be easily visible, overlap 100%, gap width 5% (top right). You had 0% in your example, but I put in a small gap so the hours are more easily seen.

Select and copy the second data range, select the chart, use Paste Special to add the data as new series, series data in rows, series name in first column, category labels (X values) in first row. Excel adds this as a new column chart series (middle left).

Right click the new series, select Series Chart Type from the pop up menu, then select the XY Scatter type (middle right). Excel plots this series on the secondary axes.

Delete the secondary (right hand side) vertical axis, format the secondary (top) horizontal axis so the minimum is 0.375 (9/24, which is 9:00 am), maximum is 0.5 (12/24, or 12:00 noon), and major unit is 0.04166666 (1/24, or 1 hour) (bottom left).

Hide the top axis (don't delete it!) by setting axis tick labels to None and line color to None. Format the XY points so they appear clearly (bottom right).

 

[eoinymc]

Absolutely perfect, I appreciate your explanation and the thoroughness of it!

Brilliant!