Filter by question in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1338552%22%20slang%3D%22en-US%22%3EFilter%20by%20question%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338552%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20building%20out%20pivot%20tables%20for%20a%20survey%20which%20is%20essentially%20a%20series%20of%20Likert%20based%20questions.%20So%20all%2020%20questions%20in%20this%20survey%20have%20responses%20from%20Strongly%20Disagree%20to%20Strongly%20Agree.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%20i'm%20aiming%20for%20the%20below%2C%20where%20you%20can%20toggle%20the%20question%20filter%20to%20another%20question%20and%20it%20will%20populate%20the%20below%20without%20having%20to%20build%20separate%20pivot%20tables%20for%20every%20question.%20Any%20help%20would%20be%20amazing%20as%20I've%20been%20stuck%20searching%20Google%20for%20the%20past%20day!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100.00000000000001%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EQuestion%20Filter%3A%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EQuestion%201%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%228.333333333333334%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EStrongly%20Disagree%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EDisagree%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3ENeutral%3C%2FTD%3E%3CTD%20width%3D%228.333333333333334%25%22%3EAgree%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3EStrongly%20Agree%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3ECustomer%20Segment%20A%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%228.333333333333334%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3ECustomer%20Segment%20B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%228.333333333333334%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%224.166666666666667%25%22%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1338552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338618%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20by%20question%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637359%22%20target%3D%22_blank%22%3E%40darrenlow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20what%20prevents%20you%20to%20add%20Questions%20field%20to%20filter%20area%20of%20PivotTable%3F%20I%20assume%20all%20your%20source%20data%20is%20in%20one%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338683%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20by%20question%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20response.%20The%20pivot%20table%20doesn't%20seem%20to%20be%20allowing%20me%20to%20add%20the%20questions%20into%20the%20filter%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEvery%20time%20I%20add%20one%20of%20the%20questions%20in%20the%20Field%20Name%20box%20to%20Column%2C%20it%20doesn't%20allow%20you%20to%20put%20it%20into%20Filters.%20When%20you%20add%20other%20questions%20into%20the%20Column%20box%2C%20it%20ends%20up%20creating%20a%20table%20within%20a%20table%20per%20the%20second%20screen%20shot.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20amazing.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1338783%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20by%20question%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1338783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637359%22%20target%3D%22_blank%22%3E%40darrenlow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20data%20is%20not%20suitable%20for%20a%20pivot%20table.%3C%2FP%3E%3CP%3EYour%20source%20data%20is%20in%20a%20crosstabular%20format.%20It%20has%20to%20be%20changed%20into%20a%20data%20set%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm building out pivot tables for a survey which is essentially a series of Likert based questions. So all 20 questions in this survey have responses from Strongly Disagree to Strongly Agree. 

 

Essentially i'm aiming for the below, where you can toggle the question filter to another question and it will populate the below without having to build separate pivot tables for every question. Any help would be amazing as I've been stuck searching Google for the past day! 

 

Question Filter:Question 1      
  Strongly DisagreeDisagreeNeutralAgreeStrongly AgreeTotal
 Customer Segment A1234515
 Customer Segment B5432115
 Total6666630
3 Replies
Highlighted

@darrenlow 

Could you please clarify what prevents you to add Questions field to filter area of PivotTable? I assume all your source data is in one range.

Highlighted

@Sergei Baklan 

 

Hi Sergei,

 

Thanks for the quick response. The pivot table doesn't seem to be allowing me to add the questions into the filter column. 

 

Every time I add one of the questions in the Field Name box to Column, it doesn't allow you to put it into Filters. When you add other questions into the Column box, it ends up creating a table within a table per the second screen shot. 

 

Any help would be amazing. 

 

Highlighted

@darrenlow 

Your data is not suitable for a pivot table.

Your source data is in a crosstabular format. It has to be changed into a data set list.