In this Instructable, I will be showing you how to make an excel crossword puzzle. This is very easy and simple to make and In the end, I'll show you how to check if the answers are correct

Supplies

Microsoft Excel

Pen and Paper

Step 1: Clean Slate

Right now you should have a brand new excel sheet. Before doing anything to the excel sheet, I suggest that you take a pen and paper and draw a rough grid for the crossword. If you are confident you don't need to draw the grid then okay.

Step 2: Formatting

It will better if we first start by formatting the cells. Select a bunch of cells and click on the Format button in the home section. There, set the row height to be about 25 and the row width to be around 6 this way you get the cells to be almost square.

Step 3: Entering Your Crossword

Now, turn on caps lock and enter the crossword, One letter per cell. When you are done with entering the crossword choose any colour of your choice and highlight the text with it. After that consider putting a border for the Text by selecting the borders option.

Step 4: Questions

Now that the crossword is ready let's start typing the questions. Make the headings bold and maybe increase the font size. put the questions with numbers near it. after you are done making the question section and assigning each question a number we can start assigning the same numbers to the same words in the crossword. Just put in the number near the word bold it and add a border while you're at it.

Step 5: New Sheet

From this point onwards we are gonna make use of excel formulas and create an automatic checking system. The first thing you need to do for that is to add a sheet. in the bottom of the window, you will see a small + sign in a circle. Click on that, this means you are creating a new excel sheet. now in the new excel sheet create a small table similar to the one I have made, in the first column add numbers. In the second column type in all the answers near the correct number. and another heading called Given. leave that column empty for now. Answers entered in the crossword will be shown in that column with the magic of excel formulas.

Step 6: Excel Formulas

In this step, we will check if the answers are correct. in the last step, we made a column called given. under that in each cell, we will enter a formula. Now let us try to understand what the formula means, In excel you tell the computer that whatever you are entering is a formula by putting in a = sign as the first character, The first word in the formula is LOWER, This basically converts whatever word you enter to lowercase. If the user enters the letters in capital there may be a problem when checking so, we convert all of them into lowercase. The next word you will see is CONCATENATE. if you remember the letters in the crossword is one letter in one cell but while checking we need all those letters to be a single word. This command puts all the letters into one full word. Inside the brackets Sheet1!K12, The first part is Sheet1!, this specifies where the letter is and the next part is K12, this tells me which cell the letter is in. specify cells in this format and when you hit enter the word will magically appear. Do the same for the rest of the word and you should have something similar to the picture I have.

The Formula (The cells I entered may not be the same for you):

=LOWER(CONCATENATE(Sheet1!K12,Sheet1!K13,Sheet1!K14,Sheet1!K15,Sheet1!K16))

Step 7: Answer Checking

Now we will check if the answer given is correct. for that, we will be using another formula. As usual, we will start off with an = sign. The first word is IF, with this command, we will see if both the cells are the same. Inside the bracket I have said B2=C2, here we ask if cell B2 = C2. then we see the number 1, here we say if the condition is true print 1 in the cell. After that, I have entered a 0 to say if the condition is not true then print 0 in the cell. do the same for all the other cells. now if the cell is 0 then the answer is wrong if the cell is 1 then the answer is correct.

Step 8: Status Bar

Select all the values under Status. Under Formulas click on the auto sum button, and you will get all the values added up! Now go to Sheet1 over there pick a cell where you can tell the user how many words he has left to find you can do that by putting in this formula

=6-Sheet2!D9

here as you may have already seen Sheet2! tells us which sheet the cell is in and 6 is the total number of words. D9 is the cell for this, you should select the cell where all the values are added up. Hit enter and see the value appear. Merge a couple of cells near that and type in this formula:

=IF(C2=0,"You are done","left")

Here we are basically saying if the number of words left is equal to 0 then say "You are done" else say "left".

Step 9: Locking

We are almost done! Just go to the sheet where you have all the answers typed in and select the whole table, under data select group and press ok. Now you should see a - sign, if you click on it the whole table will get minimized. Now under review click on the Lock sheet button while the table is minimized and enter in a password. That's it your sheet is locked now nobody can cheat by looking at the answer sheets.