Unlock the power of Microsoft Forms in Excel

Podcast episode

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode we're actually not going to be talking about Excel so much but Microsoft Forms. There is a link back to Excel so tuned for that.

So Microsoft Forms are part of the Microsoft 365 subscription and you can access them online. You can see all of these different types of form features on the companion video so you can check that out on the In the Black website.

Now forms can be used for feedback, quizzes, even expense recruitment. Now just a note on Microsoft 365 which is the subscription for Microsoft. It's getting a name change so by the time you listen to this it's going to be Microsoft 365 CoPilot and CoPilot is Microsoft's AI interface. So CoPilot can be part of all of the Office suite like Excel, Word, PowerPoint etc.

But back to Microsoft Forms. Things change pretty quickly with Microsoft these days and after I wrote the article on Microsoft Forms there was an update that the desktop version of Excel will have access to forms directly in the insert ribbon. So keep an eye out on your insert tab in Excel and you might see an icon on the far left hand side for forms and so you can go directly to the website to create the forms.

Now forms allow you to import from Word or PDF documents so if you have a list of questions with answers you can import them into forms and they will automatically create the form for you. On the companion video I've created a form from scratch.

Now these forms can be shared via email, they can be embedded on the website, you can use a QR code to access them, they can be used just within your organization or you can make them public. These forms also display well on mobile devices and the results are captured in Excel. So that's the link back to Excel there.

There's also a reporting feature associated with forms so you can look at their visualizations of the responses. There's eight different types of questions that you can include on a form and there's also a section so I will explain how the section can be used when we get to the question type that needs sections.

So the first type of question you can include is a straightforward choice. There's two types of these choices, the first is just a single selection and that is like what's called a radio button in interface terminology. You can only choose one out of a list. Now this one also allows you to convert this list into a drop down because you're only choosing one, a drop down interface also works for this.

The second type of choice is a multiple choice so you can select more than one of the items that are listed. You can even limit the number of items that are allowed to be chosen as well. Now with all questions they can be made required or not. If they are required then when they're displayed there's a little red star next to it so to say that it's required.

Also, all questions can include an image so you could include an image of something and ask a question about that. There's also what I call an ellipse which is just the three dots. It's at the bottom right hand corner of a question when you're creating it and this has a few extra options. One of the options that's useful is the ability to shuffle answers. So what this does is this randomizes the sequence of the answers.

Now depending on your question you may not want that. It's also useful so that when you use the shuffle answers there's no built-in sequence there so you're getting sort of a randomized list of the entries there.

Now the other one that I didn't really cover in the video is what's called branching. With Microsoft Forms you can set up branching based on the responses. Based on what the user comes back with, how they respond, you can set up a specific branch of questions that follow that question if you like so that you can set up different branches based on different responses.

Obviously that's a little bit more complex so I didn't cover that in the video but that's also accessed via those three dots that's at the bottom right hand corner when you are creating a question.

The second type of question, very straightforward, is just a free form text answer. The only choice there you want to make is whether it's a long or a short and that's just via a little button to select that.

The third type is what's called a rating. So this uses symbols, typically you might use a star but you can use different types of symbols that are listed so you can list between two and ten symbols.

The fourth type is a date picker. Now I wish Excel had a date picker so that's the typical calendar display that you can choose a date from so that's also built in to the forms.

The fifth type, this one's unusual in that it's a ranking but it allows you to move the lists up and down within the listing to create a sequence so you can ask people to rank the entries. The example I used was the functions in terms of usefulness so you can drag the entries up and down. There's also some arrow buttons that you can use to move the entries up and down so that's a ranking type.

Now I learnt a new word and this is the type of question where you'll have a statement and then you'll have let's say five columns going across and the first column might be strongly disagree and then, neutral, agree and strongly agree. Now this is called a Likert question and you can increase the number of columns that you display as well as the number of statements you make.

This is the type of question that could use sections. Likert questions tend to be in sequence and you'll tend to list like a lot of these statements together and you'll keep that same response across all of the questions. If you want to change that response then you can create a separate section and then explain that the column headings are going to change for the future ones. So that's where you want to use a section.

The seventh type of question is not a question really, it's the ability to upload a file. So for example, if you were creating an expense recruitment form you could have the user upload an image file of their receipt.

The last type of question is what's called a net promoter score. Now this is the type of question you may have seen where you have the numbers from zero to ten and you have some text on the left hand side and then you have some text on the right hand side. So the typical question is how likely are you to recommend Excel to a friend or colleague and on the left hand side is the zero and that's not at all likely and on the right hand side you'll have something like extremely likely and that's a 10 and then you choose from zero to 10.

So that's the net promoter score.

Okay so once you've created your form you then can share it using either an email, it can provide a URL, it can provide a QR code, plus it'll give you the HTML code to embed it into your website as well.

There's the ability also to limit who can access the form so you can have it only within your organization or you can make it public as well.

Now once you've got the responses in there's a built-in reporting mechanism where you can see visualizations. There's sort of a different visualization for each type of question and so you can use that.

Also, all of the responses are captured in an Excel file in a table and that's saved to OneDrive and so you can open that from your desktop and you can see the responses in the table. Just a heads up you do need to open the Excel file to get the latest list of responses. I think that's a fairly recent change there.

So Microsoft Forms, it's built in to Microsoft 365 Copilot and you can use it to create quizzes, feedback forms and expense recruitment type forms. So have a look at it, have a play with it and see how you go. Thanks for listening. Hope you enjoyed the podcast.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.

About the episode

What if you could seamlessly collect and analyse data right inside Excel?

In this episode, you’ll learn more about Microsoft Forms, a powerful tool in Microsoft 365 (which, by the time you read this may be renamed Microsoft 365 CoPilot).

Discover how Forms integrates directly into Excel, making it easier than ever to gather feedback, conduct quizzes, and streamline processes like expense approvals and recruitment.

For fast reference, use the episode timestamps:

0:00 – Introduction to Excel Tips podcast
0:10 – Overview of Microsoft Forms
0:30 – Microsoft 365 subscription and CoPilot overview
1:35 – Update on Excel forms in the Insert ribbon
2:00 – Importing from Word or PDF documents
2:21 – Sharing forms and mobile compatibility
2:55 – Types of questions in forms (introduction)
3:10 – Straightforward choice: single and multiple selections
3:45 – Required questions and image inclusion
4:05 – Shuffle answers feature and branching based on responses
5:16 – Free-form text answer option
5:20 – Rating questions with symbols
5:45 – Date picker functionality
5:55 – Ranking type questions with drag-and-drop
6:25 – Introduction of Likert questions
6:55 – Using sections with Likert questions
7:20 – File upload feature in forms
7:35 – Net promoter score questions
8:11 – Sharing forms: URL, QR code, and embedding
8:30 – Limiting form access: public vs. organizational
8:40 – Built-in reporting mechanism for responses
9:15 – Closing remarks on Microsoft Forms capabilities

Explore how to import questions from Word or PDFs, share Forms via email, QR codes or websites, and ensure a smooth mobile experience.

You’ll also learn about the diverse question types - choice, ratings, date pickers, file uploads and even Net Promoter Score - and how Forms' reporting and branching features can enhance your workflows.

If you're in accounting, business or finance, and want to optimise data collection and reporting, or just level up your skills, this episode is for you. Tune in now.

Host: Neale Blackwood CPA. He has more than 20 years of experience as a Microsoft Excel educator and is the author of more than 200 INTHEBLACK articles as well as a book, Advanced Excel Reporting for Management Accountants.

CPA Australia publishes four podcasts, providing commentary and thought leadership across business, finance, and accounting:

Search for them in your podcast platform.

You can email the podcast team at [email protected]

And you can read the accompanying online article of this podcast.

And you can read more of Neale’s Excel articles for INTHEBLACK online.