Creating a Self-Populating Form and Spreadsheet Using Microsoft Tools

There are several reasons to use a form to collect data for your business, from customer impressions and service needs to employee feedback about internal matters as a means of collaborative decision-making. The trouble comes when it is time to compile all the responses… unless you know how to use the tools at your disposal.

For this week’s tip, we’re walking through the process of creating a form that self-populates a spreadsheet using Microsoft’s toolkit.

It’s actually a lot easier than you might think.

You’ll Rely on Two Tools: Microsoft Forms and Microsoft Excel

Let’s walk through the process.

First, Start with Microsoft Forms

In Office.com, select the app launcher in the top-left corner and then choose Microsoft Forms. This will open the start of a brand new form.

1. Create a New Form
   Click on the "New Form" button to start a new form. Change the form name to whatever you need to keep it organized, and update your form title to the name you want your participants to see. Let’s assume this is meant to be a client satisfaction survey, so we’ll update the form name to “Satisfaction Survey” and call the form “How Are We Doing?”
2. Add Form Description
   You’ll see the option to create a form description—your opportunity to sell your form and provide any pertinent information or instructions you want to communicate to the person filling it out.
3. Add Questions to Your Form
   Click on the "Add new" button to start adding questions to your form. Microsoft Forms provides a variety of question types to choose from:
   • Choice: This allows you to create multiple-choice questions for your audience to answer.
   • Text: This module allows you to prompt an audience member to provide a short or long response to a question.
   • Rating: This allows your audience to rate an item on a scale that you define.
   • Date: This enables your audience to select a date.
   • Ranking: This allows your audience to rank a list of items.
   • Likert: This module allows you to create a group of statements with which respondents can agree or disagree.
   • File Upload: This allows respondents to upload files directly to the form.
   • Net Promoter Score: This allows you to measure customer loyalty.

Customizing Your Form

For each question, you can customize the settings:

• Required: Make the question mandatory.
• Subtitle: Add a subtitle for more detailed instructions.
• Branching: Create a branching rule to guide respondents to different questions based on their answers.

Reviewing Your Responses in Microsoft Excel

Once your form is set up and responses are being collected, you can easily review and analyze the data in Excel.

1. Linking to Excel
   In Microsoft Forms, navigate to the "Responses" tab. You’ll find an option to "Open in Excel." Clicking this button will create an Excel workbook with your form responses, updating in real-time as new responses come in.
2. Analyzing the Data in Excel
   With your responses now in Excel, you can leverage all of Excel’s powerful tools to analyze your data. Create charts, apply filters, use pivot tables, and more to gain insights from your form responses.

Advanced Tips for Microsoft Forms and Excel

• Email Notifications: Set up email notifications in Microsoft Forms to alert you whenever a new response is submitted.
• Collaborate with Others: Share your form with colleagues for collaborative editing and review.
• Integrate with Power Automate: Use Power Automate to create workflows that automate actions based on your form responses.

Hopefully, This Walkthrough Helps Make the Process Clear

This is just one small sample of what your technology can do in the right hands. We can help ensure that its support and maintenance are in the right hands, too. Reach out to us to learn more about our managed services.