Excel

Modified for Excel 2007 from

“Create Self-Correcting Worksheets”

By Lorrie Jackson

http://www.education-world.com/a_tech/techtorial/techtorial104.pdf

What will I learn today?

You will learn how to use if/then statements in Excel to create self-correcting worksheets.

What’s a self-correcting worksheet?

Let's say your kindergarten class is struggling with simple addition. You'd like to use the computer to help review addition, but your Internet connection is spotty or nonexistent, or you're worried that students will start exploring other sites. You need something fun, simple, and offline! Why not create a simple Excel worksheet that students complete on the computer? If they type the right answer, they get a happy face. If they type the wrong one, they get a sad face.

Creating such worksheets is easier than you might think! Using if/then statements, we'll create a simple worksheet you can use in your K-2 (or 3-12 math, science, or foreign language) classroom.

1. Open Microsoft Excel.

2. In cell A1, type the word "Question." In cell B1, type "Answer." In cell C1, type "Correct?"

For this example, we'll use simple addition equations. If you prefer to pick another topic, stick with numbers or words that are rarely misspelled. Anything that's not perfect (a misspelling, an extra blank) will make the answer wrong in Excel. Also, because the logic can be a bit tricky, try to stick with simple math the first time you complete this techtorial.

Type the following:

1. In cell A2, type "1+1=?"

2. In A3, type "2+2=?"

3. In A4, type "3=3=?"

Save your work.

If/Then Statements

Whether or not you’ve taken logic/critical thinking, if/then statements are easy to understand. "If it is raining, then we will not go outside for recess" is an example. If one thing happens, then another thing must happen. In Microsoft Excel, we can use if/then statements to check an answer and to have a special icon appear if the answer is right or wrong. For example, if the answer to "2+2" is 4, we tell Excel that if a student types a "4," then a happy face should appear. If a student types anything else, a sad face should appear.

So, let's type an if/then statement for our worksheet.

1. Click in cell C2, and type =IF(B2=2,"J","L"). You must type every symbol and space correctly or you will get an error message. Don't space after the commas as you normally would.

2. Hit Enter or Return. You now should have an L in cell C2. Why does our statement look like that?

a. =IF - tells Excel it's an if/then statement.

b. B2=2 - tells Excel that the condition we're testing is that this cell is equal to the number 2. (The number 2 has been typed in cell C2).

c. "J" tells Excel what to put in the cell if the condition is true. (If a student typed a 2 in cell C2).

d. "L" tells Excel what to put in the cell if the condition is false. (If a student typed something other than a 2 in cell C2).

Type a 2 in cell B2 to see the letter J pop up instead. Why on earth should you type a "J" if the answer's correct and an "L" if the answer's wrong? It's happy face time. Save your work and read on!

Happy/Sad Face

"J" and "L"? What on earth?! We need Excel to show a happy face when the answer is right and a sad face when the answer is wrong. Although a happy face is on the Drawing toolbar, that won't work for our purposes. Instead, click in cell D2 and type a capital J. Highlight the J, and then select Wingdings as your font. Tada! You've got a happy face (Wingdings is a font of commonly used symbols). Delete the contents of cell D2.

Before moving on, let's correct the Correct column.

1. Highlight cells C2, C3, and C4.

2. Select Wingdings font.

3. Change the size to 22.

4. Change the color to green -- or any other color you want that shows up. Yellow is not a good choice. And remember, just the outline of the happy/sad face will appear. The face will not be filled in with a color.

If you have a 2 in cell B2, you should now see a happy face in cell C2. If you have anything else in cell B2, you should see a sad face.

Finishing Your Worksheet

Almost done!

1. Highlight cell C2, and change the font back to a text-based font. (Why? We need to use that formula for other cells and, without changing the font, the Wingdings symbols will make it hard to copy.) You should see a J or L in cell C2 again.

2. Highlight cells C2, C3, and C4. With the Home tab selected on the ribbon, click Fill, then Down in the Editing ribbon group.

3. You now should see Ls in cells C3 and C4. Click in cell C3, and you'll see that your if/then statement has been copied and pasted there, except now the statement uses B3 (the answer to 2+2) as the condition it's testing. But our if/then statement still has "2" as the answer. We need a "4" instead, because the equation for cell A3 is 2+2=? So...

a. Find the Formula Bar at the top of the screen.

b. Click in the blank just behind the 2, and change the 2 to a 4.

c. Hit Enter or Return.

d. Type a 4 in cell B3, and you'll see a J appear in cell C3.

e. Click in cell C4, go to the formula bar and change the 2 to a 6 in the if/then statement (because A4 asks what the sum of three plus three is).

f. Highlight cells C2 through C4, and change them back to Wingdings font.

4. Experiment with both correct and incorrect answers in cells B2, B3, and B4 and watch the happy and sad faces change as your answers change!

5. Save your work.

6. Click File > Save As and select Template as the file type to create a version of the worksheet that students can practice on, but not permanently alter.

Add a Title

To add a title on your worksheet (for printing purposes):

1. Click Insert > Header and Footer

2. Type your name, date, and the worksheet title.

3. Click View>Normal to return to the regular worksheet view.

4. Save your work.

The above technique might seem complicated at first, but consider that, after you've made one worksheet, you can go back and change the correct answers and the questions in column A and be done in seconds. Use the worksheet you just created as the starting point for many more worksheets.