Excel Forms Beginner Tutorial
We’ve all been there. Some basic data must be collected. An email is sent out with … with an Excel attachment to complete. Now, I’m a huge fan of Excel, but even I struggle with this. Why? Because the process of downloading, saving, completing, replying and attaching a file takes a long time, and is prone to error. Not to mention downloading and collating files at the other end. A nightmare!
Microsoft Forms offers an easy and practical alternative. In this video, I show you how to create your first Form, how to easily distribute to colleagues and even how to analyse data gathered. Yes, don’t worry – we end up in Excel for the data analysis!
WATCH THE VIDEO Here
00:00 FORMS INTRODUCTION
Forms lives in the cloud and should be available to you if you have a Microsoft account. Full details on Microsoft plans are at the below link at the time of recording. Click Here
00:51 STEP 1: CREATE A FORM
The first step is to navigate to Microsoft Forms and sign in using your Microsoft account. Clicking ‘New Form’ takes you to the form creation interface. Here you can name your form and add a description, then start adding questions.
02:11 STEP 2: ADD QUESTIONS
A useful feature of Forms is the range of question types. Questions requiring simple text inputs are of course possible. But, it’s easy to create multiple choice questions, rating and ranking questions – and there’s even a date picker option. It’s so much more practical than downloading and populating a spreadsheet! And, the form displays equally well on PC, tablet and mobile.
In the video, I quickly create a form to gather data from colleagues about the Tiger Christmas party. So, go ahead and experiment with your own form now. Try different question types and remember you can quickly re-order questions at any time. There are advanced options too such as question branching – these are out-of-scope for this beginner session but are useful in the real world.
05:04 STEP 3: DISTRIBUTE THE FORM
The next step is to share the form. Again, there are a range of options from a standard email to a sharing link … and even a QR code. Yes, you can organise your Christmas Party with a QR code. Cool! And remember, Forms display well on mobile, as I show in the video, so you could easily share a link via WhatsApp.
06:06 STEP 4: LOCATE THE DATA
With the Form shared and, presumably, being completed, it’s time to locate the collated data. A great feature of Forms is how collated data is automatically stored in an Excel file on OneDrive. Remember to test this by completing a response yourself – the data should drop in quickly. In the video it takes just a second or two …
08:00 STEP 5: DO ANALYSIS
Forms offers basic visual analysis as I show you in the video. But, with the data automatically collated in Excel, it’s easy to get whatever analysis you need. Analysis via basic formulae or pivot tables is straightforward. In the video, I show you how to create a simple chart using Excel’s COUNTIF formula.
But, we’re really just scratching the surface here. On client projects, we’ve used Forms to trigger, for example, complex automated processes that gather and analyse data from teams dispersed across the world. You’ll likely need Power Automate to help you do that – here’s a beginner video to get you started.
So, have you managed to create and share a Form yourself? And did you analyse the data in Excel? I would love to hear what you make of Forms in the YouTube comments, I’ll get back to you there!
30 DAY EXCEL ANALYST (COMPLETE EXCEL COURSE)
THE FIVE LEVELS OF EXCEL AUTOMATION