Hi,

I've got a dashboard system in excel which cycles through about 400 salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their sales
may be investment products, with the remaining 5% split between all the other
types, e.g. pensions, annuities, protection etc. On a pure pie, this is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to an
empty bar chart and a stack of superimposed bar chart labels. It looks awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values from
the series, or delete them altogether, but where this has to work for 400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then return
"") and the same on the value, but that makes the data table rubbish as the
table shows year on year comparison, so missing titles are not a cunning
plan...

Anybody got any ways around this?

Thanks.

Hi,

If you remove the zero entries form the chart the labels and leader lines
will not appear.
You can use Autofilter to do this using a custom filter of "is greater than"
0

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi,

I've got a dashboard system in excel which cycles through about 400
salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which
products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their sales
may be investment products, with the remaining 5% split between all the
other
types, e.g. pensions, annuities, protection etc. On a pure pie, this is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of
the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to an
empty bar chart and a stack of superimposed bar chart labels. It looks
awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values
from
the series, or delete them altogether, but where this has to work for 400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then
return
"") and the same on the value, but that makes the data table rubbish as
the
table shows year on year comparison, so missing titles are not a cunning
plan...

Anybody got any ways around this?

Thanks.

Hi Andy.

That's a really good idea.

Just one more thing - the bar of pie has two leader lines from the pie to
the bar, showing how it "explodes" out of it. If the bar chart is absent
because e.g. there are no values below 5%, then it is not shown, but the
leader lines still are.

I considered turning them white or removing them, but then it wasn't clear
that the bar chart was an expansion of the contents of the pie...

Am I trying to have my cake AND eat it?

"Andy Pope" wrote:

Hi,

If you remove the zero entries form the chart the labels and leader lines
will not appear.
You can use Autofilter to do this using a custom filter of "is greater than"
0

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi,

I've got a dashboard system in excel which cycles through about 400
salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which
products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their sales
may be investment products, with the remaining 5% split between all the
other
types, e.g. pensions, annuities, protection etc. On a pure pie, this is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of
the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to an
empty bar chart and a stack of superimposed bar chart labels. It looks
awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values
from
the series, or delete them altogether, but where this has to work for 400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then
return
"") and the same on the value, but that makes the data table rubbish as
the
table shows year on year comparison, so missing titles are not a cunning
plan...

Anybody got any ways around this?

Thanks.

For me when the bar has no data the bar appears as a diagonally shaded box
with no lines.
Which version of xl are you using.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi Andy.

That's a really good idea.

Just one more thing - the bar of pie has two leader lines from the pie to
the bar, showing how it "explodes" out of it. If the bar chart is absent
because e.g. there are no values below 5%, then it is not shown, but the
leader lines still are.

I considered turning them white or removing them, but then it wasn't clear
that the bar chart was an expansion of the contents of the pie...

Am I trying to have my cake AND eat it?

"Andy Pope" wrote:

Hi,

If you remove the zero entries form the chart the labels and leader lines
will not appear.
You can use Autofilter to do this using a custom filter of "is greater
than"
0

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi,

I've got a dashboard system in excel which cycles through about 400
salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which
products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their
sales
may be investment products, with the remaining 5% split between all the
other
types, e.g. pensions, annuities, protection etc. On a pure pie, this
is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of
the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to
an
empty bar chart and a stack of superimposed bar chart labels. It looks
awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values
from
the series, or delete them altogether, but where this has to work for
400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then
return
"") and the same on the value, but that makes the data table rubbish as
the
table shows year on year comparison, so missing titles are not a
cunning
plan...

Anybody got any ways around this?

Thanks.

2002.

Don't worry too much - I think we're going to go back to a pure pie.
Anybody can understand those! :-)

Cheers,

Tom.

"Andy Pope" wrote:

For me when the bar has no data the bar appears as a diagonally shaded box
with no lines.
Which version of xl are you using.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi Andy.

That's a really good idea.

Just one more thing - the bar of pie has two leader lines from the pie to
the bar, showing how it "explodes" out of it. If the bar chart is absent
because e.g. there are no values below 5%, then it is not shown, but the
leader lines still are.

I considered turning them white or removing them, but then it wasn't clear
that the bar chart was an expansion of the contents of the pie...

Am I trying to have my cake AND eat it?

"Andy Pope" wrote:

Hi,

If you remove the zero entries form the chart the labels and leader lines
will not appear.
You can use Autofilter to do this using a custom filter of "is greater
than"
0

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Hi,

I've got a dashboard system in excel which cycles through about 400
salesmen
and produces individual dashboards for the lot.

Because of this, they all have to work the same way.

We've been looking at having a chart to pictorally represent which
products
they sell.

Initially, i looked at a pie, but ran into a problem - 95% of their
sales
may be investment products, with the remaining 5% split between all the
other
types, e.g. pensions, annuities, protection etc. On a pure pie, this
is
unworkable as it looks awful, so I switched to bar of pie.

Using bar of pie wasw great. I said that anything with less than 5% of
the
total should end up in the bar, so small stuff was still visible.

Then I ran into some of our salesmen who only actually sell a couple of
products, say a 50:50 split. This splits the pie, leaving 2 leaders to
an
empty bar chart and a stack of superimposed bar chart labels. It looks
awful.

Can I suppress the leader lines and labels for zero entries?

I know that if it was a static chart, I could simply remove the values
from
the series, or delete them altogether, but where this has to work for
400
people, this simply isn't an option.

Likewise, I've tried (against the labels) =IF(the value is zero then
return
"") and the same on the value, but that makes the data table rubbish as
the
table shows year on year comparison, so missing titles are not a
cunning
plan...

Anybody got any ways around this?

Thanks.