+ Reply to Thread
Results 1 to 4 of 4

Removing "0.00" entries from Pivot Table

  1. #1
    Registered User
    Join Date
    04-09-2008
    Posts
    2

    Wink Removing "0.00" entries from Pivot Table

    Hi .. Newbie here so please excuse any terminology errors.

    I have a simple excel spreadsheet with a worksheet specifically assigned to track our clients money coming in and out of the bank account. It works like this ;

    08/04/08 Money In Mr D Smith 20,000
    08/04/08 Money Out Mr D Smith 20,000
    etc

    I then have a pivot table running on a second spreadsheet that totals clients balances and the overall aggregated bank balance. This all works perfectly (although a little simplistic) and is accurate.

    Here is the problem : If you take the example above, Mr D Smith's balance is 0.00 and gets reported in the pivot table. We now have a very messy pivot table (with hundreds of 0.00 entries) and need to remove all entries where client balances are 0.00 or return results <-0.01 and >0.01.

    I have tried messing around with the 'null' value settings but I think this refers to empty cells. Assuming 0.00 is considered an amount and not empty?

    Any suggestions (apart from setting it up properly in a database!!!)

    Any help would be magic ..

  2. #2
    Forum Contributor
    Join Date
    02-28-2006
    Posts
    690
    use a helper column named test on the first sheet that says YES if balance is lower than minus 1 cent or greater than one cent.

    on second sheet use pivot table as before but in the top left layout box select test = yes

    it will not pull out the zeroes...

  3. #3
    Registered User
    Join Date
    04-09-2008
    Posts
    2
    Thanks Robert,

    I think the problem is that the first sheet doesn't actually know what the balance is for each client. The pivot table is calculating that.

    The source data contains thousands of rows and they come in a very random order. I find it difficult to understand how to get the individual client balances to total on the first sheet - hence why i started using a pivot table to get the overview.

    Does that make sense?

  4. #4
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848
    You can also have the "balance" field added to the "Page" section of the pivot table (You can have it in both page and data section)

    In the Page field you can "hide" values "0"
    Last edited by Portuga; 04-09-2008 at 10:23 AM.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

+ 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