blank lines in column data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to make a simple pie chart with 2 colums of data. One column is the
names and the other is the data. However, there are blank spaces in the
columns. Can I get excel to ignore these blanks. Of course they don't show
up in the pie since they have no value but the legend still has a spot for
them. Perhaps it is just a matter of getting them out of the category labels.

apples 50
bannanas 20

oranges 70
grapes 10

The manual way I have used is to only select the cells with data. But if
the data changes and other cells become non-blank then I have to go through
and reselect. The example above is pretty simple. The actual columns are
about 200 cells long with about 10 to 20 non-blank cells in the list. The
colums were created by taking the most significant entries out of two other
columns.
 
Hi,

One way would be to use Auto Filter. Create a helper column with a
formula that contains a 1 or a 0 if a label is present. The auto filter
on 1's
Something like this in column C, if your labels and data are in A:B
=IF(A1="",0,1)

Even with the 10 to 20 entries removed a pie with 100+ slices just seems
the wrong choice of chart.

Cheers
Andy
 
Hi Andy,
Auto Filter does provide a nice solution and I don't even have to create a
helper column. In fact I don't even have to extract the original data
creating blank lines. I can filter on the values choosing greater than or
equal to some number and get the rows I am interested in. Thanks a bunch.

Oh, and there are only 10 or 20 slices in the pie. Now by changing the
filter I can easily modify the significant value I want .

joe
 
Hi,

Glad it worked.

On re-reading your post I see I missed the 'non-blank' part, which does
indeed make a big difference <g>

Cheers
Andy
 
Back
Top