Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using text values in Pivot Tables | Excel Discussion (Misc queries) | |||
Calculating from values in pivot tables | Excel Worksheet Functions | |||
Pivot Tables - Ranking Values | Excel Discussion (Misc queries) | |||
Pivot tables with zero/null values | Excel Discussion (Misc queries) | |||
Supress Zero Values on Pivot Tables | Excel Discussion (Misc queries) |