+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Pivot Table link to Line Graph, forward in time 0's ruining graph

  1. #1
    Registered User
    Join Date
    08-26-2011
    Location
    nyc
    MS-Off Ver
    Excel 2003
    Posts
    3

    Pivot Table link to Line Graph, forward in time 0's ruining graph

    Hey all & thanks for trying,
    2 Issues on one post.

    So I have a traditional pivot table going out by months, in the outter months I do not yet have data. This makes the graph sink like illustrated in those outter months. I tried formatting the data, IFs to return values other than o in the data but the table still treats that as 0. I tried formating the table to treat the 0 as blanks but the chart still treats this as zero. The data is too extensive to create a rule on what to pivot. Is there a chart feature that simply doesn't plot anything with a 0 in a line graph?

    Two. Is there a way in my pivot, without having to reaarange my data, to create a drop down selection for my months as shown while keeping the structure of the pivot.

    Thanks people, I've read like ninety blogs over the day looking for the right direction here.

    LS
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table link to Line Graph, forward in time 0's ruining graph

    It would be more helpful to post an example file rather than a image.

    If the cells acutally contain zero then that is what will be plotted.
    Are you using zero for empty cells in the pivot table?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    08-26-2011
    Location
    nyc
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Pivot Table link to Line Graph, forward in time 0's ruining graph

    Hey Thanks Andy, I was actually eventually able to fix my first issue and in the spirit of saving some other poor soul this hassle I'm explaining it below.

    However, my second issue still remains and I can't think that its not too hard for most experts but I just don't know how to create a drop down list out of my available Values in the Pivot without restructuring the layout of the input data or the table itself ?


    My First Issue answer;
    Create an IF and use NA-not 0 & not blank & not other stuff i tired either-just NA. This will force the cell result to yield #NAME? which then in your pivot table it will show up like that exactly. Then go through the step in Pivot table options For Error values show ...and select it but leave the space blank.
    IF('2011'!CJ31/1000=0,NA,'2011'!CJ31/1000)

+ 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