Note: Excel Survey is now called Forms.
A while back Quiara asked me if there was a way she could collect data from over 1000 different sites that was easier than their current process, which was via emails!
I don’t know about you but the thought of keying data into Excel from over 1000 sites puts me to sleep.
Since Quiara didn’t have the budget or time to develop her own system I suggested she use an Excel Survey (Form) in Microsoft’s OneDrive.
It’s free, easy to set up and best of all Quiara doesn’t need to lift a finger because each of the 1000+ sites enter their own data directly into an Excel Web App, which she can then open in Excel.
All Quiara needs is a Microsoft account, which is free. In fact she might already have one since you can access OneDrive through your Hotmail, Messenger, Windows Phone, Xbox LIVE or Outlook.com account.
Setting Up a Survey (Form)
To create an Excel survey (Form) simply log into OneDrive and click 'New' > ‘Forms Survey’.
This opens the form designer where she can start to set up her questions.
Quiara can change the response type to Date, Type, Yes/No, or multiple choice and even give a default answer.
Before publishing Quiara can see a preview of what others will see and make any changes.
Once it is ready Quiara simply gets a link to the Form which she can then email to the 1000+ sites. They don’t even have to log in to complete the Form.
Results
Quiara then sits back and watches the data roll in, in real time. And since it’s a Web App she can access the data from anywhere.
As you can see below, the response from each question populates a column in an Excel worksheet.
If Quiara wants to start reporting on the data she simply clicks the ‘Open in Excel’ button at the top of the OneDrive window to start working away on her monthly sales report.
Alternatively she can use the built in charting tools in Excel Web App.
Other Uses
Of course you don’t have to use OneDrive for serious stuff. Here are some other ideas:
- Put together an office pop quiz or a game of ‘Would you rather’
- Collate a list of your colleague’s top 5 movies, books, bands…
- Survey the office to find a date that suits the majority for your Holiday Party
Excel Shortcut When Using Dates as Formula Criteria
Bob bacon
Just took your survey regarding “expression” and found this link. I’ve never tried OneDrive before, but just developed and sent my first questionaire. Where have I been for the past three years?!? Great stuff (as always) and thanks again for being so generous with your highly creative examples, tutorials, and online classes. I’m totally impressed with you and your Online Training Hub associates!!!
Bob Bacon – Idaho, U.S.A.
Mynda Treacy
Thanks for your kind words, Bob. Glad I am able to introduce you to new tools.
Enjoy the survey 🙂
Mynda
Maki S. Hussain
When I signed in in my OneDrive account, I got “new” instead of “Create” and when I open it, the list below appeared:
– Folder
-Word Document
-Excel Workbook
-PowerPoint Presentaion
-OneNote notenook
-Plain text document
Excel Survey didn’t appear.
Best Regards
Maki S. Hussain
Mynda Treacy
Hi Maki,
That’s strange. When I click New I get:
– Folder
-Word Document
-Excel Workbook
-PowerPoint Presentaion
-OneNote notenook
-Excel survey
-Plain text document
The other place you can create a survey is from within the Excel Workbook. You need to open it in Excel Online, or click ‘Excel Workbook’ from the ‘New’ list. Then on the Home tab > Survey.
Mynda
Debbie
Hi Mynda,
I have several “subtitle” for 1 question. Is there a way I can put several titles to just one question? Ex: Building secretary, other clerical, central office, para-professional, food service, etc. – all pertain to one question.
Thank you.
Mynda Treacy
Hi Debbie,
No, you can use the subtitle field to put quite a lot of text in though. There is also question type: choice which is a multiple-choice question style.
Mynda
Igor Eisenblatter
From a consulting point of view a very interesting function to do quick temperature or similar tests in an organisation.
Mynda Treacy
Glad you’ll find it useful, Igor.
Jim Branden, PMP
Mynda, thank you for this great tip. I’ll try it soon. By the way, I’m sure you know, SkyDrive now goes by the name OneDrive! Keep in touch,
Mynda Treacy
Thanks, Jim.
I’ve updated the post and replaced SkyDrive in the text.
Cheers,
Mynda
Andrew Marshall
My SkyDrive doesnt appear to have Excel Survey anywhere!? Are there other versions of SkyDrive??
Thanks
Mynda Treacy
Hi Andrew,
You should find it under the ‘Create’ menu (see first image in blog post above), but if not it might be a regional limitation that you don’t have it in the UK, although I’d find it odd that we get it in Australia before you. Might be worth asking Microsoft direct.
Sorry I’m not more help.
Mynda.
Rizwan Khan
Hello,
1. My first and most important Q is that can I edit confirmation message that pops up after press submitting button? I know that google drive supports this but their excel version does not have anything advance like function and formatting.
2. Is their a skip logic in excel survey form ?
Awaiting your answer Thanks.
Mynda Treacy
Hi Rizwan,
Great questions. Unfortunately I haven’t used it enough to know the answers. I suggest you have a play around with it yourself and see if it does the things you want. It doesn’t take much to set up so it should be fairly quick to test it.
Kind regards,
Mynda.
sree
Hi Everybody,
This is very very useful to everyone who are interested in doing Excel.
Mynda Treacy
Thanks, Sree 🙂