Clustered Column Chart-displaying bars side by side (1 Viewer)

[Tim L]

Hello O venerable people,

It's a long time since I have posted in these forums, and I don't recall ever having made my way into the Excel forums before, so Hi!

Having done a couple of searches (and scanned over 500 thread headers) I have to come to the conclusion either I'm not searching right, or no-one else has come here to ask about this issue before.

The question:
How do you get MS Excel 2010 to display a bar chart with two both primary and secondary vertical axes and still display the bars side by side?

The problem:
I have a set of data which I wish to plot using the Clustered Column style chart. There are two sets of date to plot vertically with wildly differing scales values so I need both a primary and secondary axis, which is where the problem comes in...

The programmers of Excel have decided, in their infinite wisdom, that as soon as the secondary axis is brought into play, the bars should be displayed one-behind the other, rather than side by side, as is the case before the secondary axis is brought into play. This is an issue because one bar can obscure another, and it happens either way around so some data will become hidden. It is possible to adjust column widths, colours, and transparency levels but that is not the ideal solution as it's still not really clear enough.

The "Separated" value for "Series Overlap" does not appear to have any affect at all when the bars have been arranged this way.

Happily, OpenOffice.org Calc, back in Version 3.3.0, will do the same, but those wonderful people have a "Show bars side by side" option. Yeay. Unfortunately I need to be able to replicate the side by side in Excel 2010 in work, where I won't be able to have OpenOffice.org installed and it would be naughty to send the date home to work on, so that's a no, no. It has however given me the chance to put an example of what I'm looking to achieve, into the attached.

The attached has a randomly generated set of example data and charts showing the full set of data with bars overlapping, and the smaller sets highlighting the side by side and obscuring. I also tested with the smaller set of data to see if the chart would behave differently with less data. Apparently not.:banghead:

 

[Rx_]

It is not just you (sorry), it is as you say the new Excel options.
I too had to settle for something less than expected.
If you are trying to use automation and advanced charts, the group over at Mr Excel have done well for me.
It is my guess that the Excel Chart was downgraded (or wasn't upgraded) so it would fit the new Web services better.

This guy offers some free and for-profit tools. In this day and age, it pays to know who is ahead in the Excel specilization. This is just a FYI.
ExcelUser.com
After five months of hard work, I've finally posted a new web design at www.ExcelUser.com. The site is much better-organized and the content is much easier to read. You'll find many pages that still have the old design, but I'm working on them!

Your New Download Credentials

Your subscription to this email newsletter now gives you access to many downloads. Here are your credentials (to share with others):

User name: user
Password: rain

(I live on the coast in the Pacific Northwest of the United States. So when my IT guy called to ask what I wanted to use for the password, I was looking out a window at our soggy August weather, and "rain" was the first word that popped into my head.)

These credentials will give you full access to the Excel downloads here. The downloads include the first two chapters of Dashboard Reporting With Excel, which I know you'll find interesting if you haven't read my book already.

I'll change the download credentials once in a while, so be sure to read future editions of Excel for Business to learn the new ones.

By the way...Feel free to forward this message to your friends and co-workers who use Excel, so they can get a copy of the downloads for themselves. However, please remind them that if they register for Excel for Business they'll be able to download new files each time I change the credentials.

Alert...Be sure to "whitelist" exceluser.com with your email system. If you don't, your email server's overly aggressive spam filter could block future editions of this newsletter. If you use a public email provider, you can google: yahoo email whitelist, google email whitelist, or whatever, to learn how to do it.

Sorry I can't be of more help: Rx_

 

[Tim L]

Ahhh, shame. As I was beginning to fear though. Thanks for responding.

 

[Tim L]

Okay, so thinking on it more (Rx_'s post helped nudge me back into action), I have now found a work-around (funny how these things take a while to come to you, three hours of hard work previously and nothing, half an hour now and *ping*).

Anyway, this is somewhat convoluted, hence being a work-around and not a true solution (for which I would like to see an option built into Excel which enables the required display format to be achieved, rather than having to follow the procedure detailed below).

1. Compile your data.
2. Insert a set of data with zero values.
3. Generate the chart.
4. Apply the secondary axis as required.
5. Edit the series gap width to make both bars visible.
6. Edit the legend to delete the name of the zero value range.

You may be able to change the order of some bits to suit your preference or perhaps add the zero value data after, making sure you put it in such that the bars are displayed correctly.

It would be nice to have a better solution. If OpenOffice.org can solve it with a simple check box why can't Microsoft???