MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Charts


Posted by Erica on August 07, 2001 2:03 PM

I need to know how to create a custom chart. I want to combine a clustered column and a stacked column.


Posted by Mark W. on August 07, 2001 3:59 PM

Such combination charts are not supported.

Posted by AB on August 08, 2001 9:54 AM

Hello Erica and Mark,

You CAN combine a clustered column with a stacked column chart. You just need to assign one of the chart types to a secondary axis. If you snchronize the two axes and perhaps even hide one of them, you'll have your custom chart.

If anyone needs to see an example, I'd be happy to send it upon request.

Regards,
AaronThe Excel Logic Page

Posted by Mark W. on August 08, 2001 12:18 PM

I believe you're mistaken. So, why don't you tell us
how you'd create a Column-Stacked Column combination
chart?

Posted by AB on August 08, 2001 3:16 PM

I already mentioned the methodology. You assign one of the chart types to a secondary axis. But, maybe it's not what you all had in mind.

I'll put a link to a GIF image below of what I'm talking about and you can critique that if you like.

I guess if all else fails you could do some sort of multi-chart image overlay. I've done it before but admittedly it's a bit hacky even if automatically synchronized with VBA.

Regards,
Aaron


The Excel Logic Page
http://geocities.com/aaronblood

I believe you're mistaken. So, why don't you tell us

Posted by Mark W. on August 08, 2001 3:44 PM

Aaron, please be patient with me... I've been
using Excel for 15 years and it's always been my
understanding that you can't mix these two
chart types in a single combination chart. This
belief is in part founded on the fact that Excel
doesn't list this as a built-in combo chart.
I must admit that it's possible to get 4 series
onto a chart and assign the last 2 to the secondary
axis and format them as Stacked Columns; however,
how do you get these to coexist side-by-side.
Both occupy the center of each category value.
This makes for a very cluttered, unappealing chart.
I can't imagine why anyone would favor such
presentation over separate charts -- one for each
chart type.

I'd very much like to see what you produced, but
when I click on your link to your GIF I get a
"Page is not available" message. I already mentioned the methodology. You assign one of the chart types to a secondary axis. But, maybe it's not what you all had in mind. I'll put a link to a GIF image below of what I'm talking about and you can critique that if you like. I guess if all else fails you could do some sort of multi-chart image overlay. I've done it before but admittedly it's a bit hacky even if automatically synchronized with VBA.

Posted by AB on August 09, 2001 6:24 AM

Agreed, it might be too cluttered.

However, by sheer luck, you also used an example of the one combination that actually can be set up to look like a side-by-side (if you don't mind the stacked column in the middle).

If you setup the combo chart as you have stated:

"4 series onto a chart and assign the last 2 to the secondary axis and format them as Stacked Columns"

Select the stacked columns and set overlap = 100 and gap width = 490

Select the non-stacked coluns and set overlap = -100 and gap width = 140

Obviously a hacky thing to do, but it was fun anyway. Aside from that you're right Excel is lacking and all you could do is a multi-chart overlay. Sometimes overlays are fun to do just to hear people say, "Hey how'd you combine those charts."

Yes I know, overlays are hacky. Yes I'd prefer that Excel had more features. But, if you can find a way to build a chart that other developers have given up on then you're one step ahead. One time I was asked to create a combo bubble chart with connecting lines between the bubbles. Everyone else told them: "You can't do a combo like that."

Since I've had to do it more than a few times now I'm even considering developing an addin that will facilitate the creation of multi-chart overlays.

Talk to you later,
Aaron

BTW: Sorry about the image file not showing up. Geocities can be hit or miss sometimes. Try just looking at the root directory. It should at least allow that:

http://www.geocities.com/SiliconValley/Hills/6814/images/

Aaron, please be patient with me... I've been


Posted by Mark W. on August 09, 2001 6:40 AM

Are you familiar with the work of Edward Tufte?

However, by sheer luck, you also used an example of the one combination that actually can be set up to look like a side-by-side (if you don't mind the stacked column in the middle). If you setup the combo chart as you have stated: "4 series onto a chart and assign the last 2 to the secondary axis and format them as Stacked Columns" Select the stacked columns and set overlap = 100 and gap width = 490 Select the non-stacked coluns and set overlap = -100 and gap width = 140 Obviously a hacky thing to do, but it was fun anyway. Aside from that you're right Excel is lacking and all you could do is a multi-chart overlay. Sometimes overlays are fun to do just to hear people say, "Hey how'd you combine those charts." Yes I know, overlays are hacky. Yes I'd prefer that Excel had more features. But, if you can find a way to build a chart that other developers have given up on then you're one step ahead. One time I was asked to create a combo bubble chart with connecting lines between the bubbles. Everyone else told them: "You can't do a combo like that." Since I've had to do it more than a few times now I'm even considering developing an addin that will facilitate the creation of multi-chart overlays. Talk to you later, BTW: Sorry about the image file not showing up. Geocities can be hit or miss sometimes. Try just looking at the root directory. It should at least allow that: http://www.geocities.com/SiliconValley/Hills/6814/images/


Posted by AB on August 09, 2001 7:01 AM

I got the book.

Is there a page you'd like me to look at.

Or do you suggest I just reread the whole thing a few times. :)

I presented an alternative to "It can't be done".
Let's just leave it at that. BTW: Sorry about the image file not showing up. Geocities can be hit or miss sometimes. Try just looking at the root directory. It should at least allow that


Posted by Mark W. on August 09, 2001 7:12 AM

Actually... it's 3 BOOKS now...

> Is there a page you'd like me to look at.

No, I was just wondering if you're familiar with
his work. I'm a big fan!

> Or do you suggest I just reread the whole thing a few times. :)

Heavens, no... I was just wondering what he'd
have to say. He is such a minimalist!

Posted by AB on August 09, 2001 8:23 AM

Tufte

Well let's see, what would Tufte think?

He'd probably gag, especially if it were a 3D chart. :)

I admire his work as well. In fact, I even have a Tufte inspired 3D chart example on my website that shows why the things are just not that good to use.

I'll have to look around for his other books. The one I have is, "The Visual Dispaly of Quantitative Information" c1983 (reprint Jan 97)

Thanks for the book tip!

Talk to you later,
AaronThe Excel Logic Page: > Is there a page you'd like me to look at. No, I was just wondering if you're familiar with

Posted by Mark W. on August 09, 2001 8:47 AM

Re: Tufte

His others are...

"Envisioning Information" c 1990, 6th printing Feb. 1998
"Visual Explanations" c 1997, 3rd printing April 1998

He also gives seminars around the country for about
$400 which includes all 3 books. He's an excellant
presentor... well worth the price of admission.
I have subscriptions to "Scientific American" and
"Science News". When he comes to town I receive
2 brochures in the mail. I presume that he's
"mining" the scriber list for these magazines.