| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calcualted field formating
How do I make the format of a calculated field equal white if the value is
zero. or the same color as the cell background? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
There is no easy conditional formatting for pivot tables. The only solutions
that I can think of would be to conditionally format the entire column such that 0 values are White or (I have not tired this but it might work) you could change for formula to generate an error if the result is going to be zero. You can format errors to not display via table options. -- HTH... Jim Thomlinson "Candyman" wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
Perfect! I created an if statement if (x-y0,x-y,1/0). Worked like a charm.
"Jim Thomlinson" wrote: There is no easy conditional formatting for pivot tables. The only solutions that I can think of would be to conditionally format the entire column such that 0 values are White or (I have not tired this but it might work) you could change for formula to generate an error if the result is going to be zero. You can format errors to not display via table options. -- HTH... Jim Thomlinson "Candyman" wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
Thanks for replying... I was wondering if that would work...
-- HTH... Jim Thomlinson "Candyman" wrote: Perfect! I created an if statement if (x-y0,x-y,1/0). Worked like a charm. "Jim Thomlinson" wrote: There is no easy conditional formatting for pivot tables. The only solutions that I can think of would be to conditionally format the entire column such that 0 values are White or (I have not tired this but it might work) you could change for formula to generate an error if the result is going to be zero. You can format errors to not display via table options. -- HTH... Jim Thomlinson "Candyman" wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
If the pivot table is the only thing on the worksheet, you could hide
all the zeros: Choose ToolsOptions On the View tab, remove the check mark from Zero values Click OK Another option is to format the numbers to hide the zeros: Right-click on a cell in the calculated field Choose Field Settings Click the Number button In the Category list, choose Custom In the Type box, enter the format for positive, negative, and zero. For example: #,##0.00_);(#,##0.00); or 0;0; Click OK, twice. Candyman wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
Hey that is better, yet! I like that.
One other question if I could so ask. . . I have data fields A and B with the calculation Diff (a-b) totaled by category. The Diff field does not total. Any suggestions? "Debra Dalgleish" wrote: If the pivot table is the only thing on the worksheet, you could hide all the zeros: Choose ToolsOptions On the View tab, remove the check mark from Zero values Click OK Another option is to format the numbers to hide the zeros: Right-click on a cell in the calculated field Choose Field Settings Click the Number button In the Category list, choose Custom In the Type box, enter the format for positive, negative, and zero. For example: #,##0.00_);(#,##0.00); or 0;0; Click OK, twice. Candyman wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
If you use the Automatic subtotals, you should see a total for the
calculated fields. If you use a Custom subtotal, the calculated fields won't total. Candyman wrote: Hey that is better, yet! I like that. One other question if I could so ask. . . I have data fields A and B with the calculation Diff (a-b) totaled by category. The Diff field does not total. Any suggestions? "Debra Dalgleish" wrote: If the pivot table is the only thing on the worksheet, you could hide all the zeros: Choose ToolsOptions On the View tab, remove the check mark from Zero values Click OK Another option is to format the numbers to hide the zeros: Right-click on a cell in the calculated field Choose Field Settings Click the Number button In the Category list, choose Custom In the Type box, enter the format for positive, negative, and zero. For example: #,##0.00_);(#,##0.00); or 0;0; Click OK, twice. Candyman wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
That was it! I've been using Excel heavily for 9 years and have created MANY
VBA applications, report schedulers, templates, auto charting, etc, but I never had to go fancy into pivot tables. Learn something every day. :) "Debra Dalgleish" wrote: If you use the Automatic subtotals, you should see a total for the calculated fields. If you use a Custom subtotal, the calculated fields won't total. Candyman wrote: Hey that is better, yet! I like that. One other question if I could so ask. . . I have data fields A and B with the calculation Diff (a-b) totaled by category. The Diff field does not total. Any suggestions? "Debra Dalgleish" wrote: If the pivot table is the only thing on the worksheet, you could hide all the zeros: Choose ToolsOptions On the View tab, remove the check mark from Zero values Click OK Another option is to format the numbers to hide the zeros: Right-click on a cell in the calculated field Choose Field Settings Click the Number button In the Category list, choose Custom In the Type box, enter the format for positive, negative, and zero. For example: #,##0.00_);(#,##0.00); or 0;0; Click OK, twice. Candyman wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Calcualted field formating
You saved the best for last!
Candyman wrote: That was it! I've been using Excel heavily for 9 years and have created MANY VBA applications, report schedulers, templates, auto charting, etc, but I never had to go fancy into pivot tables. Learn something every day. :) "Debra Dalgleish" wrote: If you use the Automatic subtotals, you should see a total for the calculated fields. If you use a Custom subtotal, the calculated fields won't total. Candyman wrote: Hey that is better, yet! I like that. One other question if I could so ask. . . I have data fields A and B with the calculation Diff (a-b) totaled by category. The Diff field does not total. Any suggestions? "Debra Dalgleish" wrote: If the pivot table is the only thing on the worksheet, you could hide all the zeros: Choose ToolsOptions On the View tab, remove the check mark from Zero values Click OK Another option is to format the numbers to hide the zeros: Right-click on a cell in the calculated field Choose Field Settings Click the Number button In the Category list, choose Custom In the Type box, enter the format for positive, negative, and zero. For example: #,##0.00_);(#,##0.00); or 0;0; Click OK, twice. Candyman wrote: How do I make the format of a calculated field equal white if the value is zero. or the same color as the cell background? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I remove a Calculated Field from the Pivot Table field list | Excel Discussion (Misc queries) | |||
| Creating a Calculated Field in a Pivot Table for same field | Excel Discussion (Misc queries) | |||
| sum a pivot table field as a calulated field | Excel Discussion (Misc queries) | |||
| How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
| Calcualted Fields in Pivot Tables | Excel Worksheet Functions | |||
Linear Mode
