Export Results to Excel

Quick reference

• 00:04 In a previous lesson, we saw how we can jump into the Responses tab in a quiz, and
• 00:09 take a deeper dive into the details of the responses that we've received.
• 00:14 Now, it might be that this type of analysis that's available within
• 00:18 the Microsoft Forms application is more than enough for you, and if so
• 00:22 that is completely fine.
• 00:24 However, if you want to do a little bit more with this information,
• 00:28 then you could potentially export the details out into an Excel spreadsheet.
• 00:34 And of course, that opens up a whole world of data analysis tools.
• 00:38 So that's exactly what we're going to do in this lesson.
• 00:40 We're going to add export our quiz results into Excel.
• 00:43 And whilst this isn't an Excel training session, for
• 00:46 those of you who are familiar with Excel, we'll just run through a couple of things
• 00:50 that you could potentially do to analyze this data more deeply.
• 00:53 So, from the Responses tab, we have an open in Excel button.
• 00:58 So let's click it.
• 01:00 Now, notice it's now downloading this file, I've got a little downloads message
• 01:04 up here, and I'm going to choose to open the file.
• 01:07 So, this is how the file will open in Excel.
• 01:10 Now, notice that the top here is opened in protected view, and
• 01:13 this is really just a security feature.
• 01:15 Now, I'm confident that Microsoft Forms isn't going to send me any bugs,
• 01:19 so I'm going to click on Enable Editing.
• 01:22 So what's happened here is all of those results have been exported out into
• 01:26 an Excel workbook, and they've also been put into an Excel table.
• 01:30 How do I know my data is in a table?
• 01:32 Well, it looks like it's in a table, plus I have the table
• 01:35 design ribbon at the top here which would indicate that that is true.
• 01:40 Now if I was going to analyze this data, the first thing I would do is I would take
• 01:44 a look at all the information it's exported, and
• 01:46 I would start removing columns that I don't want to appear in my analysis.
• 01:50 So, column A, that's the ID, I don't really want to analyze that,
• 01:54 let's delete out that column.
• 01:56 Start time, completion time, we'll keep those.
• 01:59 Email address, I'm not really going to include that in my analysis, so
• 02:02 that can go, let's right click and delete.
• 02:04 Name, now I do want to keep that.
• 02:06 Again remember, this will be all of the responders' names,
• 02:09 it won't be your name over and over again.
• 02:11 Total points, we'll keep that.
• 02:12 Now some of these columns don't have any information at all,
• 02:16 so I'm going to get rid of those, let's right click and delete.
• 02:20 Now column E contains the answer to the first question, and
• 02:24 column F is telling me how many points each responder achieved for that question.
• 02:29 Column G is showing me the feedback, and
• 02:32 then column H is the second question, so on and so forth.
• 02:36 Now, what could I do with this data once I have it in Excel?
• 02:39 Well, I can filter it.
• 02:40 And again, you might have a lot more results than I do here.
• 02:43 But if I wanted to filter by the respondent name, for example,
• 02:47 I could click the little drop down, it's going to show me all of the respondents.
• 02:51 Again, in this scenario, it's only me, you can check the name, click on OK, and
• 02:55 it's going to show you just the results for that person.
• 02:57 We could sort our list, so maybe I want to sort by completion time newest to oldest.
• 03:03 I could do that.
• 03:04 I could also analyze this data further by maybe putting it into something like
• 03:08 a pivot table.
• 03:09 So if we click in the data,
• 03:11 I'm going to choose Summarize with pivot table from the table design ribbon.
• 03:15 We're going to use this table range as our data source, and
• 03:18 we're going to put it on a new worksheet.
• 03:20 Let's click on OK.
• 03:21 So, if you're not familiar with how pivot tables work, don't worry too much,
• 03:26 this is just really an example to show you the potential.
• 03:28 But we have our blank pivot table report, and then on the right hand side,
• 03:33 we can choose different pivot table fields to analyze our data.
• 03:36 So, maybe I want to create a summary report of all of the students' names, so
• 03:41 I'm going to drag that down into Row.
• 03:43 Remember, there's only one in this case, and
• 03:45 the total number of points that they achieved.
• 03:47 I could do that.
• 03:48 I could then maybe drag the first question down into Columns, and
• 03:52 it's going to show me the responder names, and
• 03:55 a summary of the total number of points for each of those answers for question 1.
• 03:59 Now, I wouldn't say that this is the perfect data to be
• 04:03 putting into a pivot table, but there is potentially so
• 04:06 much that you can do when you export your results out to Excel.

Lesson notes are only available for subscribers.