pivot table counting blank cells in data table

Hi all,

I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

I have been able to fix this by putting on a filter in the data table, highlighing all the blank cells only and then clearing them column by column.

The problem is the data table is 50 columns long, so it is quite time consuming to do this manually. The data table gets updated regularly so this is an ongoing problem.
Does anyone know of a faster solution to fix this problem?

many thanks,

Nicko

Try checking you pivot table settings... could be your row or column fields are set to include blank cells.

Also check the formatting of the "blank" cells that are being pasted into the data range.

Post a sample .xls and this will get resolved in no time.

here is an example of of my problem...

the pivot table is picking up every cell

any ideas?

many thanks,

Ok.. I see now. You are geting these results because you are counting Severns, Fens, & Cullum. You can't count those because they are column headers, not data. The items under these headers is what is being counted: AMBROSE, AMION, etc.

What end result are you trying to achieve? Is it that you want a count of Severns, Fens, & Cullum by Primary Category regardless of the data in this categories?