Home > Templates > Templates > Survey

Excel Survey Templates

Debra Dalgleish - Contextures

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

Excel Checkbox Survey Form

1) Excel Checkbox Survey Form

If you're using Excel 365, this is a quick and easy way to create a simple survey, using the Checkbox feature, which is on the Excel Ribbon's Insert tab.

Excel Checkbox Survey Form

1.1) Video: Checkbox Survey Setup

In this video, I show how to create a quick survey form, using the Checkbox feature.

I also show how to add data validation, so that only one box can be checked for each survey question.

To follow along, you can get the sample file on the Excel Checkbox Survey page. That page has written steps and screen shots too!

2) Excel Survey Template - Option Buttons

For earlier versions of Excel, you can create a survey with option buttons, instead of checkboxes. There are 2 main setup steps, and details are in the sections below.

--2.1) Run Survey Setup Macro

--2.2) Customize Survey Template

--2.3) Use the Survey Template

Thanks to Dave Peterson, who shared this technique.

selection values in column C

2.1) Run Survey Setup Macro

First, you'll run the survey 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.

  • Note: The macro is only used for the survey setup.
    • No macros are needed to fill in the survey.

basic survey form with option buttons

2.1.1) How to 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.

2.2) Customize Survey 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

2.2.1) How to 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

2.3) How to 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.

3) Manual Setup for Survey Form

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

4) Tech Notes: Survey - Option Buttons

This section has technical details for the survey with option buttons.

--4.1) Survey Score Formulas

--4.2) Survey Setup Macro Code

--4.3) Edit the Macro Code

4.1) 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))))

4.2) 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

4.3) Edit Survey Setup Macro Code

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

4.3.1) 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, scroll down almost to the end, and look for the following lines of code.

      With grpBox
        .Caption = ""
        .Visible = True 'False
        .Placement = xlMoveAndSize
      End With

In the line that starts with .Visible, change the True to False, so the group box border doesn't show.

      With grpBox
        .Caption = ""
        .Visible = False
        .Placement = xlMoveAndSize
      End With

4.3.2) 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

4.3.3) Number of Responses

In the survey setup code, you can also change:

  • the number of Responses (maxBtns) - **see note below
  • and the default number of questions

edit the macro code

**NOTE on maxBtns:

  • If you change the maxBtns number, add or remove response headings, in the "Array("Question#" line of code, as shown below.
  • The number of headings listed in the array, must match the maxBtns number.

survey form with option buttons

Get the Excel Files ✅

Get the Excel files for the survey templates shown on this page. There are more sample files in the next section too.

FN0078

Excel Checkbox Survey -- Simple survey with 3 questions, rate from 1 to 5, using checkboxes. Data validation prevents multiple checks per question. The zipped file is in xlsx format, and there are no macros. Version: Excel 365
Written Steps: Excel Checkbox Survey page
Excel File: excelcheckboxsurvey.zip

survey option buttons

Option Button Survey -- Get the Option Button Survey template file, which works in earlier versions of Excel. The zipped file is in xlsm format, and contains macros.
Excel File: Excel Survey - Option Buttons.

More Excel Survey Sample Files

FN0057

Calculate Survey Scores from Letters
Select letters (A, B, C, D) to answer survey questions. Formulas calculate total survey score, and show description based on total
Format: xlsx  Macros: No 
Excel File: sumproductcodevalues.zip
Instructions: In the workbook

GAME

Which Excel Function Are You? -- Answer survey questions with option buttons, to find out which Excel function you are.
Excel File:
whichexcelfunction.zip

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

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

 

Last updated: March 2, 2025 12:21 PM