Pivot table formats text data with 2 decimals

My source table has a vlookup field using general format that looks up
a value in a separate table also using general format. The data is
either alphanumeric or numeric (ie: TX1 or 4, or 5, etc.). I have
tried changing the formats to text with the following results.

When I run a pivot table using the source table, one of the row fields
(for grouping) is this lookup field. Excel displays the results that
resemble numeric data with two decimals and offers no formatting in
the pivot table options (like it does for data fields).

I can change the field format, but refreshing the pivot table always
goes back to two decimals. I have turned preserve formatting and
other options off & on with the same results.

Any ideas what I'm doing wrong?

Thanks!

Don S

If you're using an older versions of Excel, when you're selecting cells
to format, use the Pivot Table selection, instead of selecting cells
with the Excel pointer:

You may have to Enable Selection first. (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)

Then, before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table.

Or, use the commands on the Pivot Table>Select menu, e.g. select the
entire table, then choose to Select Data.

Then apply the formatting, and it may stick.


Don S wrote:
> My source table has a vlookup field using general format that looks up
> a value in a separate table also using general format. The data is
> either alphanumeric or numeric (ie: TX1 or 4, or 5, etc.). I have
> tried changing the formats to text with the following results.
>
> When I run a pivot table using the source table, one of the row fields
> (for grouping) is this lookup field. Excel displays the results that
> resemble numeric data with two decimals and offers no formatting in
> the pivot table options (like it does for data fields).
>
> I can change the field format, but refreshing the pivot table always
> goes back to two decimals. I have turned preserve formatting and
> other options off & on with the same results.
>
> Any ideas what I'm doing wrong?
>
> Thanks!
>
> Don S


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

Bless you, Debra. That's the second time you've saved me from
insanity.

I am using Excel 2003 (office XP Pro). I don't know why that just
started happening. I did add one element to my lookup table, but
can't determine why it would have changed things. It is interesting
that some of my pivot tables in this workbook kept their formatting
while others only mis-formatted the new element. When I removed the
row from the pivot table & put it back, they all had the wrong format
: ( But that's another story.

Thank You!!!!

Don S

On Wed, 05 Oct 2005 17:17:33 -0400, Debra Dalgleish
<[email protected]> wrote:

>If you're using an older versions of Excel, when you're selecting cells
>to format, use the Pivot Table selection, instead of selecting cells
>with the Excel pointer:
>
>You may have to Enable Selection first. (From the Pivot toolbar, choose
>PivotTable>Select, and click on Enable Selection)
>
>Then, before formatting cells, use the selection feature to select the
>cells. For example, move the pointer to the top of a column in the pivot
>table (just above the column's heading cell). When the black arrow
>appears (like the one that appears when the pointer is over a column
>button), click to select the column in the pivot table.
>
>Or, use the commands on the Pivot Table>Select menu, e.g. select the
>entire table, then choose to Select Data.
>
>Then apply the formatting, and it may stick.
>
>
>Don S wrote:
>> My source table has a vlookup field using general format that looks up
>> a value in a separate table also using general format. The data is
>> either alphanumeric or numeric (ie: TX1 or 4, or 5, etc.). I have
>> tried changing the formats to text with the following results.
>>
>> When I run a pivot table using the source table, one of the row fields
>> (for grouping) is this lookup field. Excel displays the results that
>> resemble numeric data with two decimals and offers no formatting in
>> the pivot table options (like it does for data fields).
>>
>> I can change the field format, but refreshing the pivot table always
>> goes back to two decimals. I have turned preserve formatting and
>> other options off & on with the same results.
>>
>> Any ideas what I'm doing wrong?
>>
>> Thanks!
>>
>> Don S

You're welcome! Glad I could help again.

Don S wrote:
> Bless you, Debra. That's the second time you've saved me from
> insanity.
>
> I am using Excel 2003 (office XP Pro). I don't know why that just
> started happening. I did add one element to my lookup table, but
> can't determine why it would have changed things. It is interesting
> that some of my pivot tables in this workbook kept their formatting
> while others only mis-formatted the new element. When I removed the
> row from the pivot table & put it back, they all had the wrong format
> : ( But that's another story.
>
> Thank You!!!!
>
> Don S
>
> On Wed, 05 Oct 2005 17:17:33 -0400, Debra Dalgleish
> <[email protected]> wrote:
>
>
>>If you're using an older versions of Excel, when you're selecting cells
>>to format, use the Pivot Table selection, instead of selecting cells
>>with the Excel pointer:
>>
>>You may have to Enable Selection first. (From the Pivot toolbar, choose
>>PivotTable>Select, and click on Enable Selection)
>>
>>Then, before formatting cells, use the selection feature to select the
>>cells. For example, move the pointer to the top of a column in the pivot
>>table (just above the column's heading cell). When the black arrow
>>appears (like the one that appears when the pointer is over a column
>>button), click to select the column in the pivot table.
>>
>>Or, use the commands on the Pivot Table>Select menu, e.g. select the
>>entire table, then choose to Select Data.
>>
>>Then apply the formatting, and it may stick.
>>
>>
>>Don S wrote:
>>
>>>My source table has a vlookup field using general format that looks up
>>>a value in a separate table also using general format. The data is
>>>either alphanumeric or numeric (ie: TX1 or 4, or 5, etc.). I have
>>>tried changing the formats to text with the following results.
>>>
>>>When I run a pivot table using the source table, one of the row fields
>>>(for grouping) is this lookup field. Excel displays the results that
>>>resemble numeric data with two decimals and offers no formatting in
>>>the pivot table options (like it does for data fields).
>>>
>>>I can change the field format, but refreshing the pivot table always
>>>goes back to two decimals. I have turned preserve formatting and
>>>other options off & on with the same results.
>>>
>>>Any ideas what I'm doing wrong?
>>>
>>>Thanks!
>>>
>>>Don S
>>
>


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