Zero values in Pivot Tables

[alanas]

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]

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

 

[alanas]

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]

How are the zero rows being automatically hidden?

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

:

 

[alanas]

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]

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]

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]

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