Pivot Table problem, blank fields aren't being added

[Ant]

Hi,

I have a Pivot table pulling data from another workbook.
It's displaying a count for given values in 2 columns.
It's fine for most of the data, but when one of the columns is blank, it
shows a count of 0, even though there are items that are blank.
If I double click on the field, it shows me a sheet that contains the data
that isn't being counted. Is this a bug in the Pivottable? Is there a way
around it?

Thanks.
Ant

[Debra Dalgleish]

A pivot table can't count blanks. You could type a space character in
those cells, or use a formula: =""

Then, they'll be counted.

Ant wrote:
> Hi,
>
> I have a Pivot table pulling data from another workbook.
> It's displaying a count for given values in 2 columns.
> It's fine for most of the data, but when one of the columns is blank, it
> shows a count of 0, even though there are items that are blank.
> If I double click on the field, it shows me a sheet that contains the data
> that isn't being counted. Is this a bug in the Pivottable? Is there a way
> around it?
>
> Thanks.
> Ant
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Ant]

Thanks for your reply. But then why is there a category for blanks
automatically added to the pivot table and when I click on show data, it
shows the rows that are blank?

Thanks.

"Debra Dalgleish" wrote:

> A pivot table can't count blanks. You could type a space character in
> those cells, or use a formula: =""
>
> Then, they'll be counted.
>
> Ant wrote:
> > Hi,
> >
> > I have a Pivot table pulling data from another workbook.
> > It's displaying a count for given values in 2 columns.
> > It's fine for most of the data, but when one of the columns is blank, it
> > shows a count of 0, even though there are items that are blank.
> > If I double click on the field, it shows me a sheet that contains the data
> > that isn't being counted. Is this a bug in the Pivottable? Is there a way
> > around it?
> >
> > Thanks.
> > Ant
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

The record has other fields with data, so when you show the detail, it
can show the complete record.

If there's no Customer name, it will show (blank) as the Customer item.
But, if you add Customer to the data area, as Count of Customer, it
can't count anything for those blank cells. You could add a different
field, that has data in every row, to the data area, and use it to get
the count.

Ant wrote:
> Thanks for your reply. But then why is there a category for blanks
> automatically added to the pivot table and when I click on show data, it
> shows the rows that are blank?
>
> Thanks.
>
> "Debra Dalgleish" wrote:
>
>
>>A pivot table can't count blanks. You could type a space character in
>>those cells, or use a formula: =""
>>
>>Then, they'll be counted.
>>
>>Ant wrote:
>>
>>>Hi,
>>>
>>>I have a Pivot table pulling data from another workbook.
>>>It's displaying a count for given values in 2 columns.
>>>It's fine for most of the data, but when one of the columns is blank, it
>>>shows a count of 0, even though there are items that are blank.
>>>If I double click on the field, it shows me a sheet that contains the data
>>>that isn't being counted. Is this a bug in the Pivottable? Is there a way
>>>around it?
>>>
>>>Thanks.
>>>Ant
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html