Graphwork

[tanewha1956]

Hello

I am producing a simple spreadsheet for analysis of a clients life
insurance needs. The results within the sheet will then create a graph
showing the various areas they need to cover. I have done this in a
pie chart (pay mortgage, funeral costs, clear credit card debt etc)

The chart is just fine however because I have about 15 possible cells
to complete on the spreadsheet, depending on the client, some will
remain empty (as they have no such need) The problem i have is that
the resulting graph is cluttered up with legends for the '0' or zero
categories. Is there an easy way to select these to not appear? I only
want those with amounts above zero to show on the graph

Thanks heaps


Tony

 

[Guest]

Hi I don't think this is possible to automate the chart to display non-zero
entries only. The only way round it would be to put the zeros at the top or
bottom, perhaps by sorting, then re-create the chart excluding the zero
entries. It would be possible to write a macro that automates this, but
depending on how often the chart would need to be refreshed it'd probably be
best just to manually re-sort and re-create the chart as and when needed.

 

[Jon Peltier]

Remove the legend. Put custom labels into a cell next to the data. Suppose
your categories are in column A and the values in column B. Then in C1 enter
this formula

=IF(B1=0,"",A1&" ("&B1&")")

This makes C1 look blank unless there is a value in B1, then it gives you a
label like "Mortgage (1500)". You can get fancier with the label, of course.

Then use one of these free Excel utilities to apply the labels in column C
to the data in your chart:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

When a wedge isn't showing, its value is zero, so the data label doesn't
appear.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Not-so-Old Excel Veteran" <Not-so-Old Excel
(e-mail address removed)> wrote in message

 

[tanewha1956]

Remove the legend. Put custom labels into a cell next to the data. Suppose
your categories are in column A and the values in column B. Then in C1 enter
this formula

=IF(B1=0,"",A1&" ("&B1&")")

This makes C1 look blank unless there is a value in B1, then it gives you a
label like "Mortgage (1500)". You can get fancier with the label, of course.

Then use one of these free Excel utilities to apply the labels in column C
to the data in your chart:

Rob Bovey's Chart Labeler,http://appspro.com
John Walkenbach's Chart Tools,http://j-walk.com

When a wedge isn't showing, its value is zero, so the data label doesn't
appear.

- Jon

Thanks a lot for this, it is appreciated. I will work on this today.
What is the '&' for in the formula please. I am only just and so
getting into mre advanced formulas and not too sure on its relevance

T

 

[Jon Peltier]

The ampersand is used to join together bits of text.

- Jon