An Introduction to Microsoft Forms for Excel

Have you tried Microsoft Forms yet?

It’s a great new tool from Microsoft that allows you to quickly and easily create surveys, quizzes and polls.

There is a wide variety of potential uses for these. You could use them to get customer feedback, collect reviews and testimonials or even use them as data entry forms.

I’m using these using these forms in my courses to get student feedback and reviews so I can improve my teaching. I also suggested them to a friend who was looking for a simple data entry solution.

Let’s take a look!

Sign Up For Microsoft Forms

If you’re signed up with Office 365, then you already have Microsoft Forms and it can either be accessed from OneDrive, SharePoint, Excel Online or the Forms website.

If you don’t have an Office account, then you can still sign up to use forms for free here https://forms.office.com/ by creating a Microsoft account.

Creating a New Form or Quiz

There are a couple different ways to create a form or quiz with Microsoft Forms.

Creating a Form in OneDrive

You can create forms inside OneDrive personal or business. Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.

You will then be asked to name the workbook associated with your form. This workbook will be saved in your chosen folder and will be where all the form submissions will be saved.

Creating a Form in SharePoint

The same thing can be done to create a form if you have an Office 365 business account with SharePoint online.  Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.

This also prompts you for a new workbook name where your form submissions will be saved.

Creating a Form in Excel Online

If you’re working with Excel Online, you can also create forms. Go to the Insert tab ➜ click on the Forms button ➜ select New Form from the menu.

This will create a form that’s linked to the current workbook.

Creating a Form from the Website

After you sign into https://forms.office.com/ you should be taken to the home page where you can create new forms and quizzes. If you don’t land on the home page, you can always get there from any screen using the button in the top left corner of the screen that’s labelled Forms.

From the home screen, click on either New Form or New Quiz.

The Different Types of Questions

Microsoft Forms currently has two types of forms. There are Forms and Quizzes. They both allow you to create the same type of questions. The only difference between them is you can assign point values and correct answers to quiz questions in order to calculate a quiz score.

All the questions can be accessed by clicking on the Add new button. This will show the list of available questions choices, but note that some are hidden in a menu accessible by clicking on the Ellipses.

Types of Questions

There are 7 types of questions available. Each has different options.

  • The Choice option allows you to define a list of possible answers for the user to select one or more answers from.
  • The Text option allows you to create long or short answer text questions.
  • The Rating option allows you to create questions with a star or number rating between 2 and 10.
  • The Date option allows the user to select a date from a calendar to answer the question.
  • The Ranking questions allows a user to drag and drop items to answer questions like order of preference.
  • The Likert option allows you to create “agree/disagree” scale type questions.
  • The Net Promoter Score option allows you to create questions like “How likely are you to recommend [brand X] to a friend or colleague?” that utilize a net promoter style grading.

Tip: Some question types like the Choice and Ranking options allow you to copy and paste from a range in Excel or a line separated text file. This is handy if you have a long list of choices to add.

Each type of question has a different menu. For example, the above picture shows the available options for the Choice style questions.

  1. You can copy, delete or move the question from the menu in the top right of the question.
  2. You can add the actual question along with a subtitle (the subtitle option is found in the Ellipses menu).
  3. Forms has some built in AI capability to suggest answers for some types of questions. You can select individual items from its suggestions or add them all.
  4. For the multiple choices you can add or delete choices. You can mark the correct answer (for quizzes) and add comments to the choices.
  5. You can add more choice options.
  6. For quizzes, you can assign a point value for the purpose of calculating a quiz score.
  7. You can allow multiple answers and set the question to require an answer in order to submit the form.
  8. Further options are available in the Ellipses menu.

Form Sections

Sections in forms or quizzes allow you to break up the form into parts.

If you have a lot of questions in your form and don’t use sections, then the user would see all the questions on one page. Adding sections means you can break this up into multiple pages and the user will only see the next section of questions after completing the current section.

This can help with form submission rates, as seeing long lists of questions can discourage a user from answering all the questions and submitting the form.

You can add sections by clicking on Add new ➜ Ellipses menu ➜ Section.

Previewing a Form

When you’ve done creating your form, you can easily preview it and see exactly what a user will see.

Click on the Preview button in the top right to view and test the form. Careful though, as submitting the form in preview mode will still add the response to your results and you will have to manually delete the response to remove it from your results.

You’ll be able to preview what the form looks like on both mobile and desktop by using the buttons at the top right while in preview mode.

Form and Quiz Settings

Each form has some important settings that can be found in the Ellipses menu.

  1. For quizzes, you can choose to show the results to respondents automatically after submission.
  2. Forms and quizzes can either be public or private to an organization. When shared within an organization, you can chose to record the respondent’s name and limit users to one submission.
  3. There are options to open or close the form to accepting responses. You can set a start and end date for accepting responses. You can shuffle the order in which questions appear. You can add a custom thank you message that appears after a user submits the form.
  4. You can set notification options to send email notifications to each user or to yourself when a new response is received.

Form Branching

The above is an example of a form that uses branching.

Branching is one of the most useful features in Forms, but it’s unfortunately hidden inside an Ellipses menu. This will allow you to have different questions appear next based on how the user has answered a previous question.

To create a conditional form, click on the Ellipses found in the top right ➜ then select Branching.

This example asks the user if they’ve used Microsoft Forms before and gives two options, either Yes or No. If the user selects yes, then they are asked to rate the product out of 5 stars. If the user answers no, then they are asked why not. This way users are not shown questions that are not relevant to them.

Viewing Form Results

At some point, you’re going to want to take a look at the answers that have been submitted by people using your form. This can be done in the Responses tab of any form where you can see a summarized version of the results.

  1. You can view the details of each result individually.
  2. You can view all the results in the associated Excel file.
  3. You can share the results by creating a summary link. Click on the Ellipses ➜ choose Create a summary link.

In fact, I created a summary link to the above example for which can be viewed here.

Form Themes

There’s not much you can do in order to change the look and feel of your forms, but you can change the colour or background image.

Go to the Theme menu in the top right. Here you can select from a couple preset themes or if you click on the plus icon, you can select a custom colour or background image.

Sharing Your Forms

How do are you going to use your new form?

The whole point of creating a form is to collect information from users, so after creating a form you’re going to need to share it with your user audience! This can all be done from the Share menu in the top right.

  1. You can choose to make the form available to anyone with the link or only people inside your organization.
  2. You can copy this link and send it to anyone you want to complete the form.
  3. Sharing the form can be done via link, QR code, embedded HTML form code (see example of embedded form above), or by email.
  4. You can share a copy of your form as a template via a link so others can modify it for their own use.
  5. You can collaborate on form creation within an organization or with external users with an Office account.
  6. You a link to collaborate on editing the form can be copied and shared with anyone you want to give access to edit the form.

Conclusions

If you need to collect information from different users, then Microsoft Forms might be the tool for you.

With forms, you can quickly and easily create questionnaires that you can share both internally or externally from your work.

These forms will automatically collect and store the responses inside an Excel workbook so they can be easily viewed and analyzed later.

It’s another great tool in the Office suite that works well with Excel and is one you’re definitely going to want to explore using.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

5 Comments

  1. Kevin Hankins

    MS Excel Enterprise 2019 64 bit, WITH a signed-in MS account, does not show an Insert Forms button.
    How do I get the button????

    • John

      Try creating a form from OneDrive, or log into forms.office.com

      • Magdoulin

        If I’ve an existing form already, how could I place its exported excel file to the SharePoint? You know, the opposite way around of the above, not creating new form and having its excel file in the SharePoint, instead, creating the excel file in the SharePoint for the existing form, is it doable without the need to use Flow?

  2. Guy Boswell

    I think there is a Power Automate Flow created in the background, triggered by a form completion, getting the form data and writting it to the spreadsheet. Am I right? If so can I access and edit that flow?

    • John

      It would probably be connected via API (flows use the same graph API) and wouldn’t be editable.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃