+ Reply to Thread
Results 1 to 8 of 8

Issues with charting pivot table

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Issues with charting pivot table

    I've just started getting into pivot tables and I'm trying to build a graph showing progress of several keywords, as shown in the first screenshot. Basically, each keyword has 3 numerical values, from 3 different weeks in May. I want to build a line chart that shows the progress of each keyword over these 3 weeks.

    This is the pivot table I am working with
    excel1.jpg

    And this is the graph I'm trying to customize and how it currently looks.
    excel2.jpg

    I tried ticking "Values in reverse order" but the keywords at the bottom of the graph go on top instead of the number order being reversed.

    Ultimately, I would like it to look something like this.

    I'd appreciate any suggestions on how to make this happen. Sorry if I haven't been clear enough, English is not my native language.

    Thanks in advance!

    Edit: Turns out the reason why I'm having trouble with the report is that the value "-" in the table is converted into 0s. Not sure how I could format the table considering 1 is the maximum value of a cell and 0 is the exact opposite.
    Last edited by bogdn; 05-29-2013 at 07:59 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Issues with charting pivot table

    Although very nice to look at, pictures are not of much use. Pleas post sample sheet - Thx

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Issues with charting pivot table

    Apologies, here is a sample sheet

    sample sheet.xlsx

  4. #4
    Registered User
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Issues with charting pivot table

    Any suggestions for fixing this? Thanks!

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Issues with charting pivot table

    it looks like your dates are individual fields in the source data and not items within a field so a pivot chart won't work
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Registered User
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Issues with charting pivot table

    Not sure I understand the issue, could you tell me how to fix this?

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Issues with charting pivot table

    I mean that your dates are actually the names of fields and therefore you can't plot them on the axis as a timeline. you would have to rearrange your source data-or perhaps plot the source data directly; can't be sure because you didn't provide it

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Issues with charting pivot table

    Try normalizing your data

    Acct Jan Feb Mar
    Acct01 234 123 567
    Acct02 345 234 678
    Acct03 456 345 789

    and you're having problems doing the stuff to it that you'd like to do.

    That's because your data is NOT NORMALIZED. You might want to check out this EXCELLENT link 'Fundamentals Of Relational Database Design'

    Excel's PivotTable Wizard to the RESCUE!

    1) Start the wizard -- Data/PivotTable & PivotChart Report...
    If you have Excel Version 2007+ use alt+D P to activate the PT Wisard

    2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]

    3) Step 2a of 3 - Select Option Button: I will create the page fields -- [Next]

    4) Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize -- [Add] -- [Next]

    5) Step 3 of 3 - [Finish]

    6) Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]

    7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL

    8) Again, on another sheet -- This is you data normalized, or at least closer to it. You'll need to change headings at least.

    Row Column Value
    Acct01 Jan 234
    Acct01 Feb 123
    Acct01 Mar 567
    Acct02 Jan 345
    Acct02 Feb 234
    Acct02 Mar 678
    Acct03 Jan 456
    Acct03 Feb 345
    Acct03 Mar 789

    Courtesy of tek-tips.com)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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