pivot tables reports - altering display of (blank) cells
 

I have a spreadsheet that has some cells with no data in them.
Some will never have data, others will as the project progresses.
The pivot table report puts the word (blank) in the report for those cells.

The only solution I have found is to go to each empty cell in the source
data sheet and hit the space bar to force a character into each cell. This
does work but is extremely tedious ( and time consuming). Some other users
(my bosses) of the file add new rows, but are not willing to spend time
fussing around like this.

I tried these two suggested solutions, neither solve the real problem.

1) If I right click the cell and select hide the whole row disappears. I
want the row displayed.

2) In the formatting options menu 'FOR EMPTY CELLS SHOW - _____ " I
checked the box and put various characters in the box. I put the characters
inside quotes, not inside quotes, tried numbers, special characters, just a
blank, any combination I could think of - but the display never changed
from the word (blank) in the pivot table report.

Is there a practical way to change the way these blank cells display in the
Pivot Table.

That "for empty cells, show" is for the empty cells in the data area--not in the
row or column or page.

Debra Dalgleish has an addin that does lots of nice things to pivottables at:
http://contextures.com/xlPivotAddIn.html
instructions:
http://contextures.com/xlPivotAddIn02.html

You can just select that cell with blank and hit the space bar. (One time for
each field.)

===
Ps. I like to use a formula to make those blank cells non-blank.

I use
=""

I hope that it reminds me that I did something on purpose to those cells.
Spacebars are difficult to see (well, for me).

cak wrote:

I have a spreadsheet that has some cells with no data in them.
Some will never have data, others will as the project progresses.
The pivot table report puts the word (blank) in the report for those cells.

The only solution I have found is to go to each empty cell in the source
data sheet and hit the space bar to force a character into each cell. This
does work but is extremely tedious ( and time consuming). Some other users
(my bosses) of the file add new rows, but are not willing to spend time
fussing around like this.

I tried these two suggested solutions, neither solve the real problem.

1) If I right click the cell and select hide the whole row disappears. I
want the row displayed.

2) In the formatting options menu 'FOR EMPTY CELLS SHOW - _____ " I
checked the box and put various characters in the box. I put the characters
inside quotes, not inside quotes, tried numbers, special characters, just a
blank, any combination I could think of - but the display never changed
from the word (blank) in the pivot table report.

Is there a practical way to change the way these blank cells display in the
Pivot Table.

--

Dave Peterson