Forum Discussion

darrenlow's avatar
darrenlow
Copper Contributor
Apr 26, 2020

Filter by question in Pivot Table

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
excel
Excel on Mac
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 26, 2020

    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.

    • darrenlow's avatar
      darrenlow
      Copper Contributor
      Apr 26, 2020

      SergeiBaklan 

       

      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. 

       

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Apr 26, 2020

        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.

         

  • Qaiser_j's avatar
    Qaiser_j
    Brass Contributor
    Jun 14, 2024

    HI darrenlow 

     

    The Likert Scale Chart is considered the most suitable visualization for the given data format. The platform described below supports three distinct data formats, enabling the creation of a Likert Scale Chart in just a few steps. Additionally, it offers a rich set of customization properties, enhancing the flexibility and utility of the chart.

     

    I regularly create these charts with the following tool. Hope you will like it.

     

    You can check here for Microsoft Excel & Office 365.

     

    And if you are comfortable with Google Sheets, you can find it here.

     

    Thanks

     

    Likert Scale Chart for survey data

     

    Few more charts

     

     

Resources