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.

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 wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

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" wrote:

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 wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

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

cmgedc wrote:
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" wrote:


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 wrote:

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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com