Column chart with floating dots

[Texas Longhorn]

Hi all,

Sorry to ask what is probably a basic question, but I've had no luck searching google or the board. I have a clustered column chart in Excel 2010 with two series - one column for 3/31/2010 next to a second column for 3/10/11.

I would like to add two more series on the secondary axis. For these series, I'd like a dot aligned with the date-appropriate column.

To give an example (which will hopefully clarify the above), suppose the columns show temperature in degrees - one column for each date. Then, aligned with the date appropriate column would be a marker (secondary axis) for wind speed.

Any help would be great.

Thanks,
Bill

 

[Gerald Higgins]

Yes this can be done.

Add another chart series, make it refer to the 2nd Y axis, and change the chart type, just for that series, to Line, instead of Bar. Then, make sure the line has some kind of symbol for the data points - it will probably have some symbol anyway by default, and you can change it to a circle or something else. Finally, change the format for the line to no line, the line will disappear, but the symbols (dots) will remain.

 

[Texas Longhorn]

Gerald - thanks for the reply. When I implement your solution, both of the markers are aligned in the middle of the two columns as opposed to the 3/31/10 marker being centered with the 3/31/10 column and the 3/10/11 marker being centered with the 3/10/11 column. Is there a way to align the markers with the (horizontal) middle of the columns?

Thanks,

Bill

 

[Gerald Higgins]

I see.

Yes, this is do-able, but you'll need to change the way your data is laid out.

I'm guessing it's laid out something like this (very simple version) at the moment -

...............Series 1.....Series 2.....Series 3.....Series 4
Day 1.......5..............7..............5.5............7.5
Day 2.......6..............8..............6.5............8.5
and so on.

If you set your data up like this, it may help
...............Series 1.....Series 2.....Series 3.....Series 4
Day 1........5..............................5.5.................
(day 1b)....................7..............................7.5
(blank)...........................................................
Day 2 .......6..............................6.5.................
(day 2b)....................8..............................8.5
(blank)...........................................................

Does this make sense ?

 

[Texas Longhorn]

Gerald,

Thanks for the reply. Unfortunately, I'm not exactly following. Below is my actual data (I used the weather example above for simplicity, but the actual data might be more helpful now).

Comparison 20100331 to 20110310.xlsx
	A	B	C	D	E
6		3/31/2010	3/10/2011	3/31/2010	3/10/2011
7		Beta	Beta	MarginalExcessReturn	MarginalExcessReturn
8	10-yrYield	0.16	0.18	3.651	3.84
9	Oil	0.05	0.06	3.93	4.42
10	Commodity	0.05	0.22	4.46	4.74
ev Exposures (2)

So for each factor (10-yr yield, oil, and commodity - which would be on the x-axis), I would have two columns (3/31/10 Marginal Excess Return and 3/10/11 Marginal Excess Return), and two markers (RHS axis, 3/31/10 Beta and 3/10/11 Beta).

I'll keep tweaking my data to try to get it in a format similar to your suggestion, but any additional help would be great.

Thanks,
Bill

 

[Gerald Higgins]

I understand.
So get it like this if you can...
Row 1...................3/31/2010.........3/10/2011.........3/31/2010..........3/10/2011
Row 2....................Beta.................Beta................M.E.R................M.E.R.
Row 3..10yr yield......0.16......................................3.651.......................
Row 4..........................................0.18........................................3.84..
Row 5............................................................................................(all blank)
Row 6..Oil................0.05......................................3.93........................
Row 7..........................................0.06........................................4.42..
and so on...

 

[Texas Longhorn]

Excellent! I have it working just as hoped. Thanks for all the help Gerald!

 

[Gerald Higgins]

Great ! thanks for coming back to say thanks . . .