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?

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?

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?

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?

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

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

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

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

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