Data in my pivot table changes to zeros

I have created a pivot table using a dynamic named range as my source. The source data consists of 52,416 rows and 10 columns showing:

Membership code
Date
Hour
Day
Week
Month
Term
Year
Checkouts
Checkins

Everything appears as expected when I go through Excel's PivotTable wizard but the Sum of Checkins and Sum of Checkouts (which I place in the data area of the PT) always come up as zeros. The PT seems to have replaced all the correct data in each cell with a zero!

In addition, I have to manually change the fields to "Sum of..." as opposed to the "Count of..." that appears first. It feels as though this screwy behaviour must have a bearing on why all my data is being replaced with zeros.

I should add that this has never happened to my other PTs. In fact, I have a PT in the same workbook with exactly the same columns and layout with a similar amount of data that works perfectly. I just can't figure it out!

The checkouts and checkins numbers in the source data may be text,
instead of numbers. To convert them to real numbers, you can use one of
the techniques shown here:

http://www.contextures.com/xlDataEntry03.html


MyrtlePeacock wrote:
> I have created a pivot table using a dynamic named range as my source.
> The source data consists of 52,416 rows and 10 columns showing:
>
> Membership code
> Date
> Hour
> Day
> Week
> Month
> Term
> Year
> Checkouts
> Checkins
>
> Everything appears as expected when I go through Excel's PivotTable
> wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I place
> in the data area of the PT) always come up as zeros. The PT seems to
> have replaced all the correct data in each cell with a zero!
>
> In addition, I have to manually change the fields to "-Sum of-..." as
> opposed to the "-Count of-..." that appears first. It feels as though
> this screwy behaviour must have a bearing on why all my data is being
> replaced with zeros.
>
> I should add that this has never happened to my other PTs. In fact, I
> have a PT in the same workbook with exactly the same columns and layout
> with a similar amount of data that works perfectly. I just can't figure
> it out!
>
>


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

Thanks for the advice. I used the "Paste Special" method to convert the text numbers into real numbers on my data list and then updated my PTs. All the zeros were gone and I now have the correct data where it should be.

Thanks again.

You're welcome. Thanks for letting me know how you solved the problem.

MyrtlePeacock wrote:
> Thanks for the advice. I used the "Paste Special" method to convert the
> text numbers into real numbers on my data list and then updated my PTs.
> All the zeros were gone and I now have the correct data where it should
> be.
>
> Thanks again.
>
>


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