An Intense Tutorial on Building Surveys and Quizzes

Building A Quiz, Survey or Questionnaire … Step by Step

Building Quizzes, Surveys and Questionnaires either to test knowledge on any topic or collect answers… is extremely common and useful.

In a previous tutorial, I shared an Excel Quiz used to test the Excel level for candidates applying for a Job. It consists of 40 Multiple choice questions. The Quiz can be marked with a click and reveal the score and rating of the candidate.

You can download that file and take the quiz at your convenience, by clicking on the link here below:

A picture containing ball, player, holding, sitting Description automatically generated

In this intense tutorial, I show you how to create this quiz or any similar project from ground up in Excel.

You can download the start File and follow along by clicking on the link here below:

A picture containing drawing Description automatically generated

We’ll be combining lots of functionalities in this project, such as:

Data Validation, Functions, Conditional Formatting, Charts, Macros, protection and much more… it’s a Power-Packed recipe full of Excel Vitamins and Minerals

So let’s dive in

Here are the Steps and the VBA Codes used

I got the questions and answers from this website:

Entering Quiz Questions, 4 options and Answers ►Hide Column L

Create a Drop List in D2 ►Copy down to D41

Create a Function in Column F =IF(F2=””,””,IF(D2=L2,1,0))

Create Conditional Formatting in D2 : D41 and test it:

=F2=”” ►No Fill Color

=AND(F2=0,$XFD$1=1) ►Red Fill Color

=AND(F2=1,$XFD$1=1) ►Green Fill Color


X1 ► Sum(F2:F41)

Y1 ► ??/40

Z1 ► =”Your Score Is “& CHAR(10) &Y1

X2 ►=”You Are A(n) “& CHAR(10) &VLOOKUP(X1,X4:Y8,2,1) & ” of Excel”

Y10 ► =COUNTIF($F$2:$F$41,1)

Y11 ► =COUNTIF($F$2:$F$41,0)

Creating a Text Boxes:
Text Box 1 ► Name it
Reads: You Have 40 Questions To Answer in 30 Minutes
Select The Best Answer From The Drop List in Column D

Format in Dark Blue ► Size & Properties

Text Box 2 ► Name it
Reads: =$Z$1

Format in light blue ► Size & Properties

Text Box 3 ► Name it
Reads: =$X$2

Format in light blue ► Size & Properties

Chart 1 ► Name it
Source: X10:Y11

No Title, No Fill, Top Legend, resize Plot Area, Properties

Align and Position Text Box 2, Text Box 3 and Chart 1

Hide Columns F to L & X to Z

Insert 3 Pictures ► Set their Size & Properties

Automation 2 – 3 codes

Code one

Sub StartTest()

Range(“D2:D41”).Interior.Color = xlNone



Sheets(1).Shapes(“Chart1”).Visible = FALSE

Sheets(1).Shapes(“TextBox2”).Visible = FALSE

Sheets(1).Shapes(“TextBox3”).Visible = FALSE

Sheets(1).Shapes(“TextBox1”).Visible = TRUE


End Sub

Code Two

Sub SeeResults()

Range(“D2:D41”).Interior.Color = xlNone

If Range(“XFD1”) = 1 Then

Exit Sub

End If

Range(“XFD1”).Value = 1

Columns(“XFD”).Hidden = True

Sheets(1).Shapes(“Chart1”).Visible = TRUE

Sheets(1).Shapes(“TextBox2”).Visible = TRUE

Sheets(1).Shapes(“TextBox3”).Visible = TRUE

Sheets(1).Shapes(“TextBox1”).Visible = FALSE

End Sub

Code Three (Optional)

Sub AnswerExam()

Dim AnswerRange As Range

Dim QST As Range

Set AnswerRange = Range(“D2:D41”)

For Each QST In AnswerRange

QST.Value = QST.Offset(0, 8).Value


Range(“D2:D41”).Interior.Color = RGB(146, 208, 80)

End Sub

Protection and Testing

Unlock the Range D2:D41 and XFD1

A screenshot of a cell phone Description automatically generated


Protect Worksheet

Protect Workbook

Protect VBA Code ► Must save and reopen.

You can watch the video Tutorial on my YouTube Channel


Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

SUBSCRIBE To Our Emailing List And Receive 3 FREE Gifts...Now

Receive 3 Instant Free Gifts  When Subscribing to our newsletter here below
Two Books( Excel Pivot Tables - Dynamic Charts) and The Magical Shortcuts Quick Reference Card Showing 78  easy to remember Shortcuts.