getting rid of (blank) in Pivot Table
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: getting rid of (blank) in Pivot Table

  1. #1
    Board Regular
    Join Date
    Jan 2004
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default getting rid of (blank) in Pivot Table

    In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.

    I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work

    I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.


    any Ideas???

    Thanks

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    I don't understand what you're trying to show? Are you trying to show a 0 instead of an empty cell? Have you tried unclickin (blank) under the Column Labels filter?

  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    1,741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    Drag the field that contains the blank cells into the page area of the pivot, then double click on it and hide items (BLANK) then drag the field back to whereever you want it to be (row area or column area).

  4. #4
    Board Regular
    Join Date
    Jan 2004
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    Every column in my pivot table might contain a (BLANK) or no data. If I tell it to hide them by clicking the blank off it hides the whole row. I want the pivot table to look just like it is only where there is no data rather than showing a (BLANK).....I don' want to see that word in it.

  5. #5
    Board Regular
    Join Date
    Mar 2006
    Posts
    1,741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    One way is to go to your data source, highlight the entire area, CTRL+G, ALT+S, ALT+K, type a space (spacebar), CTRL+ENTER.

    Then you can refresh your pivot table and the (blank) will be blank.

  6. #6
    Board Regular
    Join Date
    Jan 2004
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    Thanks! I always forget about the cntl enter to change all the cells at one time.

    I just wish the Pivot table Options to set empty cells to show them as " " would work. Sounds like that is what it was designed to do!

    Thanks again..

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    FYI,

    I just got the condtional formatting to work
    if cell value is ="(blank)"

    format font color white.


    guess I will nvver know why:
    Pivot table Options, set empty cells to show them as " "

  8. #8
    Board Regular
    Join Date
    May 2013
    Location
    Atlanta
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting rid of (blank) in Pivot Table

    Quote Originally Posted by PATSYS View Post
    One way is to go to your data source, highlight the entire area, CTRL+G, ALT+S, ALT+K, type a space (spacebar), CTRL+ENTER.

    Then you can refresh your pivot table and the (blank) will be blank.
    This is awesome. Only needed it just now, but it worked. Only thing I'd add for those looking at the steps literally, after 'ALT+K' you need to click on the 'OK' button. Then, wait while Excel locates all the blanks in the Highlighted area, then hit the space bar/type a space (spacebar) and do CTRL+ENTER.

    Awesome Awesome Awesome!

    I couldn't use Conditional Formatting because the area of concern within the Table was included in a Concatenation, which would have still included the "white" font word '(blank)' that I was trying to avoid.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com