Microsoft Forms HIDDEN feature. Data, Formulas and Charts update LIVE in EXCEL

[@tomcummins8979]

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.....

[@paphitisn]

I am glad this helped. Thanks for your feedback.

[@graciasnara]

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!

[@paphitisn]

@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.

[@georgeallen4048]

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!

[@paphitisn]

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.

[@paphitisn]

Here are links to 2 playlists. The first is a complete series for begginers to advanced. The second is for specific tips and tricks. kzbin.info/aero/PLWeerq9oa1ndM1kKGiwe4uM_npzPtC6M8 kzbin.info/aero/PLWeerq9oa1ne0OAyjtdea9x-4WFDLAUDg

[@khuram2311]

Excellent feature hardly many people knew it before this video uploaded. Bravo!

[@paphitisn]

Glad you found this useful. Definitely not one of the most well known features of Forms

[@enzym2004]

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! =)

[@paphitisn]

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.

[@sx3578]

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?

[@louiseclarkson3651]

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! 😉

[@paphitisn]

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.

[@SickBeats-i3x]

Thank you very much sir. You just saved me from days of struggle! I am very grateful

[@paphitisn]

@@SickBeats-i3x thankyou for your comment. I am glad this helped you.

[@DianaJ88_]

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

[@paphitisn]

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.

[@DianaJ88_]

@@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

[@paphitisn]

@@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.

[@DianaJ88_]

@@paphitisn Thank you for offering to help! I just sent something by email.

[@DianaJ88_]

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!!

[@MaErika-t7m]

Hello, great tutorial. Just a question, if we create a form this way, are the responders able to edit their responses if needed? Thanks!

[@paphitisn]

If youbsemect the option in Forms section then yes.

[@AhmadMohammadAlhariri]

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!

[@paphitisn]

No sorry, not possible.At lease not as far as i know

[@craigscoular5238]

A great help , thank you.

[@shababalmustaqbal]

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.

[@paphitisn]

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.

[@FabiolaVazquez-x5i]

Is there a way to add a calculation to the form entry ? Would like the ability to add a formula and generate a separate result of P/F from the form. Im thinking like a bidirectional sync between forms and excel

[@paphitisn]

not entirely sure of what you are asking but i think you want to add a calculation in the form so that the result of the form is saved in the Excel. I don't think this is possible, or at least i don't know how i would go about it.

[@sivaiyer4017]

Thank you a ton. But i have already rolled an generic forms.. is there way to do this way...without using power automate?

[@RubiJubi]

Hi Nico Thank you so much for this.helped a lot. Ive got a question about the data collected from the form. Is it ok to amend answers on the excel file? My form is abour nominating a colleague and if the name is misspelled my formulas do not capture 😞 Not sure if i can touch the data collected.. or what happens if i do? Really appreciate if you could help Thank you. Good work here

[@paphitisn]

Hi, i am glad you found this useful. The answer to your question is Yes. You can modify the names so that your formulas will show the correct results. Any changes you make will be saved and will not cause any issues. I can understand the frustration of having to check names to make sure that your formulas return the correct results so unless there is a very large number of staff then i would personally add all the names as options for them to select. Will save you a lot of headaches.

[@Estagio2Phbp]

I was wondering if you could help me. I've created a form that synchronises with an Excel file for a company where I'm doing an internship. I've created a form where users can enter the tasks they've done during the day and then analyse the data. The problem I've encountered is that the Excel file only synchronises with the answers when I open the file as an administrator, I can't get it to synchronise automatically... And I think it's stupid not to add a new Excel row when someone fills in the form.....

[@gmagcaling]

wonderful presentation! keep it up, sir!

[@paphitisn]

Thanks, will do. Thanks for your comment.

[@jandebruijn5642]

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.

[@sebastianverdugo8066]

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?

[@paphitisn]

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

[@karencoates8487]

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.

[@sandyip9342]

Thank a lot for your video, I get what I am looking for ❤

[@paphitisn]

that's great

[@SamSung-xo8wq]

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..???

[@paphitisn]

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.

[@maphekomoloto2961]

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.

[@jckv.digital]

Correct, I don’t have that option forms to excel. I just have the option to create the excel sheet

[@svetislavvidenovic7573]

Great video, thanks. Shame we can't use this method to create quizzes that are automatically graded.

[@munyaop]

Can i customize for mobile phone when the Likert scale options are overlapping?

[@paphitisn]

@@munyaop not that i know of. If yiu do come up with something though ease share ina comment.

[@arantxaiduma7985]

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?

[@paphitisn]

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.

[@JenniferWallis-y5q]

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?

[@paphitisn]

Here you go. This thtorial should help you. kzbin.info/www/bejne/p2OzaayBmJqHg7c

[@jamesangelou5171]

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

[@paphitisn]

As far as i know you cant

[@JMoney-ne3to]

This is awesome. Thank you!

[@paphitisn]

You are welcome. Thanks for the comment

[@Drie11.11]

HELP! First, thank you so much for this tutorial. I have it saved and refer back to it when I need a refresher. WHAT I NEED HELP WITH... I don't know if there was an Office 365 update that has caused this issue, but when I'm in OneDrive and hit +Add New, the "Forms for Excel" option is no longer there. I only see "Forms Survey" which does not save my survey in the folder I've selected. Can anyone give any insight or let me know if I need to configure something in settings?

[@paphitisn]

Click on the +Add New and add a new Excel Workbook. When Excel opens go to Insert at the top. In the ribbon that opens choose Forms and then click new Form (some times it takes a few seconds for New Form to be enabled). It will then create a new spread sheet and on the right it will open a window to open Forms where you can create your form which will be linked to the Excel Workbook in the new spread sheet. Create your form and share like before. NOTE. If you do not submit a response from your newly created Form and you open the Spreadsheet you will not see the columns for your questions. These will appear automatically after the first response is submitted. I hope this helps.

[@dallydelossantos2671]

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!

[@paphitisn]

No sorry. I don't have a work around for this.

[@cbsubs]

Does this work if instead of one drive we create the form/excel in sharepoint?

[@paphitisn]

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.

[@elizabethromero4348]

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.

[@rayhawkins8103]

Try opening the excel document and hitting "insert" and click form.

[@paphitisn]

With the application there is always a delay as it has to sync. In the tutorial you can see that the data in Excel updates automatically. Test by viewing the Excel file in the online version and see if it updates automatically. Question, did you create Form for Excel like i demonstrated?

[@docvigo]

Hi Nicos, nice informative video. what happens if MS forms responses for some reason stops sync with the linked sharepoint excel file.

[@RogerStocker]

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)

[@mathew9665]

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 ;-)

[@aiciled1237]

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

[@paphitisn]

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.

[@mohamedreyad4547]

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

[@mohamedreyad4547]

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.

[@JackYoung-y2j]

Can I add questions for users to upload files? I can't seem to figure out how.

[@paphitisn]

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.

[@hillerstefan4269]

Your Video is really great! THX

[@paphitisn]

Thanks for your comment. I am glad you found it usefull

[@suchjuraj2284]

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

[@priscillaholmes4961]

HELP! I managed to delete a column of data on the worksheet that will be collected from my form-- how do I restore it?

[@paphitisn]

Oepn the Excel file. Go to file, info, version history. From here choose the version before you deleted the column.

[@priscillaholmes4961]

@@paphitisn I do not see the file info option or history

[@priscillaholmes4961]

found it, but the history is not there. Is there another way to update the worksheet to capture the current form?

[@paphitisn]

@@priscillaholmes4961 click on file in top right and there should be an Info option in the menu that opens in the left menu

[@paphitisn]

@@priscillaholmes4961 i am not sure if you add the collumn and add the correct column heading it should populate the data when you close and open excel. I cant say for sure. I will have to test it

[@amjamber]

life saving, you're the real homie

[@paphitisn]

Glad it helped. Thanks for the comment.

[@samwijeratne406]

I am using Macbook pro Forms for Excel not showing as you explained. Please help me

[@paphitisn]

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

[@AbhraRoyChoudhury]

This was amazing..!!

[@paphitisn]

I am glad you you found it useful. Thanks for the feedback

[@NoName-rr9vg]

Any way to do this but with Quizzes and not just forms?

[@paphitisn]

sorry, no

[@melsonllorera3243]

Hi, how can i use this to ky existing forms?

[@lauradana6264]

How do I re-add the completion time column and have it repopulate when a form is submitted? I deleted it

[@paphitisn]

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.

[@christinebutler7139]

How to Duplicate/copy the same Form and Excel set up to use over again each fortnight?

[@paphitisn]

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

[@ousmanesidibe951]

Great job, can’t believe I’m year late to see this

[@paphitisn]

@@ousmanesidibe951 better late than never :) thanks for your comment.

[@mii1563]

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.

[@paphitisn]

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.

[@paphitisn]

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?

[@mii1563]

@@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.

[@paphitisn]

@@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

[@Rodbr-ca]

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

[@aaronlaryea844]

please i have a form and the data is still not capturing automatically in my formular table

[@wgtcinsdesign5834]

How can you delete a submitted response and have the response removed from the Excel sheet?

[@paphitisn]

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.

[@tutsecret499]

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.

[@mahlonbautista9717]

Can the file in web excel be edited in the PC instead of the web base?

[@paphitisn]

Yes

[@afruzalina5765]

How can I get actual email addresses i/o anonymous?

[@paphitisn]

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.

[@blackrose7784]

Can't figure out what i'm doing wrong but the responses aren't populating in my workbook

[@paphitisn]

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.

[@aldopandolfi7588]

I don't have the "Forms for Excel" option. logged into 365 then opened excel, tried to insert a form, Not there

[@paphitisn]

You were not supposed to open Excel. Did you watch the tutorial? View from 6:20

[@kulawat06]

how i will sending excel from onedrive to email

[@jaimecarrillo5467]

Great

[@GT2024.]

How can i create a Quiz?

[@paphitisn]

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.

[@GT2024.]

@@paphitisn Can i check the answes online in excel from quizzes?

[@paphitisn]

@@GT2024. yes you can. You can also export the results to an Excel file.

[@GT2024.]

@@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

[@paphitisn]

@@GT2024. this feature only works with a form not a quiz

[@Basso212]

I don't have the "Forms for Excel" option Only "Forms" :\

[@paphitisn]

Are you on GCC (Government Community Cloud ) ?

[@paphitisn]

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

[@svetlageorgieva8848]

@@paphitisn Thank you!

[@paphitisn]

@@svetlageorgieva8848 you are welcome

[@A_Little_O_Everything]

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.

[@paphitisn]

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

[@anuradhachaturvedi3731]

@@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?

[@paphitisn]

Every time you download the reults yes you will have to do this or one of the 4 methods explained here: kzbin.info/www/bejne/p2OzaayBmJqHg7c 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.

[@Shestadia]

I just finished achieving the same thing using power automate when there was a feature for just that 😂

[@paphitisn]

It does not hurt to know multiple methods to do something 😀

[@Shestadia]

@@paphitisn indeed ! Thanks for the info

[@Soulenergy31]

7:10 interesting step without PowerAutomate

[@ReneeR08]

Skip to 5.55 if you want to know how to make a live excel updated in real time while forms are being submitted

[@ulludacharkha]

kzbin.info/www/bejne/q5aXiaqFia6Ba5o 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.

[@paphitisn]

I dont think this is availae for personal licenses. A3 A5 E3 E5 licences