Aggregating Data in Other Category on Pie Chart

[Thomas M.]

Excel 2007

Basic Question
----------------
Is there a way to make a pie chart ignore "empty" cells? Note that the
cells are not truly empty. The display of data in the cells has simply been
"blanked" out using this formula: =IF(B19<1000,"",B19)

More Information
------------------
I'm putting together a pie chart that will be updated monthly. There are 40
categories of data, and the values for those categories range from 1 ot
65,000. Obviously, this presents a problem in that the categories with low
values will be shown as pie slices so thin as to be basically imperceptible.
To solve this problem, I'd like to take all the categories where the value
is less than 1,000 and sum them up into one category called Other. I would
then like to include the Other category on the pie chart along with all the
individual categories that have values greater than 1,000.

An additional complicating factor is that the number of categories with
values greater than 1,000 will vary from month to month. This month, there
are 15 categories with values exceeding 1,000, but next month there could be
more of those categories, or less.

In an attempt to solve these two problems I've done the following:

1) At the bottom of the list of categories I've created an Other category.
The value is determined by
=SUMIF(B3:B43,"<1000"), which gives me the sum of all the categories having
values less than 1,000.

2) I created a calculated column that shows the category names only when the
value for the category > 1,000.

3) I created a calculated column that shows the value of the category only
when the value is > 1,000.

This gives me two columns that show the category names and values for just
those categories where the value is greater than 1,000. I then built my
chart using the two calculated columns, hoping that the rows which don't
display data would be ignored. Unfortunately, it doesn't seem to work that
way. What I ended up with is a chart that shows blank items in the legend.

How can I make it so that the chart shows only the categories where the
value is greater than 1,000, plus the Other category, and not show blank
items in the legend?

--Tom

 

[Bernard Liengme]

My reply is a little off topic so forgive me, please

A pie chart with 40 categories is unreadable; even 10 is to much
All the graph experts (Edward Tuffe, Stephen Few, etc) are very disparaging
of pi charts; the eye has difficulty comparing angles. Why not use a Column
chart?

As for the wide range of values; have you considered a logarithmic scale? In
Excel 2007 you can have not just logs to base 10, but to any other base --
base 2 is often a good choice.

To answer your question: filter the data to hide the low values and chart
what remains

best wishes

 

[Thomas M.]

Thanks for the feedback.

In regard to the chart type, I would argue that a column chart with 40
columns would be about as difficult to read. Also, these reports are going
to management, and I've learned that the vast majority of people in the
management ranks, at least at the places I've worked, can be shown one set
of data in a simple chart and they'll say it's too confusing, but if you
show them the SAME data set with a lot of eye candy (shading, color
transitions, nice fonts, and a pleasing color scheme) they'll somehow come
to the conclusion that the data is crystal clear. I call it the raccoon
effect. Just as raccoons are attracted to shiny objects, management appears
to be attracted to shiny charts! ;-)

I have not tried a logarithmic scale. I was not aware that Excel can do
that. I'll have to give it a try.

The filtering option worked for what I needed to do now. The deadline is
Friday, so improvements will need to come later.

--Tom