Pivot Tables

[Guest]

When I create a pivot table - there is a column included with "Comments".
Where ever there are no comments in the data table - the pivot table has
"(blank)". For items that have no data I want to just show a blank.

In Pivot Table Options - for empty cells - I put in a space and yet I keep
seeing text - blank.

Thanks in advance for the help.

 

[Guest]

I can think of two other options you might use:

1)Massage your source data.
-Edit>GoTo>Special Cells: blanks, click [OK]
-Type an apostrophe then hold down [Ctrl]+[Enter]

OR

2)Add a column of formulas to your source data that will display labels (if
they exist) or empty text ("") if they don't and use that column instead of
the data column
Example: =if(isblank(B1),"",B1)

Does that help?

 

[Guest]

The Blank setting you are refering to pertains to blank data items, not
categories.
You should be able to click the drop-down at the top of the category and
uncheck Blank to hide those items.

Does that help?

 

[Guest]

Dipsy, give a man a fish or teach a man to fish, as they say...

The reason it works is because it operates on the same logic my suggestion
gave you. Namely, when you go to special, you are selecting all cells that
are blank. When you enter an apostrophe in a cell, it converts from being a
literally blank cell to being a cell with an apostrophe (which in Excel, does
not actually print, but rather indicates the cell is a text value, more or
less). When you have a highlighted area and you enter something into one of
the cells and then hit ctrl-enter, as opposed to enter, it enters the thing
you just typed into the entire highlighted area. So what you accomplished
was to turn all blank cells into cells that think they have effectively an
invisible character in them.
--
Boris

Hi Ron:

Thanks a ton. This worked. I tried Option 1. I donot know though why
typing apostrophe and then holding down ctrl and enter does.

Thanks a lot.

I can think of two other options you might use:

1)Massage your source data.
-Edit>GoTo>Special Cells: blanks, click [OK]
-Type an apostrophe then hold down [Ctrl]+[Enter]

OR

2)Add a column of formulas to your source data that will display labels (if
they exist) or empty text ("") if they don't and use that column instead of
the data column
Example: =if(isblank(B1),"",B1)

Does that help?

 

[Guest]

Thanks for explaining that, Boris

(My "real job" prevented me from following up in a timely manner on this
one. )

 

[Guest]

Hi Ron:

Thanks a ton. This worked. I tried Option 1. I donot know though why
typing apostrophe and then holding down ctrl and enter does.

Thanks a lot.

 

[Guest]

Your problem is that the setting in the table options refers to the data
area. If you deselect the dropdown in the comments field so that blank
doesn't show up, it will eliminate the line altogether from the pivot. I
THINK you are trying to avoid, in your row description area (before you get
to showing data like a count or sum total), the printing of the word blank.
In that case. I would suggest you go to your raw data, autofilter the list,
select the "blanks" under the comments column, and then highlight the whole
lot, replacing them with a " " (space). This way, when your pivot draws the
data, it will not be drawing literal blank fields, but rather a seemingly
blank value, which in actuality is a space (which shows up as nothing in the
pivot). Hope that's what you were getting at.

 

[Debra Dalgleish]

You can change the caption:

In the pivot table, select one of the cells that says (Blank)
Type a space character
Press Enter

 

[Guest]

Thanks Ron. I want those items to be displayed. But I just do not want it
to say "blank".

Thanks.

 

[Guest]

Thank you Boris and Ron. Do appreciate it.

 

[Guest]

Hi

I have a VERY LARGE pivot table. As I have to loop through one of the pivot
fields (month of year) to show data by quarters it can take a long time to
complete the query as the pivot table refreshes on every occasion it
hides/unhides a pivot item. Is there any way I can hide/unhide pivotitems
simulataneously without going through a loop.?
Thanks
Rupster

 

[Debra Dalgleish]

I don't know of any way to hide/show pivot all items without looping.
Make sure you have manual update turned on, e.g.:

ActiveSheet.PivotTables(1).ManualUpdate = True