|
Home > Templates > Templates > Survey Excel Survey Templates
|

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.

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!
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.

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.

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

The macro creates a basic survey template, with numbered questions and option buttons.
Next, you can customize the basic template. For example, in the screen shot below, I made the following changes:

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


To use the survey, follow these steps:

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
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
The following formulas are in the survey template, to calculate the scores, based on the response selected for each question.
This formula returns a sum of all the Question scores in column A
=SUM(A2:A11)
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))))
1) First, the two IF functions check the value in column C, twice.
=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))))
The SetupSurveyTemplate macro code is shown below.
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
After you copy the code into your workbook, you can make changes to the code, if needed.
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
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
In the survey setup code, you can also change:

**NOTE on maxBtns:
Get the Excel files for the survey templates shown on this page. There are more sample files in the next section too.
![]() |
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 |
![]() |
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. |
![]() |
Calculate Survey Scores from Letters |
| GAME | Which Excel Function Are You? -- Answer survey questions with option buttons, to find out which Excel function you are. |
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!
Select Answers With Option Buttons
Calculate Survey Scores with Option Buttons
Show Survey Responses in Pivot Chart
Print Selected Items in Order Form
Last updated: March 2, 2025 12:21 PM