Zero values in Pivot Tables
 

Hi all,
I have a PivotTable:
1st col: 2nd col:
Dates(Row fields) Sales(Data fields)
01.01.08 100
01.02.08 150
01.03.08 0
01.04.08 120

And that one 01.03.08 row is automatically hidden, because it contains zero
value in its data field, consequently it will not be shown in diagram also.
Could you suggest me How can I show it in my PivotTable (Diagram)? The point
is that it's shown and checked in filter list, but anyway it's not shown in
result PivotTable.
thanks

Zero values in Pivot Tables
 

What version of Excel are you using?
Do you have a filter on the values?


alanas wrote:
Hi all,
I have a PivotTable:
1st col: 2nd col:
Dates(Row fields) Sales(Data fields)
01.01.08 100
01.02.08 150
01.03.08 0
01.04.08 120

And that one 01.03.08 row is automatically hidden, because it contains zero
value in its data field, consequently it will not be shown in diagram also.
Could you suggest me How can I show it in my PivotTable (Diagram)? The point
is that it's shown and checked in filter list, but anyway it's not shown in
result PivotTable.
thanks


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Zero values in Pivot Tables
 

1) I am using Excel 2003
2) Yes I have. And "01.03.08" is checked there to be shown. But anyway, that
is not displayed in the Rows field, because "Sales" equals to 0 (in Data
field).
So How can I solve it in PivotTables?

alanas

"Debra Dalgleish" wrote:

What version of Excel are you using?
Do you have a filter on the values?

Zero values in Pivot Tables
 

How are the zero rows being automatically hidden?

alanas wrote:
1) I am using Excel 2003
2) Yes I have. And "01.03.08" is checked there to be shown. But anyway, that
is not displayed in the Rows field, because "Sales" equals to 0 (in Data
field).
So How can I solve it in PivotTables?

alanas

"Debra Dalgleish" wrote:


What version of Excel are you using?
Do you have a filter on the values?




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Zero values in Pivot Tables
 

Dear Debra Dalgleish,

thank you for your responses and willingness to help. I understand my
problem and I think it's impossible to show that data in PivotTable. The
problem is not in the resulting PivotTable, that's in initial (source) data.
For example it looks like this:
City Type
NY a
NY a
NY b
Tokyo a
Tokyo a
Tokyo b
Tokyo c
Tokyo c

And from this table I'm building next PivotTable:

Page field: Cities: (All)

Rows field: Data field:
Types Sum of Types
b 2
a 4
c 3

But if I filter by NY (city). I will see next PivotTable:

Page field: Cities: NY

Rows field: Data field:
Types Sum of Types
b 1
a 2

But, anyway I wanted to see one more row like: c 0. Is it possible???

alanas.
P.S. I hope it was understandable.

"Debra Dalgleish" wrote:

How are the zero rows being automatically hidden?

alanas wrote:
1) I am using Excel 2003
2) Yes I have. And "01.03.08" is checked there to be shown. But anyway, that
is not displayed in the Rows field, because "Sales" equals to 0 (in Data
field).
So How can I solve it in PivotTables?

alanas

"Debra Dalgleish" wrote:


What version of Excel are you using?
Do you have a filter on the values?




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Zero values in Pivot Tables
 

In the pivot table, double-click on the Type button in the row area.
Add a check mark to 'Show items with no data', then click OK
Then all the types should show, even if they have no data for the
selected city.

alanas wrote:
Dear Debra Dalgleish,

thank you for your responses and willingness to help. I understand my
problem and I think it's impossible to show that data in PivotTable. The
problem is not in the resulting PivotTable, that's in initial (source) data.
For example it looks like this:
City Type
NY a
NY a
NY b
Tokyo a
Tokyo a
Tokyo b
Tokyo c
Tokyo c

And from this table I'm building next PivotTable:

Page field: Cities: (All)

Rows field: Data field:
Types Sum of Types
b 2
a 4
c 3

But if I filter by NY (city). I will see next PivotTable:

Page field: Cities: NY

Rows field: Data field:
Types Sum of Types
b 1
a 2

But, anyway I wanted to see one more row like: c 0. Is it possible???

alanas.
P.S. I hope it was understandable.

"Debra Dalgleish" wrote:


How are the zero rows being automatically hidden?

alanas wrote:

1) I am using Excel 2003
2) Yes I have. And "01.03.08" is checked there to be shown. But anyway, that
is not displayed in the Rows field, because "Sales" equals to 0 (in Data
field).
So How can I solve it in PivotTables?

alanas

"Debra Dalgleish" wrote:



What version of Excel are you using?
Do you have a filter on the values?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Zero values in Pivot Tables
 

Thank you a lot. Now it works.

I have one more question about charting:
I'm using XY diagram. And X line is growing from 0 to 500 by step 100, e.g.
0 100 200 300 400 500. But on diagram I will never have a point for 200. So
is it possible to skip the number 200? Of course the distance between 100 and
300 hundred must be 200, only without the number "200".


"Debra Dalgleish" wrote:

In the pivot table, double-click on the Type button in the row area.
Add a check mark to 'Show items with no data', then click OK
Then all the types should show, even if they have no data for the
selected city.

Zero values in Pivot Tables
 

You could use a Custom number format to hide the 200:

Double-click on the X axis, to open the Format Axis dialog box
On the Number tab, click on the Custom Category
In the Type box, enter: [=200]"";General
Click OK

alanas wrote:
Thank you a lot. Now it works.

I have one more question about charting:
I'm using XY diagram. And X line is growing from 0 to 500 by step 100, e.g.
0 100 200 300 400 500. But on diagram I will never have a point for 200. So
is it possible to skip the number 200? Of course the distance between 100 and
300 hundred must be 200, only without the number "200".


"Debra Dalgleish" wrote:


In the pivot table, double-click on the Type button in the row area.
Add a check mark to 'Show items with no data', then click OK
Then all the types should show, even if they have no data for the
selected city.




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html