+ Reply to Thread
Results 1 to 9 of 9

hide blank cell pivot table

  1. #1
    Registered User
    Join Date
    12-26-2009
    Location
    everywhere but nowhere
    MS-Off Ver
    Excel 2003
    Posts
    15

    hide blank cell pivot table

    hi,i've got this problem, i'm using pivot table and i want to erase the blank cell that come out with pivot table and replace it with number 0 or -, how can i do that ? i've be try to do this by go to the table option and in the empty cells check list i've put 0 or - but the pivot table still come out "blank",
    Attached Files Attached Files
    Last edited by verisme; 02-21-2010 at 09:25 AM. Reason: changing title

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: pivot table

    You haven't explained what you're trying to accomplish, so the attached is simply a guess.
    Attached Files Attached Files
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: pivot table

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    Something like "Hide blanks in PivotTable" would be better
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    12-26-2009
    Location
    everywhere but nowhere
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: hide blank cell pivot table

    no, from the sample that you gave me it would be changing my pivot table report, what i'm trying to do is, i want that "blank" cells that come out from my pivot table automatically change into 0 or empty not "blank" and without changing the source of the data that have empty cells.

    i try to go to pivot tabe option and check the "for empty cells show : 0" but it doesn't work, as shown in the picture.
    Attached Images Attached Images
    Last edited by verisme; 02-20-2010 at 10:34 PM.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: hide blank cell pivot table

    Thanks for taking the time to read and comply with the rule .

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hide blank cell pivot table

    I think Palmetto was simply making the point that if the values in your Row Fields are Numeric you should store them in the Data Field and use a simple Number Format (as per his sample).

    If the source values can not be changed and said values are non-numeric then I guess you could use VBA

    Please Login or Register  to view this content.

    The point is basically that a Pivot Item can not be Null / Blank so in the above the "(blank)" text string is replaced with a space.
    (remember to change the PT specifics in the code to match your requirements - ie sheet name, pivot table name)

    You could equally apply Conditional Formatting etc to set font to white etc etc...

    (I remained convinced there's a more elegant method than the above)
    Last edited by DonkeyOte; 02-21-2010 at 05:12 AM. Reason: rephrased

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: hide blank cell pivot table

    Try this code
    Please Login or Register  to view this content.
    Copy the example
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste
    To run the Excel macro:

    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button

  8. #8
    Registered User
    Join Date
    12-26-2009
    Location
    everywhere but nowhere
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: hide blank cell pivot table

    thanks for all of your answer guys !

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: hide blank cell pivot table

    Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1