Results 1 to 6 of 6

Thread: is there a way to make a pivot table show only negative values *in all fields* ?

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default is there a way to make a pivot table show only negative values *in all fields* ?

    I have a pivot table where I'm trying to find all the losses. So I'm seeing all the values, where 80% are positive, but I only care about the negative ones. Is there a way to show only negative values? Please note, this isn't just in the grand total column - I want this in the entire grid.

    For example

    Province 5km 10km 15km 20km
    Alberta $500 -$30 $200 $100
    BC -$20 $1000 $1000 $1000
    Ontario $400 $400 -$200 $0


    That's a lot of #s to go through. I only care about the negative ones, which is 3 out of 12. Is there a way to just show those?
    Last edited by bigdan; Sep 6th, 2019 at 08:12 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: is there a way to make a pivot table show only negative values *in all fields* ?

    This may be an option.

    - Select the cells inside the pivot table (all of values).
    - On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list (General). Or select Format, Format cells.
    - In the Format Cells dialog box, under Category, click Custom.
    - In the Type box type:
    ;-0;
    - Press ok.

    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: is there a way to make a pivot table show only negative values *in all fields* ?

    Thanks!

    With this solution I'm guessing this shows only negative values and the positive values show up as empty. What I actually wanted (and I apologize, I wasn't clear) would be for only the relevant rows to be shown. So in the example above I'd like the BC row to not be shown at all since nothing in there is negative.

    Is there a way to do that?

    EDIT - sorry, I see that BC actually does have a negative value. Let's imagine it doesn't. In that case I'd want that row to be hidden / filtered out.
    Last edited by bigdan; Sep 9th, 2019 at 01:41 AM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: is there a way to make a pivot table show only negative values *in all fields* ?

    an idea: in an auxiliary column, put the formula if the min (of all the columns in that row) is> = 0, then 1, otherwise 0. add the auxiliary column to the pt and filter the 1.
    Last edited by DanteAmor; Sep 15th, 2019 at 05:35 PM.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: is there a way to make a pivot table show only negative values *in all fields* ?

    Great idea, thanks!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: is there a way to make a pivot table show only negative values *in all fields* ?

    Youre welcome!
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •