Create a quiz in Excel using conditional formatting and data validation

This is a video tutorial I created for some of my students to help guide them through creating an interactive quiz in Microsoft Excel, learning about data validation and conditional formatting.

[@paramananthensivakolunthu2876]

Excellant explanation. Thank You Sir.

[@clairewalton2857]

if having trouble with letter answers - use quotations in the formula. Ex) If your correct answer is A, quote "A" in the formula. Hope this helps!!

[@marangelyhernandez994]

Hi, I followed tour instructions but in my true or false exercise, even when my answer box is in blank, un my Sporting box appears 1. What do I need to do to change this?

[@cornucopia7329]

Can an image get displayed when perfect score 3/3 is reached ?

[@truckerdaddy-akajohninqueb4793]

Here's my favorite piece of excel code. Good for vocabulary or spelling quizzes. 1 point for survey 0.8 for survay 0.5 servay The ESL student knew the correct word but had difficulty spelling it. =IFS(SEARCH("survey",F2),"1",SEARCH("survay",F2),".8",SEARCH("servay",F2),".5",TRUE,"0") The students entered their answers using Google forms. I emailed them the link to the form. They answered using their cellphones. I turned off the form after everyone present said they were finished. Google forms exported everything to a spreadsheet.

[@godididiya9091]

This is a nice tutorial but the coloring part was not very clear. my macbook had some different feautures and i couldnt figure out how to get the colors done

[@TheTechTrain]

I'm afraid I don't use Macs so am not very familiar with how far they are able to support the features I use in the Windows version of MS Office.

[@darkprince8895]

Is there a way to show if your answer is wrong there will be a table where it shows the correct answers?

[@SimpleSubash]

Can you get your answer? I also need this😥

[@CommonSenseMan1]

I am trying to create a test for myself for Latin revision. I want a table where if I type the correct answer in a cell it will colour the cell green, say, and red for incorrect. How would I do this?

[@TheTechTrain]

You'd simply need a basic conditional formatting rule set up, much like the one in the video. You'd actually need three rules, one to check whether the cell contains the right answer, one for the wrong answer, and one to check whether no answer has been given. I cover most of the basics in this video but if you need more help please do say.

[@CommonSenseMan1]

The Tech Train Thanks. I’ll give it a try and get back to you if I need more help.

[@CommonSenseMan1]

Having trouble. For example: The correct answer is 'femina' (I don't require a drop-down menu as I don't want multiple choice). So if you type 'femina' in cell C29, cell D29 will display 'Correct'. Would the formula in D29 need to be: =IF(C29=femina,Correct,Incorrect)?

[@TheTechTrain]

Almost - you'd need to put the text in speech marks, like this: =IF(C29="femina","Correct","Incorrect")

[@CommonSenseMan1]

Great, that works, thanks. One more thing: would I be able to add to the formula so that if nothing is typed in the answer cell, nothing shows in the 'Correct/Incorrect' cell?

[@elsifa5381]

Please let me know how to make if the answer not in numbers

[@TheTechTrain]

Do you mean that you want the quiz to have words as answers, rather than numbers? If so then you don't need to do anything differently - this method works for anything that you can write in a cell.

[@marangelyhernandez994]

@@TheTechTrain hi, I'm making a quiz for my students since for the Coronavirus the schools are closed. I followed your steps but in the scoring box when I select false, even if the answer is false, the scoring box stays in blank but if I write nothing, the box is in blank, in the scoring box appears #1) HOW CAN I FIX THIS?

[@juliebechtel59]

That's cool for numbers, but what if the answers are words?

[@alyrios]

you put the answer like so: "hello" where the number would go. I was able to create a quiz like so. 😊

[@marangelyhernandez994]

@@alyrios hi, sorry to bother you but, I typed "false" and the score doesn't appear... when the box is blank appears the point. What am I doing wrong?

[@alyrios]

@@marangelyhernandez994 hmmm, is a bit complicated. Let's say the answer is "hello", and then the points.

[@marangelyhernandez994]

@@alyrios do I need to separate each expression with a parenthesis?

[@alyrios]

@@marangelyhernandez994 This is how a formula looks in one of the tests I did: =IF(C138="llevada",1,0). So that means the student will get one point if they type "llevada".

[@nurulhakima4083]

i already sum the score but it does not show the total. how do i fix this? because i already follow the instructions from your video

[@nurulhakima4083]

at 19.58