Hi, this is Wayne again with a topic “Create Interactive Worksheets in Excel by Using the IF and COUNTIF Functions”.
In this article, i will show you how to create interactive worksheets in microsoft excel and not only are they interactive, but they’re also self grading. You might also think of these as being self grading practice quizzes that the students can take to prepare for a test, and you can see i’ve created the beginnings of a spreadsheet here and you can get a copy of this in the description below the video. But what i have here is a series of icons of different animals, and i got these in the spreadsheet simply by going to insert illustrations, icons and then searching for the different animals. And then i just selected the animal that i wanted. Clicked insert shrunk it down and put it where i wanted it to be, and let’s say i’m a spanish teacher and i would like my students to practice their spanish animal words.
In this example, i’ve chosen to make the questions simply be images or icons, but, of course, if you prefer, you could use text in your own, interactive, worksheets or quizzes. So the way i would like this to work is, i want my students to be able to click here and type in the answers to each question, and then i want excel to basically grade their answer to say whether it’s correct or incorrect. So how would i do this? The answer is, i need to use the if function. Let me demonstrate so i’m going to click on c2 tap equals.
If you can see that excel explains what the if function typically does and then i’ll put in the left parenthesis excel is now looking for a logical test. So if i’m going to click here on b2, so if b2 is equal to and inside of quotes because we’re dealing with text i’m going to type el, also close quote, i put a comma and the comma represents the word then. So if b2 is equal to el also, then again i need to put quotation marks i’ll type, the word right with an exclamation point and then close quote and then, if i want to, i can put another comma. This second comma represents, if not or else so, if b2 is equal to el – also then print the word right, if not or else print.
The word wrong, close quote and right parenthesis. If i tap enter on the keyboard now, you can see that excel evaluates the contents of cell b2 and excel finds that el oso does meet the criteria that i established, and so it prints the word right here in c2. Now, of course, i want my students to put that in so i’m going to click on b2 tap delete to get rid of it and you’ll notice that the result changes to the word wrong. Okay, so now i need to do the same thing with c3 right.
I need to tap equals if left parenthesis. Well, yes, that’s a good idea to do, but let me show you a shortcut there’s a big time saver that i like to do i clicked on c2 and i’m going to go here to the lower right corner, that little green square is the fill, handle or Autofill handle i’ll put my mouse on it until the mouse pointer turns into a black plus sign and then i’ll just click and drag and pull that down. And you can see what happened. The autofill handle copied the contents of cell c2 and copied it exactly into cell c3.
Sometimes the autofill handle continues a pattern or extends a pattern, but in this case it’s simply copy pasted. If you want to learn more about the autofill handle. Please watch my other video on that topic. Now i said that it copy pasted the contents of cell c2 into c3, c4, c5 etc, and it did, but it is important to notice that excel automatically changed b2 to b3 and to b4.
So it adjusted the row in question. So that will be very helpful. Later now you probably noticed the problem right.
The word for cat in spanish is not el oso el oso means bear so i need to adjust this and change it to say elgato or the cat. I can do that here in cell c3, but in a lot of cases it’s easier, safer and just better to go up here to the formula bar. Yes, i still have to click on c3, but then i go up here to the formula bar and i can change it from el oso to elgato tap enter.
It still says it’s wrong, but now, if i put in the correct answer here in cell b3, it changes to right. So i need to do the same thing with the dog and the elephant. Give me a minute to change the rest of these formulas in these cells and then i’ll resume the video okay, i’ve finished updating the formulas so now, as the correct answers are typed in the text, changes to say right now at this point, if i want to Spice this up a little bit, i could select all of column c and i could use some conditional formatting to provide additional feedback to the student. So here on the home tab home ribbon here in the styles group, you should find conditional formatting. I want to highlight the cells rules based on text. That’S in the cells, so i’m going to go down here to text that contains i’ll type in the word write with an exclamation point and if the student gets the answer right, i don’t want it to turn red.
I would like it to turn green fill with dark green text. I click, ok and that conditional formatting has been applied to the entire column. Next, if i want to, i can click on c again.
Do another conditional, formatting highlight cells, rule again, it’ll be text that contains, but this time i’m going to type the word wrong and i want it to be formatted light red fill with dark red text. Now, of course, you can adjust all of this, and if you want to learn more about conditional formatting, i have a couple of videos on that topic that you really should watch, but i’m going to just leave it red with dark red click. Ok and that’s a nice extra way to provide feedback to the students as they answer the questions either correctly or incorrectly, the color really does help to provide some feedback. Next, i would like to add one finishing touch, and that is, i want excel to total up. The right answers and give the students a total score. I can do that here in cell c9 and to make this work i’m going to use the countif function, so i’ll tap equals type in countif. Countif counts the number of cells within a range that meet the given condition. So countif left parenthesis. I’M going to select the range that includes all of the feedback right or wrong.
So that’s my range c2 through c8. Then i put in a comma and you can see what excel is expecting here. It’S looking for my criteria, so what is it that excel is looking for to decide whether or not to count that cell? Because i’m dealing with text? I need to put this.
In quotation marks, i want excel to look for the word write with an exclamation point i put in my close quote and right parenthesis tap enter on the keyboard and according to excel. I got six out of seven questions right. If you want to take this one step further, you could make it so that the students can type in variations on the correct answer and still get the question right.
So, for example, with the word el tigre, what if the students just wrote, tigre technically that could be considered correct without the word l in front of it and yet excel marks it as wrong. How could we fix that? Well, i’ll click on cell c8 – and this is an advanced technique – don’t feel bad if you’re not ready to try this yourself, but with cell c8 selected i’m going to go up here to the formula bar and right in front of b8, i’m going to type. The word or and then put in a left parenthesis and then i’ll leave b8 equals lt, grey and then i’ll put in a comma and then b8 equals in quotes i’ll just put tigre without the word l in front of it close quote now. I need to put in a right parenthesis: you’ll notice that i have two sets of parentheses, one that begins here and ends here and one that begins here and ends here now i can tap enter on the keyboard and look excel, considers the word tigre to be Correct and also el tigre, but not el tigro, since that’s not a word. So if you’d like to do that, you can use formulas that are a little bit more complicated using this or function to create multiple possible, correct answers for each question. Thanks for watching this video, i hope you found it to be helpful if you’d like to learn more about the if function.
Please watch my previous tutorial on using the if function and if you found this video to be helpful, please like follow and subscribe and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account, or by buying channel merch and you’ll, see links to those opportunities in the description below the video .