Microsoft Forms HIDDEN feature. Data, Formulas and Charts update LIVE in EXCEL
Vložit
- čas přidán 31. 07. 2024
- Tired of having to sign in to Microsoft Forms to see responses or having to keep downloading the Excel workbook to have the most recent data? In this tutorial you learn how data from Form submissions will update live in your Excel workbook and simultaneously update update formulas and charts.
0:00 Intro
2:05 Replicating the problem
6:01 Creating a Form based on an Excel Workbook
8:16 Submitting forms and collecting data
9:45 Seeing data updated automatically in Excel online
10:25 Opening the Excel workbook from Desktop version of excel
10:42 Adding formulas and charts to the Excel workbook which will update automatically when a form is submitted
13:41 Submitting a form and seeing data, formulas and charts updated automatically
Link to Excel tutorials playlist:
• Spreadsheets (Excel) I...
This is absolutely gold! Didn't know you could do this, and had to download that stupid sheet every time you needed an update. Something which really complicates commenting and working with the data if the form is to stay up for an extended period of time. Keep up the good stuff! =)
Thanks for the feedback. Gladnyou found this usefull. You are absolutely right. Those forms which are active for a long time are a pain when you have to keep downloading the results.
Thanks very much for this extremely helpful video! I was just about to hire someone to help me figure out how to automatically apply formulas to the results of an Excel survey. With your excellent tutorial and some experimentation, I learned that I can insert columns with formulas and the results of the survey will still be inserted into the proper cells. You have saved me money and a lot of frustration. Thank you!
That is fantastic. I am so glad this tutorial helped you out. If you want to learn Excel then i have sone great tutorials in a play list which my IGCSE students and staff use. They start from the very begginner to more advced functio s. I will send you links in a follow up reply.
Here are links to 2 playlists. The first is a complete series for begginers to advanced. The second is for specific tips and tricks.
czcams.com/play/PLWeerq9oa1ndM1kKGiwe4uM_npzPtC6M8.html
czcams.com/play/PLWeerq9oa1ne0OAyjtdea9x-4WFDLAUDg.html
Perfectly timed - just looking at a complaint handling process which was being handled through an Outlook email and then manually entered to an Excel sheet etc. - lots of handling and interpretation. Thinking "Forms" but the continuous file download was daft - then I see this and my problem is solved. Thank you.....
I am glad this helped. Thanks for your feedback.
Dear Nicos: You are right. I just failed to make a form by creating directly from the Form App. It's because the
chart will NOT be updated automatically!! It should be done with your advice to create it in onedrive. You save my day. I'm grateful to your teaching.
There are already great tools in front of us. Just use it without learning how to code.
My project: Our hotel has 2 generators and we want to record and analyse the consumption rate of diesel. Besides, we need to predict how fast should order the diesel to refill the tanks. In Africa, the power supply is incredibly unstable and the generators are the secondary power source, not just a backup. It will be a disaster if the hotel has an prolonged outage. Now I ask our engineering crew to record the data through Microsoft Form and the result will be updated instantly. Everybody shares the same information by clicking the link. Beautiful!
@graciasnara thank you for your comment. It is great to hear that my tutorials are being used beyond the academic scope. I am glad this solution has been able to help you in resolving an issue.
Excellent feature hardly many people knew it before this video uploaded. Bravo!
Glad you found this useful. Definitely not one of the most well known features of Forms
wonderful presentation! keep it up, sir!
Thanks, will do. Thanks for your comment.
Thank a lot for your video, I get what I am looking for ❤
that's great
Your Video is really great! THX
Thanks for your comment. I am glad you found it usefull
Hey Nicos, this is a GREAT demo, in fact it could be the best I've watched (and I've watched LOADS). I really like your presentation, delivery, step flow: where you're not 'fluffy' nor spend time on additional info (like the formulas). I also like that you don't going on and on and on about background what you can use the form for and so forth (which again, anyone wanting to know the 'meat' of the how-to doesn't actually require. You also have clear English and didn't seem to talk to fast where I had to 'rewind' to catch what you were demonstrating.
So, what about the things I didn't find helpful or like..... hmmm, I have to think really hard and get back to you on that one...........
In the meantime, MANY THANKS! 😉
Hehe, you had me worried at the end. I am glad you found this tutorial useful and easy to follow. Thank you for the kind words in your comment, i enjoyed reading it.
This is awesome. Thank you!
You are welcome. Thanks for the comment
This was amazing..!!
I am glad you you found it useful. Thanks for the feedback
I love this but have a question. If you have a shared drive with colleagues, how do you make the initial set up in excel be linked to the shared drive instead of your own desk top?
Hi Nicos, nice informative video. what happens if MS forms responses for some reason stops sync with the linked sharepoint excel file.
Your video helped me a lot to get live data! How can I delete responses in the workbook? the default option in the form 'delete all answers' does not work.
life saving, you're the real homie
Glad it helped. Thanks for the comment.
Great job, can’t believe I’m year late to see this
@@ousmanesidibe951 better late than never :) thanks for your comment.
Great
This is great thanks for the video! We need a "create from template" or "create from existing form" option. Otherwise have to recreate every time. Any thoughts on this? Organization moved from Google to Microsoft recently and struggling with this so far, less intuitive.
I am glad you found the tutorial useful. If you want to duplicate a Form, follow theses steps.
open the form you want to duplicate, click on the 3 dots in the top right corner, choose collaborate or duplicate, click on the link that says get a link to duplicate. Now if you past that link in your browser you will be able to duplicate the form. The responses are not copied in to the duplicate form. You can also share the link with others to duplicate the form as well. hope this helps you.
Nice feature. The only thing I would do different is using the table references in the formulas instead of F:F or G:G. for =COUNTIF() I wouldn't hard code the values to count but refer to the label you entered beside. so the formula in K2 and K3 would look like shown below:
=COUNTIF(Table1[Did you learn something];J2)
=COUNTIF(Table1[Did you learn something];J3)
Fully agree with you Roger - I was about to type the same untill i saw your comment - I would also suggest locking down the J2 & J3 reference (or create a little table for them and use table references ;-)
Thank you so much for the info! Is it possible to link to a spreadsheet from a forms that you’ve already created without using the ‘forms for excel’ option?
no, the form is generated when you create Forms for Excel. You can create a new forms for excel exactly the same as your old Form and copy the data from the exported Excel from the old form in to the excel of the new Excel. This way you have all your data and new data in one Excel. Hope that makes sense.
Hi, Thanks you very much for your helpful video. I would like ask you for support.I'm trying to find an easy way to create 100 forms with similar questions by copying the questions from one form to another and keeping the connection to onedrive excels. Do you know how to do it? Thanks for feedback. Juraj
Thanks for this useful tutorial! My understanding is that both forms created from excel or one drive don't allow editing the responses after submission, whereas if the form is created via the forms portal then the option for editing is there but the responses are to be downloaded in an excel sheet as you described. How can we have a form that collect response live and still have the option for the participants to edit their responses. I am struggling with this and I did not find the answer. Thanks again!
No sorry, not possible.At lease not as far as i know
Hi Nico. Excellent information. If you need to move the xls file associated to the form to another location or folder without affecting the relationship with the form, How do you handle that?
Never thought of that. I am not sure you can as the path is probably coded in the Form. I will look into it when i have a bit of time and if i come up witb anything i will let you know. Interesting question. If you come up with something ease let us know. The form will still wotk and you will still get the results from the Form but the connection to the Excel file will be lost. I know you will not get any errors with the Form but how to move the Excel file to a new location? Hmmmmm
was there ever a solution to this please, as I've just moved an xls sheet with a lots of responses, and a tab which I'd edited. I've moved it back to the original location but I need to go in via forms to see responses since the original link was broken.
Hi Nicos! I have a question that I was hoping you could help me with. When my form comes into Excel Online, my answers to the Text Questions that I've put Restrictions on to be Numerical answers bring the numbers into the form with an apostrophe before them. I can't seem to get the apostrophe out in order to perform calculations similar to what you are doing in your video. I've tried find and replace and formatting the columns as numbers, but I can't seem to fix the issue. Any thoughts or solutions?
Here you go. This thtorial should help you.
czcams.com/video/q2P8yKc5TMU/video.html
I love this video. But I would also like to know if I cannot simply link an existing MS form to the Form for Excel to then convert the data being collected there into a live form. Instead of recreating the whole thing all over again. Is this possible or not possible? Please let me know. Thx
I am glad you found the tutorial useful. Unfortunately you can not link an existing form to an Excel spreadsheet. I have teied to see if there is a hidden Excel spreadsheet created for basic forms which you could open and see the data populate live but unfortunately there isn't.
I think I found the solution and you can clarify if I am wrong by downloading the excel file that contains the results, copying it, executing all your steps, and creating precisely the same format in sync with your method by copying the old results and starting to submit
It actually worked. I just wanted to show the solution and help, but make sure that the actual version that will be created is exactly the same.
please i have a form and the data is still not capturing automatically in my formular table
Great step by step presentation, thank you. Good to learn to start with Forms for Excel. A question please - Do you have a video or steps on how to get multi-choice text answers from a question in Forms, to populate into separate columns when the results appear in Excel? Currently all the answers are appearing in one cell separated by semicolons. I'm hoping there's a nice solution as the results appear, rather than having to manipulate the text after in Excel. Thanks
I would need to see an example but from what you have explained, directly from Forms no. From Excel though it is a simple task of selecting the data with the answers separated with ;
from the Data ribbon select Text to Columns,
Click next and in the section, other, for the separator enter semi colon. ;
Your answers will be separated in to columns.
@@paphitisn Thank you, yes, I have been able to do the Text to Columns. Unfortunately the options don't necessarily appear in the same columns, because Forms is not counting blanks. So I have to do a lot of manipulation of the text in Excel. Is there a way to add something (like "n/a") for options that are not selected by the responder when they fill out the form? Perhaps that would let all the options or "n/a" appear in the correct column? Thank you again
@@DianaJ88_ Hi, if you are able to send me a sample form and a copy of the results i will try to see if i can come up with a solution for you. Again, a dummy sample form with the results. Make sure no personal data is included.
@@paphitisn Thank you for offering to help! I just sent something by email.
Just want to say thank you again for your help and suggesting VLOOKUP using Mixed References. Your teaching on the videos makes often complicated formulas easy to understand and use!!
It would be nice if you tell us that this is only possible with office 365 business plan, there is no forms for excel available with other plans. Thanx for the info.
Correct, I don’t have that option forms to excel. I just have the option to create the excel sheet
I followed your steps, but I don't see forms for excel option on my end, I see Form for Survey Does it need to be paid to have this option forms for excel? I don't even know my status paid or non paid. I bought the computer with the applications installed.
Hi Nicos! I have a question that I was hoping you could help me with. If multiple forms i have then I want to single Excel file with multiple sheets like Google Forms. can we do that..???
i cant really understand what you want to do. If what you mean is to have the results from multiple forms to populate different spreadsheets in the same Excel workbook then, No, you cant do that. Each form is associated with the workbook not the spreadsheet in the workbook.
Am I doing something wrong? I did the exact same steps to populate live responses from forms however, after clicking "add new" from one driver and then "forms for excel" it takes me straight to an excel sheet not the form. Please help.
Try opening the excel document and hitting "insert" and click form.
Hi! I'd like to make a live excel file from a microsoft form that allows responders to edit their submissions. When I tried this method, it no longer allows the editing of submissions. Would you have a solution for this? Thank you!
No sorry. I don't have a work around for this.
I am using Macbook pro Forms for Excel not showing as you explained. Please help me
Try this. Login to 365 through your browser. Open Excel (online) and start a new blank workbook.
Click Insert to open the Insert ribbon.
You should see a Forms button in the Insert ribbon. Click it and select New Form. It should open Forms.
Let me know if this worked for you
Can I add questions for users to upload files? I can't seem to figure out how.
Yes. Open your Form and click Add New, from the list of question types, select Upload File. Users will only be able to upload PDF and Word document files.
How to Duplicate/copy the same Form and Excel set up to use over again each fortnight?
Unfortunately no. Creating a copy of the form will create the new form but not a separate Excel file for you to see data appear live in the Excel workbook
if i have collected data outsode MS forms, how can i input that new data in the excel sheet and have MS forms results reflect the new data such as in charts
As far as i know you cant
Does this work if instead of one drive we create the form/excel in sharepoint?
sure. open you sharepoint site and simply choose new Forms for Excel. You can also open any Excel workbook through the online version of Excel, navigate to Insert ribbon, click Forms and create new Form. So as long as you have an Excel workbook stored anywhere in your 365 cloud (synced or not) you can apply a form to it.
I have been looking but havent found a way to create a unique ID based on each different response. Do you know how to do that? Im trying to ensure my form has something identifiable for colleagues to reference.
I have not tested this yet so cannot confirm but i would go about this by creating a form based on an Excel workbook as described in the video and then add a column that would automatically generate a unique identifier whenever a new entry is submitted. It could be as basic as an incrementing number or use a date time now() function. I will also test this asoon as i can and confirm. If indo getting working i will create a tutorial on this also.et me know if you try the above with any success or fails.
Creating a new Forms for Excel as explained in the video will automatically create an ID field which is an incremented number for each new response. This is a unique field which can be used to identify each record. you can see this in the video at 3:41. The left most column is an id field. Is this what you where looking to do?
@@paphitisn Not quite, Im sending an email through power automate with the info from the microsoft form fill; I need the form to have a unique identifier so that my people know which form is which and once it sends the email to them it has the unique identifier on the email confirmation. Does that make sense.
@@mii1563 That now makes sense. you want a unique form identifier not record identifier.
Not quite sure how to tackle this one. I will play with it a bit and see if i come up with something, I have not use pawer automate much, apart from triggering an alert based on a response and sending the alert to an email
Why don't you make the quiz takers identify themselves in the first question. Then you'll have the type of ID you need automatically
How do I re-add the completion time column and have it repopulate when a form is submitted? I deleted it
Not sure you can add it again but you can try to restore it as it is saved in OneDrive. Open the Excel file, go to file - info - version history
Find the most recent version that contains the column and restore it.
If you have received responses in the file from Form submissions since you deleted the colimn make sure to copy those responses to another file before you restore to an earlier version. You van add them again after restoring.
I don't have the "Forms for Excel" option. logged into 365 then opened excel, tried to insert a form, Not there
You were not supposed to open Excel. Did you watch the tutorial?
View from 6:20
how i will sending excel from onedrive to email
Can the file in web excel be edited in the PC instead of the web base?
Yes
Can't figure out what i'm doing wrong but the responses aren't populating in my workbook
if you have the option to create Forms from Excel from within OneDrive then it should work providing you have not renamed or moved the Excel file.
Any way to do this but with Quizzes and not just forms?
sorry, no
How can you delete a submitted response and have the response removed from the Excel sheet?
You can view the results in Forms, scroll to the response you want to delete and click on the 3 dots. choose delete. Then open the results in Excel and delete the row with the response you do not want.
How can I get actual email addresses i/o anonymous?
Not sure what you mean. If it is anonymous then you can not record emails unless you add a field for them to enter their email.
7:10 interesting step without PowerAutomate
This may be a little off topic, and you may know a solution, for some reason Microsoft Forms format numbers as text and it go into the excel sheet with a hidden apostrophe in front of the number which kinda defeats the purpose of the process I was working on.
To solve the issue you can do the following in Excel.
Select the data
Then click Data > Text to Column, and in the Convert Text to Columns wizard window just click Finish. Apostrophes will be gone
@@paphitisn Hi Nicos, we have to do this every time we receive a new form response. Is there any other workaround for the same that we can do once and it fixes the issue?
Every time you download the reults yes you will have to do this or one of the 4 methods explained here: czcams.com/video/q2P8yKc5TMU/video.html
The reason is because it is a text feild in Forms. So it saves as text and there are different number and date conventions so it would be impossible to know what each person is writting. Some would use a comma for a decimal and others a dot. The best approach is to edit the results when the submissions are finished.
I just finished achieving the same thing using power automate when there was a feature for just that 😂
It does not hurt to know multiple methods to do something 😀
@@paphitisn indeed ! Thanks for the info
czcams.com/video/ue4XwOTIN58/video.html at 6:59 , you said that we can click + and add "Forms For Excel". Now I have a subscription of Office 365, But I am not seeing any such option.
I dont think this is availae for personal licenses. A3 A5 E3 E5 licences
I don't have the "Forms for Excel" option
Only "Forms" :\
Are you on GCC (Government Community Cloud ) ?
Try this. Login to 365 through your browser. Open Excel (online) and start a new blank workbook.
Click Insert to open the Insert ribbon.
You should see a Forms button in the Insert ribbon. Click it and select New Form. It should open Forms.
Let me know if this worked for you
@@paphitisn Thank you!
@@svetlageorgieva8848 you are welcome
How can i create a Quiz?
If you are looking to make a quiz in a way that you can see the results populate in Excel as explained in the video, unfortunately you can not. This is only for Forms. Quizzes you will have to make by going to the forms app and clicking the Quiz button.
@@paphitisn Can i check the answes online in excel from quizzes?
@@GT2024. yes you can. You can also export the results to an Excel file.
@@paphitisn but when a i create a quiz, and click in excel for answer, it download and it doesn´t open online as the form does. How can i do it please? do you have a link with the step by step please
@@GT2024. this feature only works with a form not a quiz