Results 1 to 2 of 2

Thread: Help! I can't get the x axis fields on my pivot chart to separate out

  1. #1
    Neophyte 2012NASkater2012's Avatar
    Join Date
    Jul 2019
    Posts
    2
    Articles
    0
    Excel Version
    Excel 2016

    Post Help! I can't get the x axis fields on my pivot chart to separate out



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've attached the excel for reference (sheet 1) - I am trying to create a pivot chart based on some survey results, but the x axis fields are not separating out how I want.
    The question I am trying to build a pivot chart for has multiple select answers (so out of the 4 answers, a person could pick choices "a, b, c, d" or "a and b", or just "b". I want answers a, b, c, and d as distinct fields on the x axis, and then each bar would correlate to the "Count" of each of these fields. However, the x axis is instead showing me a|b|c, a|b, a|b|c|d, etc. based on the answers that each person has selected, so now I have over 10 bars. How do I make the x axis separate out to just 4 bars?
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,613
    Articles
    0
    Excel Version
    365
    Note: Your file had a pivot table that wouldn't refresh properly since the source data range had been changed.

    In the attached:
    On sheet2 a chart as you may want it. Produced directly from your data.
    On sheet Data Rearranged, guess what, your data rearranged and a pivot & chart.
    On Sheet6, another pivot and chart based on that re-arranged data.

    The Data Rearranged sheet was put together using the macro blah which creates a new sheet each time it's run.
    To be able to group the questions a little, I added some group names to cells B9:C9,AI9:AJ9 of the Answers - Participant Level sheet.
    Basing your pivot tables on this new data sheet will allow you a lot more flexibility in summarising data in pivot tables and charts.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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