Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot chart loses data (2003)

    I'm attaching a spreadsheet where I made a pivot table. I don't know where to start.. I have too many questions! If you look at this workbook and pivot table/chart, I am sure you will be aghast, but I'm taking this one question at a time! So my first question is,

    If I select the day of the week button at the bottom of the chart, and pick one day, then I lose my secondary axis points. I can't get them back by picking all the dates again, so I have to close the workbook and reopen it. I realize this is probably a very elementary question, and I apologize. I am really pretty new to pivot tables that go past the basic wizard created ones.

    Any other suggestions when you see this would be soooo appreciated. In the meantime, i will continue trying to answer my other questions myself before I ask them of you.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot chart loses data (2003)

    I think part of the problem is that you "corrupted" the sourcedata for the pitvot table/chart by adding a blank column and also adding subtotals. When the source data is not in a format that works with pivot tables it is not surprising that XL may get confused...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot chart loses data (2003)

    Although Excel lets you specify that series in a pivot chart use a secondary value axis, this will be undone each time the pivot chart is updated - all series will use the primary value axis again. You should only plot data that can be displayed using a single value axis.

    Another problem is that you have added subtotals to the source data of the pivot table/chart. You should remove all subtotals from the Quincy Stats 2007 sheet, and delete column B because it is empty - pivot tables don't like empty columns.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot chart loses data (2003)

    That makes perfect sense. Thanks!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot chart loses data (2003)

    It is also a known issue with Pivot Charts that the formatting will be reset when the chart is refreshed or changed (which may also be related to your problem). The article recommends creating a macro to restore the formatting...

    Steve

Posting Permissions

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