not showing pivot values of zero

[BorisS]

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[David McRitchie]

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

 

[BorisS]

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[David McRitchie]

Hi Boris,
I meant try it on the pivot table results because that is what you are trying
to modify the results of. Which filter -- choose something that works.

Might try making a helper column something like
H1: =SUM(A1:G1)

Or something that recognizes that there are 5 numeric columns
so you don't suppress descriptive information on rows with only titles
H1: =AND(SUM(A1:G1)=0, COUNT(A1:g1)=5)

The helper column would make it simple to use a basic filter.
select column H, data, filter, autofilter
click on the dropdown for the filter in column H and choose False

To get rid of the filter (just as important as knowing how to filter)
Data, Filter, autofiter (will remove the checkmark an the filter) :

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[BorisS]

Sorry, I should have been clear that I am actually a relatively advanced
user. So I wasn't asking how to use filters. Rather, I thought that there
was some special sort of pivot filter I hadn't heard about. But you brought
up a simple idea which I never even thought of doing before. Specifically, I
never thought of simply putting a filter onto a table sheet. I guess the
fact that you can change so little in a table made me mentally associate
pivots with static status. But you are right that filtering just changes the
hiding property, so that'll do just fine.

Thanks.
--
Boris

Hi Boris,
I meant try it on the pivot table results because that is what you are trying
to modify the results of. Which filter -- choose something that works.

Might try making a helper column something like
H1: =SUM(A1:G1)

Or something that recognizes that there are 5 numeric columns
so you don't suppress descriptive information on rows with only titles
H1: =AND(SUM(A1:G1)=0, COUNT(A1:g1)=5)

The helper column would make it simple to use a basic filter.
select column H, data, filter, autofilter
click on the dropdown for the filter in column H and choose False

To get rid of the filter (just as important as knowing how to filter)
Data, Filter, autofiter (will remove the checkmark an the filter) :

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[David McRitchie]

Hi Boris,
No problem, the extra few paragraphs will make it understandable
to few other people later on who may not be familiar with pivot tables
or filter.

 

[maplesugarsnow]

I just found this very useful!! I have been trying to figure out how to get
rid of all of the zeros for a long time and never thought of using such a
simple feature. Thanks!!

 

[Frederic]

This solution is not satisfactory to me, because:
1. it does not solve the calculation time issue
2. it hides the row tittel on the left when there is a zero on the same line

The issue is that the calculated item generates lines that did not exist
before; and even, if you double-click on the generated zero, you can see that
there is no data behind.

Can anyone truly solve this issue?

Hi Boris,
I meant try it on the pivot table results because that is what you are trying
to modify the results of. Which filter -- choose something that works.

Might try making a helper column something like
H1: =SUM(A1:G1)

Or something that recognizes that there are 5 numeric columns
so you don't suppress descriptive information on rows with only titles
H1: =AND(SUM(A1:G1)=0, COUNT(A1:g1)=5)

The helper column would make it simple to use a basic filter.
select column H, data, filter, autofilter
click on the dropdown for the filter in column H and choose False

To get rid of the filter (just as important as knowing how to filter)
Data, Filter, autofiter (will remove the checkmark an the filter) :

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[maplesugarsnow]

Once I started using it I also found problems. I had a list of companies
with mutiple types of balances. When I did the autofilter it would elimnate
not only the zeros but any company with any zero balance, even if the same
company had other balances that were not zero.

This solution is not satisfactory to me, because:
1. it does not solve the calculation time issue
2. it hides the row tittel on the left when there is a zero on the same line

The issue is that the calculated item generates lines that did not exist
before; and even, if you double-click on the generated zero, you can see that
there is no data behind.

Can anyone truly solve this issue?

Hi Boris,
I meant try it on the pivot table results because that is what you are trying
to modify the results of. Which filter -- choose something that works.

Might try making a helper column something like
H1: =SUM(A1:G1)

Or something that recognizes that there are 5 numeric columns
so you don't suppress descriptive information on rows with only titles
H1: =AND(SUM(A1:G1)=0, COUNT(A1:g1)=5)

The helper column would make it simple to use a basic filter.
select column H, data, filter, autofilter
click on the dropdown for the filter in column H and choose False

To get rid of the filter (just as important as knowing how to filter)
Data, Filter, autofiter (will remove the checkmark an the filter) :

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris


:

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.

 

[David]

Bump

....

I'm having the same problem. Adding a calculated item to my pivot table
adds all the sub items *as if I had clicked "show items with no data* ...
except that I haven't selected that.

Any thoughts anyone?

Best,
David

Once I started using it I also found problems. I had a list of companies
with mutiple types of balances. When I did the autofilter it would elimnate
not only the zeros but any company with any zero balance, even if the same
company had other balances that were not zero.

This solution is not satisfactory to me, because:
1. it does not solve the calculation time issue
2. it hides the row tittel on the left when there is a zero on the same line

The issue is that the calculated item generates lines that did not exist
before; and even, if you double-click on the generated zero, you can see that
there is no data behind.

Can anyone truly solve this issue?

Hi Boris,
I meant try it on the pivot table results because that is what you are trying
to modify the results of. Which filter -- choose something that works.

Might try making a helper column something like
H1: =SUM(A1:G1)

Or something that recognizes that there are 5 numeric columns
so you don't suppress descriptive information on rows with only titles
H1: =AND(SUM(A1:G1)=0, COUNT(A1:g1)=5)

The helper column would make it simple to use a basic filter.
select column H, data, filter, autofilter
click on the dropdown for the filter in column H and choose False

To get rid of the filter (just as important as knowing how to filter)
Data, Filter, autofiter (will remove the checkmark an the filter) :

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks, David.

Which filter there are you talking about? I see lots, and I am not sure if
you're talking about one that filters original data or the actual pivot.
LMK. Thx.
--
Boris


:

Hi Boris,
Did you try filtering the list see Debra Dalgleish's index page
http://www.contextures.com/tiptech.html

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am getting lots of values in my table which are zero. I have a calculated
item, and don't know if that is the problem. How, if at all, can I get any
line which has all zeros to not show up? the table gets excessively long
with them, not to mention the calculation takes forever.