How to Collect Data with an Excel Survey & Office 365 Education

How to Use Excel Surveys

Google Apps for Education schools have Google Forms for surveying staff and quizzing students, but you needn’t feel left out if you are using Microsoft’s Office 365. An Excel Survey does very much the same thing, and it’s easy to set up. You can even do it with a free, individual OneDrive account if your school does not use Office 365. Here’s what you need to know.


Note: An updated version of Excel Surveys – Microsoft Forms – is now available for schools who subscribe to Office 365 Education. See my blog post How to Use Microsoft Forms for more information.


Excel surveys are created online. So, you first need to log in to your OneDrive for Business account and click “new”. Select “Excel survey”, then give your document a name and click “OK”.

create excel survey

On the next screen you can enter a title for your survey and a short description or introduction for those that are filling out your survey. You can then click on the box that says to “Enter your first question here”. This opens a pop up box that gives you more options to do just that. So, enter your question, a question subtitle/help text (optional), and choose the type of question you want to ask. There are currently seven types of questions:

  • Text questions – for short one or two word answers
  • Paragraph text – for longer written responses
  • Number – for a numerical answer that you can sort, total or average later
  • Date – for answers you want to sort by date later
  • Time – for answers you want to sort by a specific time
  • Yes/No – for simple yes or no responses
  • Choice – for multiple choice answer

As with any type of survey that you may require, think about how you want to handle the data when it comes back to you, because this can help you choose the type of question that you may want to specify when creating your Excel form.

add a question

Once you have entered all your questions, look for your options in the bottom right-hand corner of the screen. From here you can choose to share your survey with others, or simply save and view what you have done so far to preview it as a viewer. If you choose to share the survey, you will get a URL link that you can use to email or add to a website for others to access.

share or view survey

Responses are collected in an Excel workbook online. You can then filter and sort the data as you need by clicking the drop-down options at the top of each column, or you can opt to download the file and open it in the desktop version of Excel for further analysis.

One exceptionally convenient feature of Excel Surveys is that your survey and the responses are grouped together as one file. When you create a Google Form you get two files, one for the survey and one for the responses. Having them together in the one file helps save a lot of confusion. It also makes it easy to view, edit and share your survey at any time from the toolbar in your online excel workbook, (see image below).

view, edit, survey

So, whether you are surveying your staff, or creating a quick quiz for your students, Excel Surveys in OneDrive for Business are a great option for doing just that.

Advertisements

99 thoughts on “How to Collect Data with an Excel Survey & Office 365 Education

  1. Pingback: How to Collect Data with an Excel Form & Of...

  2. Pingback: How to Collect Data with an Excel Form & Of...

  3. Nancy Tatum

    Great feature! Can students submit more than one response? Wanting to create a reading log and have students submit more than just once.

    Like

    Reply
      1. Sara Gilleran

        With google forms, you can link multiple forms to one spreadsheet. Is it possible to do this with Excel Survey?

        Like

      1. Lowell

        There is a way in HTML coding to force a page to refresh itself. I think it’s a tag in the META section of the page. Problem with this, though is how long between refresh intervals. You don’t know how long someone may take to complete the form.

        Another option is maybe add a Start button to your kiosk screen. this button would do a simple refresh operation at your survey’s web page. If you’re using a browser on a computer, clicking the refresh arrow in the address bar or pressing the F5 key on the keyboard will cause the survey to reopen after one has completed and submitted it. Your kiosk Start button could perform the Refresh operation and reopen the survey.
        Lowell

        Like

  4. Kaf

    We’re asking people to score applications for a program. We’d like to use the Excel survey. Ideally scorers would review an application, score it through the Excel survey and submit it. Is there a way for them to be redirected to the blank survey questions after they submit one application and are moving on to the next one? Like, “Submit and score another application?” Or do they need to completely reopen the survey link?

    Thanks!

    Like

    Reply
    1. jonathanwylie Post author

      That would be a great feature, but I can’t see a way to do that right now. You can do it with a Google Form by adding a URL to the “thanks for your response” box, or by giving users the chance to submit multiple forms, but Microsoft have not included any of those options yet. Perhaps they will come in a future update.

      Like

      Reply
  5. Michelle

    I would like to know if there is an option for a notification for when someone fills out a survey and submits it. With Google forms, there is a notification feature, but I haven’t found one yet for Excel. Do you know of one?

    Like

    Reply
    1. jonathanwylie Post author

      Nothing that I know of, but it is an oft-requested feature. The only thing that might work, and I have to admit I have not tested it recently, is OneDrive for Business. In the tabs at the top you can add notifications for selected files so that you get notified if they change. You might want to try that.

      Like

      Reply
  6. Pingback: 10 Alternatives to InfuseLearning for Classroom Assessments | Jonathan Wylie: Instructional Technology Consultant

  7. rustyvp

    Do you know, is it possible to edit the confirmation page? I would like to customize what people see once they have submitted the form.

    Like

    Reply
  8. Jay

    Hi Jonathan,

    Thanks for this post – i have been using excel surveys online too – but one limitation I discovered is that you can only have one survey per workbook. Is there a way to link more than one survey to a workbook so the data from multiple surveys populates different sheets within the same workbook?

    Thanks!

    Like

    Reply
      1. Jay

        Thanks Jonathan – that is exactly what I did – just a cumbersome work around because the data only dumps into the individual workbooks when they are online…but you can’t link them online so have to open them locally first. Hopefully Microsoft takes a nod from google and makes it more functional like google forms and sheets are!

        Like

  9. Samantha Hull

    Is there a way to monitor who answers the survey or to limit it to being filled out once? For example, if a class of students is voting on a particular book title how can we limit them voting 253 times.

    Like

    Reply
    1. jonathanwylie Post author

      I can’t think of a good way to limit responses, but in OneDrive for Business you may be able to set up notifications as to when the survey has been filled in. Try selecting the survey, then clicking Files > Alert Me > Set Alert on This Document. There are options here for letting you know about changes to the document. This might work to give you notifications when someone fills in the form.

      Like

      Reply
  10. Stacee

    Johnathan – ? for you, is there a way to capture the log on ID and time / date stamp for when people are leaving responses to your survey questions? This was a feature that worked in Google, but I’m not able to find similar functionality within Excel Survey. Can you please assist? Thanks!

    Like

    Reply
    1. jonathanwylie Post author

      I don’t believe that there is an automatic way to do that right now. The closest you could get I guess would be to have a question that required the user to enter their email address and/or the date/time. They could put whatever they wanted in the box, but it would be one way of collecting that data. Hopefully that comes in a future update. If you find another workaround, please let me know! 🙂

      Like

      Reply
      1. Lowell

        I used the following formula to post the date/time the survey was completed: =IF(OR(A2=””,A2=0),””,IF(H2=””,NOW(),H2))
        Column A of my survey holds the answer to the first survey question. The OR part of the formula checks cell A2 for an entry. If there is no entry, then the TRUE portion of the IF statement posts a null value (“”) in the cell. If there is an entry in column A, then the second IF function checks to see if a time/date stamp already exists in cell H2 (Column H holds the date/times tamp of the entry. If H2 is empty (“”), the NOW function posts the date/time. If there’s already an entry in H2, then cell H2 equals itself.

        Yes, this is a circular reference in Column H cells and you’ll get a warning about this each time you open your survey in Excel if you don’t go to Options>Formulas and enable iterative calculations in the Workbook. I set the number of calculations to 10, but you could probably set it to 1. I left the default maximum change value at .001.

        Like

  11. Pingback: How to Set Document Alerts in OneDrive for Business | Jonathan Wylie: Instructional Technology Consultant

  12. Amanda

    Are there limitations to how many users can access and submit the survey at one time? If you have (10) people filling out the form and they all click on Submit around the same time, can Excel Online handle all requests and successfully record each of the 10 submissions?

    Like

    Reply
  13. Clint

    I suppose either the majority of their users are in the US, or they just don’t care enough about the endless requests from the rest of the world – the form only accepts US date format. Even though our site settings are set to Australia, +10 GMT, English (Australia). I am seeing lots of requests for this to be fixed, but no official responses.

    Like

    Reply
  14. Neil

    Glad to see something coming along in O365 to give K-12 what they have with Google Forms.

    A few questions:
    Is there a way to keep public or anonymous users from changing data that’s been submitted?
    Can we (or will we be able to) add logos or images to the form?
    Any good training or reference materials that we can offer to educators to help them?

    Like

    Reply
    1. jonathanwylie Post author

      Hi Neil,

      Only those with access to the spreadsheet can edit data after it has been submitted. If you create the Excel Form, and share it with no-one, then only you can see the data. The link to the form is public, but the link to the spreadsheet is private.

      You can’t add images or logos to the form right now. Will you be able to in the future? I hope so, but have no way of knowing that right now.

      Training materials for educators in O365 are a little scarce. You could check out this site that I started to put together: http://www.365education.weebly.com. It is in need of an update for the latest UI in O365, but if there is anything there you can use you are welcome to use it.

      Keep in touch,

      Jonathan

      Like

      Reply
  15. Ákos

    Hi Jonathan!
    Thank you for your article! It’s very helpful, however I have a question: is there a way to use Excel survey offline? It wouldn’t be a problem to design the questionnaire online but the answers weren’t from internet users and the data are sensitive so I would prefer to store it (and also collect the data) on my pc.
    Could you help me please?
    Thanks,
    Ákos

    Like

    Reply
    1. jonathanwylie Post author

      You need to design and collect the data online. Right now there is no other way. After collecting the data, you can download it and store it offline as an Excel spreadsheet and then delete the online copy if you wanted to, but that is about all you can do I am afraid.

      Like

      Reply
      1. Ákos

        Thank you for the answer! That is what I was afraid of… So I’ll search for other soulution.

        Like

      2. jonathanwylie Post author

        Good luck with the search. I am not aware of any offline data collection tools that might work, but there could be some out there somewhere. The only scenario I can think of would be if you were collecting information on a website that was hosted on your own web server.

        The data you collect in the Excel form is private to you. No-one else can see it unless you share it with them. It is online, but it about as secure as anything else you would protect with a username and password.

        Like

      3. Ákos

        Thank you! And you’re right about the security thing but I have to find other solution anyway…
        The most close is that Excel has a form function itself but to use it I have to write macro and I am not as good in that, however maybe this will be the only solution.
        If I would host a website on an own web server, then what would be the software for the survey? I guess then I had to write the questionnaire in html or something like that, right?

        Like

  16. Kyle Whiteley

    Hi, Jonathan,
    Thanks for the post. I’m interested in using an Excel survey to get students to sign off when they received their 1:1 laptop in the fall. I am curious if there is a way to pre-populate responses from a data source. For example, all students names are in a spreadsheet. When students access the survey, they can only enter a name that is in the list. Or let’s say they enter Grade 9 and it only allows them to choose a name from the list of students that are in grade 9. Or is there a better option I haven’t thought of? Thanks for your time.

    Like

    Reply
    1. jonathanwylie Post author

      So, you could do that with Excel Surveys if you used the Choice question option, but you would have to manually enter every student name as an “answer” to the question. That is obviously not ideal, but I guess it would be one option.

      In Google Forms you can enact the “go to page based on answer” option. So, if the question was “What Grade are you in?” and they answered by selecting 9th Grade, they would go to page 2 which only has the names of 9th graders on it. 10th graders would go to page 3, and so forth. You could split it further and have one form for 9th graders and have them select the first initial of their last name, A-F, G-L, M-Z and have only the students with certain surnames on a given page. You might be able to take it one step further and use the Google Form add-on called Form Limiter to set limits on how many times a student can pick their name (i.e. limit it to one) and have that name removed after it has been chosen, but you would have to check on that one. I have not used Form Limiter recently.

      In short, Google Forms are more flexible that Excel Surveys right now. Other options might include signupgenius.com.

      Like

      Reply
  17. Susan

    Do you know if there is a way to add an “other” option? Or a comments box? For example I want to do a yes/no survey but I want the users to be able to submit comments if they need to. Thanks!

    Like

    Reply
    1. jonathanwylie Post author

      That might need to be a separate question that is set up as a paragraph text. Something along the lines of… “If you answered Other above, please give more details here.” You would just have to be sure not to make the additional question a required question.

      Like

      Reply
    1. jonathanwylie Post author

      You can set up notifications so you get an email every time something is changed on the spreadsheet. Instructions on how to do that are further up this comment stream…but it only work with Office 365 for Education or Business.

      Like

      Reply
  18. Patty Fischer

    Jonathan,

    You seem so knowledgeable about this! I am really struggling with something silly, but I hope you can help as it is not intuitive. After I copied the URL for the Excel Survey I closed out, and forgot to immediately paste it somewhere for safe keeping. So, basically I got distracted and lost the link. I have looked all over Excel, and cannot find it. I see in your illustration above where there is a survey command where you can view, edit, etc. the survey, but I cannot find it. I am running Office 365 on my computer and it automatically updates Office versions. I am either on 2013 or 2015. Thanks, Jonathan for any help you can provide.

    Like

    Reply
    1. Patty Fischer

      OH, I do feel silly now. I found the command in the online version of Excel — it is not in the desktop version. 🙂 Patty

      Like

      Reply
    2. jonathanwylie Post author

      Patty, I believe the problem you are having is that Excel Surveys can only be accessed online through Office.com. If you open the survey in the desktop version of Excel, you will not see the button you need to retrieve the link. So, try opening a web browser, logging on at office.com and opening your document that way.

      Like

      Reply
  19. incendy76

    Is there any way to get the logged in user that submitted the Form? Or do you have a recommendation for doing Student Assessments using Office 365 that does allow that?

    Like

    Reply
  20. Erica

    Hi Jonathan,

    Can you customize the form to have radio/option buttons? I want people to be able to select more than one choice for one question.

    Thanks!
    Erica

    Like

    Reply
    1. jonathanwylie Post author

      Unfortunately you cannot do this. However, I did read a workaround on eHow that suggested adding a second question, with the same choices. That would allow you to ask students to choose an additional answer that would also be correct. Not ideal by any means, but it would be one way to do it.

      Like

      Reply
      1. Erica

        That’s a great workaround; I’ve added a second Q to my volunteer form. Thanks so much for your quick reply, Jonathan!

        Like

  21. Jeff

    I’d like to use Excel surveys to administer multiple choice quizzes to my students. Is there a direct way to enter the correct answer for the questions and have the spreadsheet score the quiz?

    Like

    Reply
    1. jonathanwylie Post author

      Jeff, I am not aware of any option in Excel online to achieve that kind of task. The closest I can think of would be to open the spreadsheet with the desktop app and apply some conditional formatting rules to highlight correct and incorrect answers. Not ideal, but it is an option. Otherwise, you could look at some formative assessment tools like Socrative, Formative, Quizziz, and so on. These tools will auto-grade for you and let you export results as a CSV file if you need them.

      Like

      Reply
  22. Malcolm

    Also, there is not a way to have a multiple choice answer either. While it says choice… you can only select one answer from a drop down.

    Like

    Reply
  23. rich

    Good article Jonathan. As someone familiar with GAFE, that currently works with Office 365, I don’t feel that Excel Surveys are very good in comparison to Forms – especially in the context of a formative assessment. As you stated Forms are much more flexible, and overall more helpful for instruction.

    Hopefully Microsoft will take a look Excel Survey and find ways to increase it’s functionality for education. I feel that the currently version is not very helpful for teachers.

    If anyone with O365 is looking for an alternative for assessments, I’d suggest Formative at https://goformative.com/

    Like

    Reply
  24. tristand

    Is there a way to find the route excel sheet from a survey? I’ve had some people fill out the form survey but now can’t find where the data has gone.

    Like

    Reply
      1. Tom

        I have several users who can’t find where the data saves. I keep seeing the MS people replying “its in your onedrive” but it isn’t there or is obscurely hidden.

        The application doesn’t allow you to specify a destination location. And the phrase “in your onedrive” is very vague considering Microsoft encourages people to save everything in their onedrive. Its like if my wife loses her keys and I reply “they are in your house”. That doesn’t tell her anything.

        Seems like a huge design flaw or communication flaw from the Excel Survey team.

        Like

      2. jonathanwylie Post author

        Hi Tom,

        Not sure what to say here. The file is saved to your OneDrive and should be there alongside all your other files. It is not hidden.

        Perhaps a good tip for the future would be to create a folder in your OneDrive called Surveys. Open that folder, and then create the Survey. That way, the file will be saved to this folder and you will always be able to retrieve it from the Surveys folder.

        This would be akin to your wife only losing her keys in one room of the house. It should make it easier to find them if you know which room they are in 🙂

        Like

      3. lowell@gsrlc.org

        As Jonathan says, the file is in your OneDrive account and is not hidden. You can put the survey file anywhere you wish within OneDrive.

        Some things to check:

        1. Do your users have access to the folder where the file is saved? If you’re sharing a link and that link doesn’t provide access to the survey’s folder or the file itself, then users won’t find the survey file.

        2. If your file is not in a folder (it’s in the OneDrive root location) and your users are checking a OneDrive folder on their PC (instead of logging in online or using a link), then have them do the following:

        a. Right-click the OneDrive icon in the Notification area and then click Settings.

        b. On the Account tab, click the Choose folders button. Make sure the “Files not in a folder” checkbox is checked.

        3. Does your survey file contain multiple worksheets? If so, then could users not be looking at/finding the worksheet where the survey results are stored?

        Lowell

        Like

  25. Michael Kaiser

    I created my first survey(it was so easy) and sent it to my colleagues. Some of them got this error message “Problem viewing survey. We’re sorry we can’t open this survey …” my best guess is they are using an earlier version of Office e.g., 2010 and incompatible browser e.g. IE 8 or 9. What do you think? This is my first visit to you blog. I’m very impressed with it’s depth and stylish layout. I’m embarrassed to say I have a “website” when I see this. I plan to come back and explore.

    Like

    Reply
    1. jonathanwylie Post author

      Hi Michael, thanks for the kind words, and apologies for the late reply.

      The version of Office that they are running will not have an impact here, but the browser could, especially if some could open it and others could not. Ask them to try a different browser and see if they get the same result.

      Like

      Reply
      1. Michael Kaiser

        Thanks for the reply. In the end I offered my coworkers a choice of Google or Microsoft. 90% of the group took the survey in Google forms. The additional few that chose Excel didn’t report any problems. I work in a school with one part time, very overwhelmed IT person. Once a computer is deployed it never gets touched again so we have computers with a wide range of Office versions and IE versions. I keep hoping for the day that we all have Office 365.

        Like

  26. Sasha

    Hi Jonathan, Thank you for the blog.

    Is there a way to send an email notification to the survey taker confirming they have successfully completed the survey?

    Like

    Reply
  27. max

    Hi Jonathan, is it possible to create a question in the survey where the person who is filling in the form is asked to upload a photo? I’m trying to create some kind of warranty form for our organization and want to collect the photo’s from the defective parts for our technical staff. Maybe they can decide if it’s under warranty or not based on the photo and our client doesn’t have to send the goods to us. thank you in advance for your reply!

    Like

    Reply
    1. jonathanwylie Post author

      Unfortunately not. I think the best you could do would be to submit a link to a URL that links to the image in an online gallery or cloud account. Google Forms won’t help you this way either. I think you would need some kind of custom form builder like JotForm or something along those lines. I believe there are some WordPress plugins that offer this functionality too if you happen to be using WordPress.

      Like

      Reply
    1. jonathanwylie Post author

      Hi Ritvik,
      I am not aware of any way to do this as of now, but I am hearing that a new version of Excel Surveys is being planned by Microsoft (to be called Office Forms) so maybe there is hope for the new version.

      Like

      Reply
      1. Lowell

        This morning, I had a message in my OneDrive email account telling me someone had accessed my survey. It even provided the name of the person. I’m not sure how it got the person’s name, but the first field in my survey is a field for the person’s name.

        The email message appears to be a daily digest of activity. Someone else completed the survey today, so I will check for another digest message tomorrow and let you know if it arrives.

        Like

      2. lowell@gsrlc.org

        The notifications appear to be sporadic. On the day I completed the survey, I got no notification. However, someone else completed the survey yesterday and I got a notification, minus the person’s name, today. I still do not understand how that one earlier notification included the survey taker’s name. I think I’ll ask him. Perhaps he has a OneDrive account and perhaps he was logged in to it when he completed the survey?

        Lowell

        Like

  28. Pingback: Secure Data 365 | injurylawyerhoustontx.xyz

  29. Crystal

    Hi Jonathan,

    I created my survey and shared the link, but I’m not receiving the responses. Another person also reported getting to the end of the survey and seeing an error message saying:

    “This survey can’t accept submissions.”

    Have you seen this before? What’s gone wrong?

    Like

    Reply
  30. Gena Richardson

    Is it possible to select more than one option per survey question? For example when they survey taker is asked to “check all that apply”.

    Like

    Reply
  31. Kerensa Estes

    Any way to export the data from the survey individually (like to a PDF as a completed form), rather than just the Excel spreadsheet? I dont see that functionality in the Forms preview in my version of 365, so trying to do both ways.

    Like

    Reply
      1. lowell@gsrlc.org

        I have Office 365 and Excel 2016. I can get you visually close to what you want, but not exactly where you want to be from a technical perspective. The capability you really want doesn’t seem to be available.

        1. I opened my survey results in Excel on my computer – not in the online version.

        2. I went to the Quick Access Toolbar (QAT) , clicked its dropdown button, and chose More Commands

        3. I then selected All commands at the top of the listing and added the Form button to my QAT.

        4. Back on the survey results sheet, I clicked the Form button and my records opened in a navigable form. At this point, you can do screen image captures and paste the images into a PDF program or into Word and then export as PDF document.

        5. You will see form navigation buttons when the form opens, so you’ll probably wish to exclude those from your screen captures.

        In earlier versions of Excel, I think the Form option was easier to get to – perhaps it was on the Edit or Tools menu in 2003 and earlier. I could not find it on any of the Excel 2016 Ribbon Tabs and cannot remember if it’s on the Excel 2007/2010/2013 Ribbon.

        Lowell

        Like

      2. Kerensa Estes

        Thanks – that’s a possible solution. I am creating a referral form, hard copy of which needs to be kept in a file, but I want to be able to do some data analytics on some fields to get school wide trends, etc., and would prefer to have teachers complete it electronically and have it fed it directly into the database. I just wish there was a way I could upload my existing form, have it convert to an online version, but still be printable like my existing form. Thanks!

        Like

      3. lowell@gsrlc.org

        If you’re paying for website space, then you may wish to check the website provider’s options. Often, providers have form add-ons that you can use to capture data. They’ll store the survey results in a database that you can then access and download the results into Excel or Access. Once you download the data into Excel or Access, you can use their built-in form capabilities to get the paper copy you need to store in a file. These add-ons sometimes have the ability to notify you when someone has completed the form and most also have the option to send you the form results in an email message. It would be a little time consuming to get the form info copied/pasted into Excel, though.

        Excel surveys are much easier to set up, but this other option isn’t usually too difficult if you have some experience in setting up websites. I wouldn’t advise paying for your own website space just to get that capability, but if you already have website space from a provider, might be worth checking.

        Lowell

        Like

  32. Pingback: How to Use Microsoft Forms in Office 365 Education | Jonathan Wylie: Instructional Technology Consultant

  33. Jolene

    Hi
    Wondering if there is a way to copy an active survey so you can use the same questions over for a second set of respondents. Really don’t want to have to re-create all the questions…

    Thanks,
    Jolene

    Like

    Reply
    1. jonathanwylie Post author

      Yes. If you right-click on a file in OneDrive you have the option to Copy To another location. Choose a folder you want to copy the file to and the survey will be created as a copy in that location. Note that surveys cannot be duplicated in the same directory. You HAVE to copy to a separate folder in OneDrive or create a new folder to copy it to.

      Like

      Reply
  34. Zahid

    I have completed a survey of mobile technology as questionnaire now I need to prepare a report so how I can analys using an excel sheet to give me the report

    Like

    Reply
      1. lowell@gsrlc.org

        OneDrive used to offer a short URL, but they stopped doing this. Microsoft said it has something to do with security – brute force attacks can actually find the shortened links because they are so short. Once hacked, it is apparently possible to find the root URL of the OneDrive account. I’ve also read that the shortened OneDrive links are actually by bit.ly, but bit.ly may have used a standard format for the OneDrive links.

        Lowell

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s