ExcelInsider Team
One of the simplest yet most efficient methods for obtaining organized data is through questionnaires. In this tutorial, you will learn how to create a questionnaire in Excel using the web hosted Microsoft Forms or the built in offline Form feature. We’ll use customer satisfaction survey data and cover all the steps to create and collect the responses.
➤ Questionnaire is a structured form for collecting responses to a set of questions. In this article, we’ll learn about questionnaires and how to create a questionnaire in Excel with Microsoft Forms and the built in Form feature.
➤ Microsoft Forms: Search for Microsoft Forms >> Sign in >> Create the questions >> Preview the Form >> Collect responses via email, QR code, etc. >> View Responses.
➤ Built in Form: Set up the column headers >> Convert to Table >> Customize Ribbon >> New Tab >> Rename Group >> Commands Not in the Ribbon >> Form >> Add >> Select data >> New Tab >> Form >> Log a response >> New.
What is a Questionnaire?
A questionnaire is a structured form intended to gather responses to a set of questions. Both Microsoft and Google offer Forms to design and collect responses for a survey or questionnaire. You can easily export the responses to Excel and perform desired analyses.
Creating a Questionnaire in Excel with Microsoft Forms
We’ll look at two different approaches to create a questionnaire in Excel and collect the responses.
Web hosted forms are ideal for online data gathering when you want the answers to be saved in Excel immediately. You can link the results to Excel using Google Forms or Microsoft Forms (for Office 365 users). Excellent for extensive surveys.
The advantages of web hosted forms are:
- Simplified sharing via an email, link, or QR code
- Automatic data synchronization to Excel
- Compatible with both desktop and mobile devices
- Built in summary within the Form platform
Let’s start with the online Microsoft Forms (for Office 365 users) to create a questionnaire in Excel.
Steps:
➤ Search for Microsoft Forms in your browser >> Sign in with your Office 365 account.
➤ Select any scenario for your form. For example, we have chosen Research.
➤ You can choose from a template or design your survey from scratch. We chose the customer expectations survey template.
➤ This is the main interface of Microsoft Forms, where you can stylize, preview, collect, view, and present the responses.
➤ Click on the title >> Change the questionnaire title to Customer Satisfaction Survey >> Insert a new question.
➤ Set the question type like Choice, Text, Rating, Likert, etc.
➤ Click on the options to edit them >> Click on Add option. You can see the recommended options shown below the first two options.
➤ After setting up all the questions, make sure to enable Multiple answers or the Required option >> Click Insert new question.
➤ Again, set the question type. For instance, we have chosen Choice, which allows you to choose one or more answers.
➤ Enter the question and set up the options as before. Remember to enable the Multiple answers or Required option.
➤ For the next question, we have chosen the Text option. The Text option allows the user to type the answer.
➤ Enter the third question. You can enable the Long answer or Required option.
➤ The fourth question is also Text based.
➤ The last question is choice based with three options, and answering it is mandatory.
➤ Use this icon to quickly drag and drop to arrange the questions.
➤ Use the delete icon to remove any previous questions.
➤ Click on preview to view the questionnaire and make any necessary adjustments.
➤ Click on the Collect responses option to share the questionnaire. You can share the link or enter the email address, generate a QR code, or embed the link to this questionnaire.
➤ After gathering the responses, click View responses to see various survey insights and summary. You can download a copy of the Excel file.
Creating a Questionnaire in Excel with Form (Offline Method)
Excel’s built in form feature is well suited to gathering offline data in a controlled setting, such as for in office feedback.
The benefits of built in form feature are:
- Works without an internet connection
- Simple and user friendly interface
- Compatible with Excel table
Steps:
➤ Set up the questions and answer options in the column headers as shown below.
➤ Select the entire range (A1:E16) >> Press Ctrl + T to convert to Excel Table >> Check My table has headers >> OK.
➤ The screenshot below shows the table.
➤ Click on File at the top left corner of the Ribbon.
➤ Select Options at the bottom.
➤ In the Excel Options window, go to Customize Ribbon >> New Tab.
➤ Right click on New Group (Custom) >> Rename.
➤ Enter a suitable Group Name like Questionnaire >> OK.
➤ Choose Commands Not in the Ribbon in the Choose commands from box >> Scroll down and select Form >> Add.
➤ You’ll find the Form option added under the Questionnaire group >> OK.
➤ Select any cell in the Table >> New Tab >> Form.
➤ Type the answer to each question >> Hit New >> The information will be logged in the Table and the Form will be ready for a new response.
➤ After entering all the responses, the table will look like the picture below.
Creating a Questionnaire in Excel: Microsoft Forms vs Built in Form
- Web hosted forms are shareable and instantly sync with Excel, ideal for distant or large surveys. However, it requires a Microsoft account or an Office 365 subscription.
- Excel’s built in form offers a simple way to collect data. However, it is a manual process, suitable for small scale surveys.
Frequently Asked Questions
How to make a questionnaire directly in Excel without web forms?
Use Excel’s built in Form feature. Go to Customize Ribbon >> New Tab >> Rename Group >> Commands Not in the Ribbon >> Form >> Add >> Select data >> New Tab >> Form >> Add a response >> New.
Is Microsoft Forms free to use?
Microsoft Forms is free to use but requires a Microsoft account. It is usually included with the Office 365 subscriptions. Another alternative is Google Forms, which requires a Google account.
How to stop people from editing the questionnaire in Excel?
You can lock the cell and use sheet protection.
How to summarize questionnaire results automatically?
The web hosted Microsoft Forms will provide insights and a summary of the results. For the offline Form, use Pivot Table and Charts to analyse the results.
How to make mobile friendly questionnaires in Excel?
Use Microsoft Forms or Google Forms.
Wrapping Up
In this tutorial, we’ve learned about questionnaires and how to create a questionnaire in Excel with Microsoft Forms and the built in Form feature. Feel free to download the practice file and share your thoughts and suggestions.
