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

C

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.
 
D

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.
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table date format 1
Date prob with Pivot Table 1
Pivot Table Date Format 2
Pivot table Average with [h]:mm format 5
Pivot Table Field Number 1
Pivot Table - Currency Format 1
date filter in a pivot table 1
Convert mm-dd-yy to mmm so pivot table reads mmm not mm-dd-yy 1

Top