Published by


Kari's Avatar
Finnish but not finished


Location: A Finnish ex-pat in Germany
Posts: 9,047

Show Printable Version 


information   Information
Office Online is a collection of free web based applications available to all Microsoft email account users (outlook.com, live.com, hotmail.com, msn.com). It offers basic versions of Microsoft's Office suite of desktop applications. Signing in to any Microsoft online service like outlook.com or onedrive.live.com you'll find the web app selection on top left:

Name:  image.png
Views: 148
Size:  27.5 KB

All documents you create with Office Online will be saved to OneDrive and can be accessed and worked with from any networked PC or mobile device. Saving to a local computer is not possible but files can be made locally available with OneDrive selective sync (see tutorial). You can download a copy of the survey to local PC but that will not be refreshed when new answers to survey will be submitted.

Although surveys are created in Office Online, when done and published you can open them with desktop version of Excel if you so prefer.

An Office Online survey is totally safe, you as a creator can only get the information people taking your survey will give and submit. No IP addresses, names, locations and such are submitted if you don't specifically ask them in survey.

This tutorial is about online surveys and polls, how to create them using Office Online, OneDrive and Excel Online. More advanced Excel functions and formulas are not covered but feel free to post all your questions in this thread.


The survey made in this tutorial looks like this when embedded to your website or blog:



Of course you don't have to post the results; you can for instance simply email the link to survey to your family and friends in front of your birthday party to ask if they come, alone or avec, do they need accommodation, do they want meat / fish / vegan-gluten free-lactose free-save the whales meal, anything.



 Contents:

Part One: Create a survey / poll
Part Two: Customize output table
Part Three: Share and publish a survey / poll
Part Four: Add survey / poll to your website or blog




OneDrive - Create an online Excel survey with free Office Online Part One OneDrive - Create an online Excel survey with free Office Online
 Create a survey / poll
1.1) Sign in to https://onedrive.live.com, select New > Excel Survey:
Name:  image.png
Views: 141
Size:  31.6 KB

1.2) Excel Online opens, showing Survey Wizard. To start creating your survey enter a title (#1 in screenshot), optional description (#2), first question (#3). When done add more questions (#4):
Name:  image.png
Views: 143
Size:  33.8 KB

1.3) In next screenshot I have already added a title, description and a few questions. Adding a new question I enter the question itself (#1), optional subtitle (#2), type of answer (response) (#3), decide if answering this question is required or not, and in case of some response types set a default answer (#5), for instance if asking if people are coming to my party I would set default answer to No so only those really coming would change it to Yes:
Click image for larger version. 

Name:	image.png 
Views:	13 
Size:	96.5 KB 
ID:	107599

1.4) Available response types:
Name:  image.png
Views: 144
Size:  12.7 KB
- Text = any text, all text in one paragraph
- Paragraph text = Answer can be given dividing it to paragraphs
- Number = only numeric answers will be accepted
- Date = only correctly formatted dates will be accepted
- Time = only correctly formatted time will be accepted
- Yes/No = dropdown selection with Yes and No shown, one must be selected
- Choice = a list of user specified choices will be shown as dropdown selection

1.5) An example of Default Value for response:
Name:  image.png
Views: 144
Size:  11.6 KB

1.6) When done, click / tap Save and View at bottom left of the wizard:
Name:  image.png
Views: 142
Size:  7.1 KB

1.7) You can now review the survey and test answering the questions:
Click image for larger version. 

Name:	image.png 
Views:	8 
Size:	53.4 KB 
ID:	107603

1.8) Click Submit to submit your test answers:
Click image for larger version. 

Name:	image.png 
Views:	9 
Size:	15.0 KB 
ID:	107604

1.9) You will see that your answers were submitted:
Name:  image.png
Views: 141
Size:  6.7 KB

1.10) Excel Online will show each question in its own column in first row. Each subsequent row contains answers from one person. If you want to delete someone from survey, delete that specific row:
Click image for larger version. 

Name:	image.png 
Views:	18 
Size:	77.9 KB 
ID:	107606

1.11) By default the survey is named Survey and placed on root of your OneDrive. Click / tap File at top left corner of Excel Online ribbon and select Save As > Rename to rename your survey as you wish:
Click image for larger version. 

Name:	image.png 
Views:	11 
Size:	46.3 KB 
ID:	107607

Name:  image.png
Views: 140
Size:  5.6 KB

1.12) To save survey on another location select Save As:
Click image for larger version. 

Name:	image.png 
Views:	10 
Size:	44.3 KB 
ID:	107609





OneDrive - Create an online Excel survey with free Office Online Part Two OneDrive - Create an online Excel survey with free Office Online
 Customize output table

2.1)
The survey sheet must be left as it is, do not modify or edit it. Instead click / tap the + sign in status bar to create a new sheet for customised output. Rename your survey and output sheets simply by right clicking them and selecting Rename.

In this example I named the survey sheet as Answers and output sheet as Table:
Name:  image.png
Views: 152
Size:  9.7 KB

2.2) This not being an Excel tutorial I will only show an example of what can be done. In below screenshot I have customised the table I will later embed in a post, the blue outline showing the area that will be shown in my post (cell range A2 to E27):
Click image for larger version. 

Name:	image.png 
Views:	15 
Size:	179.0 KB 
ID:	107611

2.3) #1 in screenshot in step 2.2 = Cell E21 counts and shows how many of those answering the survey are using Windows 10 Insider builds.

To do that I use COUNTIF function in a formula, which checks the Answers (survey) sheet column D from row 2 to row 1001 (if this poll will be popular I can always change it to 2000, 5000, whatever ), column D being the one where answers about Windows version are stored, cell D1 being the question and answers starting from D2:
Click image for larger version. 

Name:	image.png 
Views:	11 
Size:	18.9 KB 
ID:	107631

2.4) #2 in screenshot in step 2.2 = To count how many answers survey has I use "help cells" (term I use, not official!); again using COUNTIF function I count in cell G6 how many answered Yes when asked if a Windows Insider, H6 counts how many answered No. As answering this question is required, everyone taking the survey must answer either Yes or No. Cell I6 then does the simple maths G6 + H6 (Yes answers + No answers) with formula SUM(G6+H6) to get grand total, which is then send to cell B6

2.5)
#3 in screenshot in step 2.2 = The same with row 18. For not using Windows 10 I had two options in this example survey, number of those who have used but not using anymore is counted in cell G18, those not using at all in cell H18, and total in I18, which is then sent to E18

2.6) #4 in screenshot in step 2.2 = also for primary Windows 10 information source I had two "other" options, simple Other and Another Windows forums. I count these in G27 and H27, total in I27 and send this value to E27




OneDrive - Create an online Excel survey with free Office Online Part Three OneDrive - Create an online Excel survey with free Office Online
 Share and publish a survey / poll

3.1)
When done, click the Survey button on Excel Online ribbon and select View Survey:
Name:  image.png
Views: 141
Size:  27.6 KB

3.2) Click Share Survey:
Click image for larger version. 

Name:	image.png 
Views:	11 
Size:	13.1 KB 
ID:	107614

3.3) Click Create link:
Click image for larger version. 

Name:	image.png 
Views:	12 
Size:	19.0 KB 
ID:	107615

3.4) Copy the link, paste it in Notepad and save in a safe place for instance your Documents folder in OneDrive. When saved, click Done:
Click image for larger version. 

Name:	image.png 
Views:	15 
Size:	30.3 KB 
ID:	107616

3.5) You can test your survey now and see how it looks by pasting the link in any browser:
Click image for larger version. 

Name:	image.png 
Views:	17 
Size:	47.4 KB 
ID:	107617

3.6) Anyone with link can open the survey and answer it, seeing a privacy warning before clicking Submit. In surveys like this example where no names and other personal details are asked, the warning is a bit unnecessary but better safe than sorry:
Name:  image.png
Views: 136
Size:  12.9 KB

3.7) A polite thank you note is shown when answers have been submitted:
Name:  image.png
Views: 136
Size:  8.7 KB




OneDrive - Create an online Excel survey with free Office Online Part Four OneDrive - Create an online Excel survey with free Office Online
 Add survey / poll to your website or blog

4.1)
Sign in to https://onedrive.live.com, browse to your survey, select it and click Embed:
Click image for larger version. 

Name:	image.png 
Views:	12 
Size:	239.1 KB 
ID:	107620

4.2) Click Generate:
Name:  image.png
Views: 132
Size:  21.5 KB

4.3) Click Customize how this embedded workbook...:
Name:  image.png
Views: 132
Size:  27.3 KB

4.4) In small Preview, select the sheet you want to embed:
Click image for larger version. 

Name:	image.png 
Views:	13 
Size:	49.1 KB 
ID:	107623

4.5) Click Select a range, enter 'Table'!XX:YY where XX is the top left and YY the bottom right cell of the region you want to embed (in step 2.2 you can see that I want to embed region from A2 to E27). When done press TAB key to activate range selection. Bottom left of the page, select Embed code or JavaScript, for instance for WordPress or BlogSpot blogs select Embed code. Copy the code and paste it in your website editor.:
Click image for larger version. 

Name:	image.png 
Views:	16 
Size:	66.0 KB 
ID:	107624


That's it. You have created an online survey. In addition to embedding it to your website, you can simply post the link, email the link to your friends; anyone with the link can see and answer the poll.

You can see this survey in action here: A Windows 10 Survey - Windows 10 Forums


Kari