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

2012NASkater2012

New member
Joined
Jul 18, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
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?
 

Attachments

  • Participant Level Report Out_AR_LP.xlsx
    38.1 KB · Views: 7
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.
 

Attachments

  • ExcelGuru10114 Participant Level Report Out_AR_LP.xlsm
    89.3 KB · Views: 2
Back
Top