| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table Data Field ... )2 | Excel Discussion (Misc queries) | |||
| Pivot table field data | Excel Discussion (Misc queries) | |||
| use field data from 1 pivot table in another? | Excel Worksheet Functions | |||
| use field data from 1 pivot table in another? | Excel Worksheet Functions | |||
| Pivot Table Field Data | Excel Worksheet Functions | |||
Linear Mode
