Likert analysis in Microsoft Forms, Excel & Power Query

Likert scales can be hard to analyse particularly if you want to have some filters applied to results. Microsoft Forms allows you to make a Likert question, and although it does some chart analysis (which I walk through at the start), when you export results to Excel it becomes harder to do your own analysis.
I explain how to first create your Likert scales in Microsoft Forms, then how to clean & reshape the data using Excel's Power Query tool and finally how to analyse it in either charts, formulas (medianif and modeif formulas or alternatively writing your own Pivot Table formulas using DAX for MedianIf.
0:00 Introduction
0:38 Microsoft Forms Likert question
1:33 Download to Excel
2:37: Power Query intro
2:56 Unpivot Likert data
4:53 Allocate numerical values in Power Query
6:43 Box & Whisker with slicers
7:32 Likert explained
8:19 Median if formula
9:20 Mode if formula
10:43 Pivot table median if

[@lauracarter8348]

Hi David, great video! You mentioned that you have another video which explains how to analyse data from Microsoft Forms in Excel when there are a continuous stream of new responses. Are you able to send the link to this video? Thanks!

[@learnspreadsheets]

Hey! Glad you like it, I have around 4 videos which describe analysing forms data in excel, this one shows that but if you search in my channel you can find more! ruclips.net/video/UoEwPKLjoeU/видео.html

[@lizzyhouska]

Hi David - This is great information. Do you have any resources that translates this same process from Forms to PBI?

[@learnspreadsheets]

Thanks for the feedback. PBI also uses the Power Query engine so the process is pretty much the same actually

[@Hindsighht]

Hi David. Thanks for the videos, they are proving very helpful with my final year project for university. I have a quick question.
When I import my data from Forms into Excel, the columns containing the answers from my likert questions are not titled with the statement from the Form itself ie: "Access to the countryside is a fundamental human right", instead it just says "Column 1, Column 2, Column 3 etc." Any ideas?

[@learnspreadsheets]

Sorry I can’t say without seeing it more clearly. PS love the south park reference in your profile name 😂

[@sellythegirl8960]

Hey David, thank you for the video :)
I wanted to know if I can do this the same way you did when I have more than one likert question in my forms.
So in my case instead of E-Mails, Chatapp and Teams, I have 5 car manufacturers and instead of percentage I have a scale from 1-5. This structure continues for 10 different likert questions. And if I do it the same way you did the Powerquery changes a bit in structure. I‘m a bit confused and I‘d be glad if you‘d help me out here 😄

[@learnspreadsheets]

The same method is adaptable for your situation for sure, the video should explain how, if it doesn’t help I could offer consulting support under my company, you can email me on [e-mail]

[@MotseHarding]

My likert scale won't allow me to add more than 7 options, Please help.

[@learnspreadsheets]

I assume you mean in Forms, in that case you can use the "Rating" question type" and write in the question what the different values mean