Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cak
 
Posts: n/a
Default 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.

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables & not printing blank rows (revisited) [email protected] Excel Discussion (Misc queries) 1 August 4th 05 07:42 AM
Pivot Tables & not printing blank rows (revisited) [email protected] Excel Worksheet Functions 1 August 4th 05 07:42 AM
Using Pivot Table Function to Summarize David Excel Discussion (Misc queries) 0 July 8th 05 04:46 AM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 10:23 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM


All times are GMT +1. The time now is 01:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"