A follow-up to episode 2226 - more cool features with Forms and Surveys in Excel.
First - some analysis of the responses from episode 2226 0:05
Coke or Pepsi 0:55
Time of Day 1:14
Date of responses 1:52
3D Map of responses 2:16
Drawing winner of book 3:06
Further Discussion of Features in Forms 3:50
How to add a logo. 4:03
How to add a theme. 4:15
How to restrict answers to a number between two values. 4:38
How to shuffle answers. 5:15
How to add a Likert Scale question. 5:33
How to add a Net Promoter Score question 6:30
Using the new Quiz feature 6:58
Getting Name and E-mail of respondents 7:10
Using Power Query to change text numbers to numbers 7:35
First - some analysis of the responses from episode 2226 0:05
Coke or Pepsi 0:55
Time of Day 1:14
Date of responses 1:52
3D Map of responses 2:16
Drawing winner of book 3:06
Further Discussion of Features in Forms 3:50
How to add a logo. 4:03
How to add a theme. 4:15
How to restrict answers to a number between two values. 4:38
How to shuffle answers. 5:15
How to add a Likert Scale question. 5:33
How to add a Net Promoter Score question 6:30
Using the new Quiz feature 6:58
Getting Name and E-mail of respondents 7:10
Using Power Query to change text numbers to numbers 7:35
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 2228: Forms, Surveys, and Quiz, Part 2.
If you like what you see in this video, please subscribe and ring that bell.
Well, I've had a chance to use these new forms tools for a couple of weeks since we did episode 2226, so I'm going to give you some great new features that I've discovered since then.
But first, let's look at the results.
Hey, back in Episode 2226 we talked about the brand new Forms button that was added to the Insert tab in Office 365, and how it could generate an online form, and I encouraged people watching the podcast to try out the bar code-- aim their phone at the bar code-- and then enter the survey.
And I said that what I would do here, is we would choose the winner, and the winner would get a copy of the brand new book, Excel 2019 Inside Out, when that comes out later in the Fall.
Alright, so we will have that drawing.
I do want to take a quick look at some of the answers here: Coke, number one answer; Pepsi, number two; water, number three; and then a whole variety of interesting answers, on through a total of a 199 people answer.
The question that's also fascinating here, let's take the Coke or Pepsi out and put the completion time along the left-hand side.
And I always said I never have a chance to roll this up by hour, so let's take this completion time and we'll Group Field, and we'll look at it just by hours-- hour of the day.
What hour of the day is it most popular?
So, kind of, I want to get up to 7 a.m., 8 a.m., 9 a.m, when people in the United States are at work, kind of peaks out there at lunch-- that's great to see people are watching at lunch and, sort of, towards the end of the day.
But all around the world, of course, the workday is different, and that works.
Alright now, let's take that Group Field and we'll change it to Days-- this is interesting.
This will show you the life of a podcast.
So the thing went live on July 26, a lot of answers that day, fewer answers the next day, and then it just kind of tails off after that.
But there's people that keep watching, keep watching, keep watching, all the way up until yesterday.
We're trying this on the 15th of August, so some interesting things we can do there.
I also ask for postal code-- we'll take a look at the 3D map of this-- gives you an idea of where people are watching.
So all the way from Alaska to Miami in the United States, some Canada, Mexico, a couple folks down in Brazil, great.
And then up here in the UK, and Europe, and cruising down Saudi Arabia-- I always see Saudi Arabia show up as one of the languages in the YouTube analytics, so we have someone there in Saudi Arabia, shout-out to them-- and then down here in South Africa, India, and all around the world.
Now, it's weird I didn't get anyone in Australia, which either means I'm not popular in Australia, or, I think, the Australian Postal Codes are being interpreted as US Postal Codes.
Either one-- either one is very, very possible.
Alright, so great to just get a view of the data there.
Alright.
Number of Entries, Count, Completion time.
There's 199 here, =RANDBETWEEN(1, 214) I want to press F9 to calculate this 17 times.
Why?
Because 17 is my favorite number.
And then we'll reveal the winner.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17-- Person # 52.
And we'll change these fonts here-- I have some index functions that are currently in white font, great way to hide data.
Alright, so there we are.
Someone in Uganda, and this is amazing.
I do have your address, but hey, shoot me an email.
I'll put the email address in there on the screen.
Favorite Number, 7; Coke or Pepsi, answer was Juice.
So our winner in Uganda will be getting a copy of the book.
Hey, look, since I did that podcast, I've written a few more articles about these forms and I've learned some new things that I want to share with you that I wasn't smart enough to share the first time.
First off, when you click on the title-- click on the title the first time-- you'll have an opportunity over on the right hand side to upload a picture, alright, and that's cool because it creates a nice little logo.
If you didn't click on the title, you wouldn't see that those-- both the subtitle and the picture-- shows up.
Also, the theme, lots of different colors here.
Now, this really only comes into play if you are taking the survey, not on a phone, but on a computer.
When you take the survey on a computer then this background appears on the left and right side.
And a few more things I've learned about the question.
So let's go in and edit this question.
Down here in the right hand side, under the ellipses, we can say that-- we can set up-- first, a subtitle, and then restrictions.
So restrictions is really cool-- the value must be a number, or I can say that it has to be a number greater than, or even between or something like that, alright?
And, so, you can make some specifications.
We'll talk about something really frustrating right here: Even if we force this to be a number, when the data comes back into Excel, that data is going to be text-- and there doesn't appear to be any workaround for that, which is a little upsetting, you know.
The data is coming to Excel, we really would rather have that be a number so we can create pivot tables from it.
Alright, another cool trick here.
So here we have multiple choice and Coke was the number one answer.
Was Coke the number one answer because it's popular, or because people were just choosing the first item?
Well, yeah, I don't know.
Come down here to the ellipsis, and there's an option to shuffle options, which would mean that some of the people would get Pepsi as the first answer, two other people would get Coke as the first answer.
We could also convert that to a drop-down.
Let's come down here.
And we will add a Question, Choice, Text, Rating, and Date.
There's two other kinds of questions: Ranking and, I don't know if this is Likert, or Likert-- it's named after a guy who created it back in the '50s.
This is pretty cool, so right now we have five options across the top, but check this out: I'm going to say, "How often do you use these features?" And because I use the words "How often", they give me a choice for Daily, Weekly, Monthly, Yearly, and never so very easy to fill those in.
And we're going to ask how often do you use VLOOKUP, how often you use pivot tables.
Add Statement, how often do you use Macros; Add Statement, how often do you use Power Query?
Alright.
So this creates a kind of a more interesting question type.
Another new question type that was just added recently is this Net Promoter Score (registered trademark).
How likely are you recommend us to a friend or colleague?
Not at all likely, extremely likely.
So there's a lot of features in the form that are kind of subtle and you don't see them on your first pass.
The other one here, if we come to Forms, and I want to Create a New Form-- now you can't do this from Excel, but a New Quiz-- a New Quiz.
The difference between a form and a quiz is that each question is allowed to be assigned a certain number of points.
Alright.
Also, when we share, if you share and say, Only people in my organization can respond, then they're forced to sign in and that will allow the responses to have a name and email address.
In my form, it was open to anyone with a link to respond, and so in the data that I got back, the name and email are all anonymous.
But, you see, very frustrating here, that the favorite number is showing up as text.
How do you Power Query?
Data, form Table, in order to convert those to real numbers-- little frustrating we have to do that for now, but hopefully it'll get better.
Alright, well, hey, I want to thank everyone who took our quiz, great little interesting feature there.
We'll see you next time for another netcast from MrExcel.
If you like what you see in this video, please subscribe and ring that bell.
Well, I've had a chance to use these new forms tools for a couple of weeks since we did episode 2226, so I'm going to give you some great new features that I've discovered since then.
But first, let's look at the results.
Hey, back in Episode 2226 we talked about the brand new Forms button that was added to the Insert tab in Office 365, and how it could generate an online form, and I encouraged people watching the podcast to try out the bar code-- aim their phone at the bar code-- and then enter the survey.
And I said that what I would do here, is we would choose the winner, and the winner would get a copy of the brand new book, Excel 2019 Inside Out, when that comes out later in the Fall.
Alright, so we will have that drawing.
I do want to take a quick look at some of the answers here: Coke, number one answer; Pepsi, number two; water, number three; and then a whole variety of interesting answers, on through a total of a 199 people answer.
The question that's also fascinating here, let's take the Coke or Pepsi out and put the completion time along the left-hand side.
And I always said I never have a chance to roll this up by hour, so let's take this completion time and we'll Group Field, and we'll look at it just by hours-- hour of the day.
What hour of the day is it most popular?
So, kind of, I want to get up to 7 a.m., 8 a.m., 9 a.m, when people in the United States are at work, kind of peaks out there at lunch-- that's great to see people are watching at lunch and, sort of, towards the end of the day.
But all around the world, of course, the workday is different, and that works.
Alright now, let's take that Group Field and we'll change it to Days-- this is interesting.
This will show you the life of a podcast.
So the thing went live on July 26, a lot of answers that day, fewer answers the next day, and then it just kind of tails off after that.
But there's people that keep watching, keep watching, keep watching, all the way up until yesterday.
We're trying this on the 15th of August, so some interesting things we can do there.
I also ask for postal code-- we'll take a look at the 3D map of this-- gives you an idea of where people are watching.
So all the way from Alaska to Miami in the United States, some Canada, Mexico, a couple folks down in Brazil, great.
And then up here in the UK, and Europe, and cruising down Saudi Arabia-- I always see Saudi Arabia show up as one of the languages in the YouTube analytics, so we have someone there in Saudi Arabia, shout-out to them-- and then down here in South Africa, India, and all around the world.
Now, it's weird I didn't get anyone in Australia, which either means I'm not popular in Australia, or, I think, the Australian Postal Codes are being interpreted as US Postal Codes.
Either one-- either one is very, very possible.
Alright, so great to just get a view of the data there.
Alright.
Number of Entries, Count, Completion time.
There's 199 here, =RANDBETWEEN(1, 214) I want to press F9 to calculate this 17 times.
Why?
Because 17 is my favorite number.
And then we'll reveal the winner.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17-- Person # 52.
And we'll change these fonts here-- I have some index functions that are currently in white font, great way to hide data.
Alright, so there we are.
Someone in Uganda, and this is amazing.
I do have your address, but hey, shoot me an email.
I'll put the email address in there on the screen.
Favorite Number, 7; Coke or Pepsi, answer was Juice.
So our winner in Uganda will be getting a copy of the book.
Hey, look, since I did that podcast, I've written a few more articles about these forms and I've learned some new things that I want to share with you that I wasn't smart enough to share the first time.
First off, when you click on the title-- click on the title the first time-- you'll have an opportunity over on the right hand side to upload a picture, alright, and that's cool because it creates a nice little logo.
If you didn't click on the title, you wouldn't see that those-- both the subtitle and the picture-- shows up.
Also, the theme, lots of different colors here.
Now, this really only comes into play if you are taking the survey, not on a phone, but on a computer.
When you take the survey on a computer then this background appears on the left and right side.
And a few more things I've learned about the question.
So let's go in and edit this question.
Down here in the right hand side, under the ellipses, we can say that-- we can set up-- first, a subtitle, and then restrictions.
So restrictions is really cool-- the value must be a number, or I can say that it has to be a number greater than, or even between or something like that, alright?
And, so, you can make some specifications.
We'll talk about something really frustrating right here: Even if we force this to be a number, when the data comes back into Excel, that data is going to be text-- and there doesn't appear to be any workaround for that, which is a little upsetting, you know.
The data is coming to Excel, we really would rather have that be a number so we can create pivot tables from it.
Alright, another cool trick here.
So here we have multiple choice and Coke was the number one answer.
Was Coke the number one answer because it's popular, or because people were just choosing the first item?
Well, yeah, I don't know.
Come down here to the ellipsis, and there's an option to shuffle options, which would mean that some of the people would get Pepsi as the first answer, two other people would get Coke as the first answer.
We could also convert that to a drop-down.
Let's come down here.
And we will add a Question, Choice, Text, Rating, and Date.
There's two other kinds of questions: Ranking and, I don't know if this is Likert, or Likert-- it's named after a guy who created it back in the '50s.
This is pretty cool, so right now we have five options across the top, but check this out: I'm going to say, "How often do you use these features?" And because I use the words "How often", they give me a choice for Daily, Weekly, Monthly, Yearly, and never so very easy to fill those in.
And we're going to ask how often do you use VLOOKUP, how often you use pivot tables.
Add Statement, how often do you use Macros; Add Statement, how often do you use Power Query?
Alright.
So this creates a kind of a more interesting question type.
Another new question type that was just added recently is this Net Promoter Score (registered trademark).
How likely are you recommend us to a friend or colleague?
Not at all likely, extremely likely.
So there's a lot of features in the form that are kind of subtle and you don't see them on your first pass.
The other one here, if we come to Forms, and I want to Create a New Form-- now you can't do this from Excel, but a New Quiz-- a New Quiz.
The difference between a form and a quiz is that each question is allowed to be assigned a certain number of points.
Alright.
Also, when we share, if you share and say, Only people in my organization can respond, then they're forced to sign in and that will allow the responses to have a name and email address.
In my form, it was open to anyone with a link to respond, and so in the data that I got back, the name and email are all anonymous.
But, you see, very frustrating here, that the favorite number is showing up as text.
How do you Power Query?
Data, form Table, in order to convert those to real numbers-- little frustrating we have to do that for now, but hopefully it'll get better.
Alright, well, hey, I want to thank everyone who took our quiz, great little interesting feature there.
We'll see you next time for another netcast from MrExcel.
