Pivot Table - NUMBER FORMAT option does not appear in field settin

[cmgedc]

Excel 2007 Pivot Table - The "Number Format" option does not appear in the
field settings for one of my columns that is formatted in the data table as a
number.

It is showing up as a date format by default.

I can change the format in the pivot table to number but then each time the
Pivot Table is changed or refreshed the format reverts back to the MM-YY
format.

 

[Debra Dalgleish]

If a field has blank cells or cells that contain text, you might not be
able to format the numbers, because that field is treated as Text data
type.

 

[cmgedc]

I double checked, no blank cells. Also I did a find format for text and none
was found in the column. I even inserted a new column and multiplied the old
column by a cell containing the number 1 formated as a number. Then I tried
inserting a new column and copying and pasting values then formatting as
number. No success.

Then I sorted the column and went through each entry and finally found the
culprit, it was a # Value entry. Deleted those rows and now it works.

Thank you for the tip, I had been trying to figure this out for a long time.

 

[Debra Dalgleish]

You're welcome, and thanks for explaining how you solved the problem.