How to Create a Questionnaire in Excel (2 Easy Ways)

In this article, we will learn to create a questionnaire in Excel. A questionnaire is a set of questions or specific items with options. It is used to collect information from a respondent. In Excel, users can create a questionnaire following some easy steps. Today, we will demonstrate 2 easy methods. Using these methods, you can easily create a questionnaire in Excel. So, without further delay, let’s start the discussion.

You can download the practice workbook from here.

2 Easy Ways to Create a Questionnaire in Excel

You can create a questionnaire in Excel using two ways. In the first method, we will create a questionnaire manually. Here, you need to insert the keywords of the questions as headers of columns. We will demonstrate the whole method in easy steps.

In the second method, we will use VBA to create a questionnaire in Excel. The overview of the questionnaire is given below:

1. Create a Questionnaire Manually in Excel

In the first method, we will create a questionnaire in Excel from scratch. In our case, we will ask for the Name, ID, and Department of some employees of a company. Also, ask them if they need the Vacation this year. Here, we can’t insert a whole question. So, we need the keyword of that question. And we will use it as the header of a column. In the steps below, we have demonstrated the procedure with easy instructions. So, let’s pay attention to the steps below to learn the method.

STEP 1: Insert Keywords of Questions

• Firstly, we need to identify the keywords of the questions and put them as headers like the picture below.
• For example, if you want to ask, “What is your name?” first, then, the header of the first column will be “Name”.
• The second question is. “What is your ID number?”, so, here the keyword becomes ID.
• The third question asks the employees about their Department, so it is in Column D.
• And lastly, we need to know if an employee needs vacation this year. We want the answer in Yes or No. So, the header of Column E is written as “Need Vacation (Y/N)”.
• In this way, you need to find the keywords of all questions and use them as headers one by one.

STEP 2: Create Table Using Keywords

• In the second step, we will create a table.
• To do so, select the headers.
• Then, press Ctrl + T.
• A message box will appear.
• Check “My table has headers”.
• Click OK to proceed.

• As a result, you will see a table like the screenshot below.

STEP 3: Create New Tab for Questionnaire

• Thirdly, we need to create a new tab for the questionnaire.
• For that purpose, select the headers of the table first.

• After that, click on the File tab.

• Now, select Options. It will open the Excel Options window.

• In the Excel Options window, select Customize Ribbon and then, select New Tab.

• After that, right-click on the New Group and select Rename from the menu.

• A Rename box will appear.
• Type the Display Name and click OK to proceed.
• Here, we have renamed the new group Questionnaire 1.

• In the following step, select “Commands Not in the Ribbon” in the “Choose commands from” box.
• Then, select Form and click on the Add option.

• After following the above steps, you will see the Form under the Questionnaire group inside the New Tab.
• Click OK to move forward.

STEP 4: Open Form to Enter Data

• In this step, go to the New Tab and select Form.

• As a result, you will see a form like the picture with the tags of the headers.
• Here, a respondent needs to insert his Name, ID, Department, and Need for Vacation one by one.

STEP 5: Enter Data for Questionnaire

• In the fifth step, the respondents need to insert the answer to the asked questions.
• For example, employee John has inserted his information.
• So, to get this information into the worksheet, click on the New option.

• As a result, the information will be available on the worksheet and the form will be ready for taking another response.
• In this case, employee Paul enters his data and clicks on the New option again.

• As a result, you will see the information in the excel worksheet.

• For entering more data, follow the above steps and click on New.

Final Output

• In the end, you will get the recorded data in the worksheet and you can use the same steps to enter more data.

2. Apply Excel VBA to Create a Questionnaire

In the second method, we will apply VBA to create a questionnaire in Excel. VBA stands for Visual Basic for Applications. Using VBA, we can implement various tasks easily. Here, we will ask the respondents a few questions and they need to answer using some options. The options are Not Sure, Agree, Disagree, and Maybe. So, let’s follow the steps below to see how we can create a questionnaire using VBA in Excel.

STEPS:

• First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
• Alternatively, you can press Alt + F11 to open it.

• In the Visual Basic window, select Insert >> Module. It will open the Module window.

• Now, copy the code below and paste it into the Module window:

Option Explicit
Sub Create_Questionnaire()
Dim GrupBx As GroupBox
Dim OptnBtn As OptionButton
Dim iMxBtns As Long
Dim icell As Range
Dim iRng As Range
Dim iWks As Worksheet
Dim xCtr As Long
Dim xFrstOptnBtnCel As Range
Dim xNumOfQ As Long
Dim iBorder As Variant
iBorder = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
        xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
iMxBtns = 4
xNumOfQ = InputBox("Set the numbers of questions", "Questions", 8)
Set iWks = ActiveSheet
With iWks
Set xFrstOptnBtnCel = .Range("E2")
.Range("A:D").Clear
With xFrstOptnBtnCel.Offset(-1, -1).Resize(1, iMxBtns + 1)
    .Value = Array("Questions", "Option1", "Option2", _
                    "Option3", "Option4")
    .Orientation = 90
    .HorizontalAlignment = xlCenter
End With
Set iRng = xFrstOptnBtnCel.Resize(xNumOfQ, 1)
With iRng.Offset(0, -1)
    .Formula = "=ROW()-" & iRng.Row - 1
    .Value = .Value
End With
iRng.Offset(0, -3).Value = 1
With iRng.Offset(0, -4)
    .FormulaR1C1 = "=IF(RC[2]="""","""",IF(RC[2]=6,""N/A"",RC[1]*(RC[2]-1)))"
End With
.Range("A1").Formula = "=SUM(A2:A" & xNumOfQ + 1 & ")"
With iRng.Offset(0, -4).Resize(, 4)
    For xCtr = LBound(iBorder) To UBound(iBorder)
    With .Borders(iBorder(xCtr))
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Next xCtr
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
iRng.EntireRow.RowHeight = 20
iRng.Resize(, iMxBtns).EntireColumn.ColumnWidth = 9
.GroupBoxes.Delete
.OptionButtons.Delete
End With
For Each icell In iRng
With icell.Resize(1, iMxBtns)
    Set GrupBx = iWks.GroupBoxes.Add _
        (Top:=.Top, Left:=.Left, Height:=.Height, _
        Width:=.Width)
    With GrupBx
    .Caption = ""
    .Visible = True
    End With
End With
    For xCtr = 0 To iMxBtns - 1
      With icell.Offset(0, xCtr)
        Set OptnBtn = iWks.OptionButtons.Add _
              (Top:=.Top, Left:=.Left, Height:=.Height, _
               Width:=.Width)
        OptnBtn.Caption = ""
        If xCtr = 0 Then
          With icell.Offset(0, -2)
            OptnBtn.LinkedCell = .Address(external:=True)
          End With
        End If
      End With
    Next xCtr
  Next icell
End Sub

• Press Ctrl + S to save the code.
• In the following step, go to the Developer and select Macros. It will open the Macro window.

• After that, select the desired code in the Macro window and Run it.

• As a result, a message will appear and it will ask you to set the number of questions.
• Here, we want to ask 5 questions to our respondents. So, we typed 5.
• Click OK to proceed.

• Instantly, you will see the results like the picture in the worksheet.
• Here, you can see Column D is for questions and Columns E, F, G, and H are for the options.

• Now, you need to format the questionnaire according to your preferences and insert questions.
• For example, we have inserted three rows and a column. So, now range B4:I9 contains the questionnaire.
• We have also hidden Columns C & D.
• Also, we have inserted the questions in the assertive form.
• There are 4 responses for each question and the responses hold values from 0 to 3.
• Here, Not Sure has a value of 0, Agree has 1, Disagree has 2, and Maybe has 3.
• For your convenience, we have shown the answers of a respondent.
• Here, Cell B4 stores the sum of the range B5:B9.

Conclusion

In this article, we have demonstrated 2 easy ways to Create a Questionnaire in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.