Contextures

Home > Templates > Templates > Survey

Excel Survey Template With Option Buttons

Use the Excel macro below, to quickly create a list of survey questions, with option buttons for each question's responses.

NOTE: The macro is only used to set up the survey templates. No macros are required to fill in the survey templates

survey form with option buttons

Thanks to Dave Peterson, who shared this technique.

Excel Survey Template

You can use the macro on this page, to quickly set up an Excel survey template with a list of questions, and option buttons to collect survey responses. Then you can customize the survey template, to meet your needs.

Step 1) Run Macro

First, you'll run the setup macro, which creates a basic questionnaire template. By default, there are 10 rows of question numbers, and 6 columns with option buttons, for the responses.

basic survey form with option buttons

Step 2) Customize Template

Next, you can customize the basic template. For example, in the screen shot below, I made the following changes:

  • changed question weight to 2, in cell B5
  • changed the column headings in row 1
  • added fill colour in cell A1, where the total score is calculated

survey form withrevised headings

Step 1) Run Survey Template Macro

To set up a new survey template, follow the steps below:

  1. Open the sample file that contains the survey template macro.
  2. Select a blank sheet where you want to create the survey -- the macro will create the survey on the active sheet
  3. To run the code, click the View tab on the Excel Ribbon, then click Macros
  4. In the list of macros, select the macro named SetupSurveyTemplate
  5. Click Run.
  6. When the Questions message appears, you can leave the default number, or type a different number in the box
  7. Click the OK button, to complete the macro setup steps.

survey form with option buttons

The macro creates a basic survey template, with numbered questions and option buttons.

Step 2) Customize Survey Template

After you run the macro to create the basic survey template, you can customize the survey. For example:

  • Add formatting and heading text to the survey template (optional)
  • Change question weighting numbers in column B.
    • survey form withrevised headings
  • Change the response scores in the table on the Admin sheet.
    • survey form with option buttons

Step 3) Use the Survey

To use the survey, follow these steps:

  1. Click on an option button for each question, to select a response
    • That button's value is automatically entered in column C.
    • selection values in column C
  2. To clear a selected option, delete the selection number in column C for that row.

Survey Score Formulas

The following formulas are in the survey template, to calculate the scores, based on the response selected for each question.

Cell A1 - Total Score

This formula returns a sum of all the Question scores in column A

=SUM(A2:A11)

Cell A2 - Question Score

In column A, the following formula calculates each question's score:

=IF(C2="","", IF(C2=6,"N/A", 
	B2 * INDEX(Admin!$C$4:$C$9, 
    MATCH(C2, Admin!$B$4:$B$9,0))))
How Question Score Formula Works

1) First, the two IF functions check the value in column C, twice.

  • If C2 is empty ( "" ), the formula result is an empty string ( "" )
  • Or, if C2 is equal to 6, the formula result is an "N/A" text string
=IF(C2="","", IF(C2=6,"N/A",

2) Next, if cell C2 is NOT empty or 6, the formula uses an INDEX and MATCH combination to find the response number in the lookup table on the Admin sheet:

 MATCH(C2, Admin!$B$4:$B$9,0)

and return its the response score:

INDEX(Admin!$C$4:$C$9

3) Finally, the response score is multiplied by the question weight in column B.

B2 * INDEX(Admin!$C$4:$C$9, 
    MATCH(C2, Admin!$B$4:$B$9,0))))

Survey Template Macro Code

The SetupSurveyTemplate macro code is shown below.

  • Copy the following code into a regular code module in an Excel workbook. The code can be stored in the same file as the survey, or a different workbook.
  • Then, select the sheet where you want the survey, and run the code once, to create the survey template.

Note: The first cell with an option button is set as E2 in the code.

Sub SetupSurveyTemplate()
'Downloaded from
'  contextures.com/xlForm01.html
'by Dave Peterson
  Dim grpBox As GroupBox
  Dim optBtn As OptionButton
  Dim maxBtns As Long
  Dim myCell As Range
  Dim myRange As Range
  Dim wks As Worksheet
  Dim iCtr As Long
  Dim FirstOptBtnCell As Range
  Dim NumberOfQuestions As Long
  Dim myBorders As Variant

  myBorders = Array(xlEdgeLeft, _
      xlEdgeTop, _
      xlEdgeBottom, _
      xlEdgeRight, _
      xlInsideVertical, _
      xlInsideHorizontal)

  'number of option buttons per row
  maxBtns = 6
  
  'prompt - number of questions
  NumberOfQuestions = _
    InputBox("How many questions " _
      & "in the survey?", _
      "Questions", 10)

  Set wks = ActiveSheet
  With wks
    Set FirstOptBtnCell = .Range("E2")
    .Range("A:D").Clear
    With FirstOptBtnCell.Offset(-1, -1) _
      .Resize(1, maxBtns + 1)
        .Value = Array("Question#", _
          "Resp0", "Resp1", _
          "Resp2", "Resp3", _
          "Resp4", "N/A")
        .Orientation = 90
        .HorizontalAlignment = xlCenter
    End With

    Set myRange = FirstOptBtnCell _
      .Resize(NumberOfQuestions, 1)

    With myRange.Offset(0, -1)
        .Formula = "=ROW()-" _
          & myRange.Row - 1
        .Value = .Value
    End With

    myRange.Offset(0, -3).Value = 1

    With myRange.Offset(0, -4)
      'formula to look up in score list
      .FormulaR1C1 = _
        "=IF(RC[2]="""",""""," _
        & "IF(RC[2]=6,""N/A""," _
        & "RC[1]*INDEX(Admin!R4C3:R9C3," _
        & "MATCH(RC[2],Admin!R4C2:R9C2,0))))"
    End With

    .Range("A1").Formula = _
      "=SUM(A2:A" & _
        NumberOfQuestions + 1 & ")"

    With myRange.Offset(0, -4) _
        .Resize(, 4)
      For iCtr = LBound(myBorders) _
            To UBound(myBorders)
        With .Borders(myBorders(iCtr))
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
      Next iCtr
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With

    myRange.EntireRow.RowHeight = 28
    myRange.Resize(, maxBtns) _
        .EntireColumn.ColumnWidth = 4

    'clean up existing junk
    .GroupBoxes.Delete
    .OptionButtons.Delete

  End With

  For Each myCell In myRange
    With myCell.Resize(1, maxBtns)

      Set grpBox = wks.GroupBoxes.Add _
          (Top:=.Top, Left:=.Left, _
            Height:=.Height, _
            Width:=.Width)
      With grpBox
        .Caption = ""
.Visible = True 'False
.Placement = xlMoveAndSize End With End With For iCtr = 0 To maxBtns - 1 With myCell.Offset(0, iCtr) Set optBtn = wks.OptionButtons.Add _ (Top:=.Top, Left:=.Left, _ Height:=.Height, _ Width:=.Width) optBtn.Caption = "" If iCtr = 0 Then With myCell.Offset(0, -2) optBtn.LinkedCell _ = .Address(external:=True) End With End If End With Next iCtr Next myCell End Sub

Edit Survey Setup Macro Code

After you copy the code into your workbook, you can make changes to the code, if needed.

Group Box Borders

Group boxes have a thin black border, and there's no way to change the line colour or thickness. However, you can change the group box Visible setting to False, so the borders won't show on the worksheet.

In the code, change this line (where True is the setting, and False is commented out):

 .Visible = True 'False

to this, where False is the setting:

 .Visible = False

Hide Existing Group Boxes

If you've already created the survey form, with the Group Boxes visible, you can add the following code to your workbook.

Then, with the Survey sheet active, run the code to hide the Group Boxes.

Sub HideGroupBoxes()
Dim myGB As GroupBox
Dim ws As Worksheet
Set ws = ActiveSheet

For Each myGB In ws.GroupBoxes
  myGB.Visible = False
Next myGB

End Sub

Number of Responses

In the code, you can change the number of Responses (maxBtns), and the default number of questions

edit the macro code

NOTE: If you change the maxBtns number, add or remove response headings, in the following line of code. The count of headings here, must match the maxBtns number.

survey form with option buttons

Manual Setup

Instead of using a macro to create a survey form on a Microsoft Excel worksheet, you could create a template manually, adding and aligning the option buttons.

Watch this video to see how to set up groups of options buttons on an Excel spreadsheet.

Tip: For written steps on setting up groups of option buttons, go to this article on my Contextures Blog: Select Answers With Option Buttons

Get the Excel Survey Template

Click here to download the zipped sample survey template. The zipped file is in xlsm format, and contains macros.

More Tutorials

Select Answers With Option Buttons

Calculate Survey Scores with Option Buttons

Show Survey Responses in Pivot Chart

Data Entry Worksheet Form

Print Selected Items in Order Form

Data Entry and Update Form

 

 

More Tutorials

Excel Option Buttons

About Debra

 

Last updated: December 29, 2023 4:31 PM