Data Field in Pivot Table
 

Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?

Data Field in Pivot Table
 

To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:
Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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

Data Field in Pivot Table
 

Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:

To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:
Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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

Data Field in Pivot Table
 

In my sample file I had two fields in the data area, and right-clicked
on the heading for one of those fields, e.g. Sum of Units.

If you only have one field in the data area, you could right-click on
the Data field button, or select the specific cells that you want to
format. If you select cells, you may lose the formatting if the pivot
table is changed or refreshed.

GSpline wrote:
Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:


To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:

Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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





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

Data Field in Pivot Table
 

Ok, that made it clear & I was able to replace the 1's with Test. This seems
to only solve part of the problem, though. I only have 1 column heading,
"Situation", and this has 3 options for showing columns in the Data field,
"Situation1", "Situation2", and "Situation3". I want to be able to replace
the 1's in each of these data columns with labels (lets call them Test1,
Test2, and Test3), such that Test1 will replace any 1 in the Situation1
column, Test2 replaces any 1 in the Situation2 column, etc. Using the method
that you described I am forced to replace all 1's in all 3 columns with the
same value. Is there a way that I can have each of the 3 columns in the data
field have their own unique text?



"Debra Dalgleish" wrote:

In my sample file I had two fields in the data area, and right-clicked
on the heading for one of those fields, e.g. Sum of Units.

If you only have one field in the data area, you could right-click on
the Data field button, or select the specific cells that you want to
format. If you select cells, you may lose the formatting if the pivot
table is changed or refreshed.

GSpline wrote:
Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:


To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:

Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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





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

Data Field in Pivot Table
 

Click at the top of a pivot table column, to select it
Choose FormatCells
On the number tab, create the first custom format
Click OK
Repeat for the remaining columns.

GSpline wrote:
Ok, that made it clear & I was able to replace the 1's with Test. This seems
to only solve part of the problem, though. I only have 1 column heading,
"Situation", and this has 3 options for showing columns in the Data field,
"Situation1", "Situation2", and "Situation3". I want to be able to replace
the 1's in each of these data columns with labels (lets call them Test1,
Test2, and Test3), such that Test1 will replace any 1 in the Situation1
column, Test2 replaces any 1 in the Situation2 column, etc. Using the method
that you described I am forced to replace all 1's in all 3 columns with the
same value. Is there a way that I can have each of the 3 columns in the data
field have their own unique text?



"Debra Dalgleish" wrote:


In my sample file I had two fields in the data area, and right-clicked
on the heading for one of those fields, e.g. Sum of Units.

If you only have one field in the data area, you could right-click on
the Data field button, or select the specific cells that you want to
format. If you select cells, you may lose the formatting if the pivot
table is changed or refreshed.

GSpline wrote:

Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:



To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:


Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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




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





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

Data Field in Pivot Table
 

That did the trick. Thank you very much, Debra.

Also, I have found your website to be very helpful with other questions as
well.


"Debra Dalgleish" wrote:

Click at the top of a pivot table column, to select it
Choose FormatCells
On the number tab, create the first custom format
Click OK
Repeat for the remaining columns.

GSpline wrote:
Ok, that made it clear & I was able to replace the 1's with Test. This seems
to only solve part of the problem, though. I only have 1 column heading,
"Situation", and this has 3 options for showing columns in the Data field,
"Situation1", "Situation2", and "Situation3". I want to be able to replace
the 1's in each of these data columns with labels (lets call them Test1,
Test2, and Test3), such that Test1 will replace any 1 in the Situation1
column, Test2 replaces any 1 in the Situation2 column, etc. Using the method
that you described I am forced to replace all 1's in all 3 columns with the
same value. Is there a way that I can have each of the 3 columns in the data
field have their own unique text?



"Debra Dalgleish" wrote:


In my sample file I had two fields in the data area, and right-clicked
on the heading for one of those fields, e.g. Sum of Units.

If you only have one field in the data area, you could right-click on
the Data field button, or select the specific cells that you want to
format. If you select cells, you may lose the formatting if the pivot
table is changed or refreshed.

GSpline wrote:

Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:



To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:


Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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




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





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

Data Field in Pivot Table
 

You're welcome! Thanks for letting me know you solved it, and that the
information on my web site helped you.

GSpline wrote:
That did the trick. Thank you very much, Debra.

Also, I have found your website to be very helpful with other questions as
well.


"Debra Dalgleish" wrote:


Click at the top of a pivot table column, to select it
Choose FormatCells
On the number tab, create the first custom format
Click OK
Repeat for the remaining columns.

GSpline wrote:

Ok, that made it clear & I was able to replace the 1's with Test. This seems
to only solve part of the problem, though. I only have 1 column heading,
"Situation", and this has 3 options for showing columns in the Data field,
"Situation1", "Situation2", and "Situation3". I want to be able to replace
the 1's in each of these data columns with labels (lets call them Test1,
Test2, and Test3), such that Test1 will replace any 1 in the Situation1
column, Test2 replaces any 1 in the Situation2 column, etc. Using the method
that you described I am forced to replace all 1's in all 3 columns with the
same value. Is there a way that I can have each of the 3 columns in the data
field have their own unique text?



"Debra Dalgleish" wrote:



In my sample file I had two fields in the data area, and right-clicked
on the heading for one of those fields, e.g. Sum of Units.

If you only have one field in the data area, you could right-click on
the Data field button, or select the specific cells that you want to
format. If you select cells, you may lose the formatting if the pivot
table is changed or refreshed.

GSpline wrote:


Debra,
Your post sounds exactly like what I am looking for. Thanks. I am a little
confused though. When I right-click on the column heading and choose Field
Settings, a window opens that is named PivotTable Field. I must be missing
something because I do not see the Number button. The window contains a Name
field (column name), 3 radio buttons under Subtotals (Automatic, Custom,
Nome), a list box with Sum, Count, Average, Max, Min, Product, Count Nums,
StdDev, stdDevp, Var and Varp, a check box at the bottom left labeled Show
Items With No Data, and buttons on the right labeled Ok, Cancel, Hide,
Advanced, and Layout (grayed out). I tried clicking on various options, the
advanced button etc, but I have not been able to find the Number button.
What am I doing wrong, or what am I missing?

Thanks

"Debra Dalgleish" wrote:




To replace the 1s with "Test":
Right-click on the column heading, and choose Field Settings
Click the Number button, and select the Custom category.
In the text box, type: [=1]"Test";General
Click OK, click OK

GSpline wrote:



Is it possible to change data field values (i.e. if they all show as "1") to
something else on a per column basis? I have 3 different columns, and did
not put any data in the data field so that I can see when the row & column
data match but I need the 1's that appear for matches to be replaced with a
different word per column. Any ideas?




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



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




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





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