How to make a crossword puzzle in Excel step by step

Almost everyone enjoys doing crossword puzzles. Therefore, they can be useful in various areas of life. For example, in online business. The user can be brought to the site by being interested in such a mini-game. Crossword puzzles are also useful in teaching, because they can be used to consolidate or test the acquired knowledge.

For example, they are used in modern English courses, where a definition is given, and you need to write the corresponding word in a certain line.

And with the help of Excel, you can automate the completion of crossword puzzles. As an option, show the correct answers and check the student by giving him a grade.

How to draw a crossword puzzle in Excel

To draw a crossword puzzle in Excel, you need to press the Ctrl + A combination (you can select everything with it), and then open the context menu by right-clicking. Then you should left-click on the line “Line height” and set it at level 18.

To define the column width, left-click on the right edge of the cell and drag it to the right.

Why do this? The reason lies in the fact that the cells in Excel are initially rectangular, not square, while for our task we need to make the height and width the same. Therefore, it is necessary to make the cells that are allocated for this game the appropriate form.

Then you need to select those cells that will be allocated for rows. After that, we are looking for the “Font” group, where we set all the borders. You can also color the cell in a certain way, if you wish.

On the right side of the sheet, you need to make long lines where questions will be written to it. Do not forget to put numbers next to the corresponding lines corresponding to the question numbers.

Crossword programming

To teach the crossword puzzle to determine which answers are correct and to rate the user, you need to create an additional sheet listing the correct answers. 

This screenshot shows that there are three main columns:

1. Answers. The correct answers are listed here.
2. Introduced. The answers entered by the user are automatically recorded here.
3. Question mark. This indicates a score of 1 if the person answered correctly and 0 if incorrect.

Also in cell V8 will be the final score. 

Next, use the functionStsepit” to glue individual letters in a crossword puzzle. This is necessary for the appearance of a whole word in this line. You need to enter the formula in the cell of the “Introduced” column.

The main problem is that a person can write both large and small letters. Because of this, the program may think that the answer is wrong, although it is correct. To solve this problem, you need to use the function LOWER, into which the function is introduced STsEPIT, as shown in this line of code.

=СТРОЧН(СЦЕПИТЬ(Лист1!I6;Лист1!J6;Лист1!K6;Лист1!L6;Лист1!M6;Лист1!N6;Лист1!O6;Лист1!P6))

This function converts all letters to the same form (that is, turns them into lowercase).

Next, you need to program the condition. If the answer is correct, the result should be one, and if it is incorrect, it should be 0. For this, the built-in Excel function is used IF, entered in the cell of the column “?”.

To display the final grade in cell V8, you need to use the function SUM.

In our example, there are a maximum of 5 correct answers. The idea is this: if this formula returns the number 5, then the inscription “Well done” will appear. With a lower score – “Think again.”

To do this, again you need to use the function IFentered in the “Total” cell.

=IF(Sheet2!V8=5;”Well done!”;”Just think about it…”)

You can also add the ability to show the number of issues that need to be resolved to the functionality. Since the maximum number of questions in our example is 5, you need to write the following formula in a separate line:

=5-‘List1 (2)’!V8, where ‘List1 (2)’!V8

To make sure that there are no errors in the formulas, you need to enter the answer in some line of the crossword puzzle. We indicate the answer “drive” in line 1. As a result, we get the following.

It is necessary to make sure that the player is not aware of which answer is correct. They need to be removed from the crossword grid on the auxiliary sheet, but left in the file. To do this, open the “Data” tab and find the “Structure” group. There will be a “Group” tool, which should be used.

A dialog will open, where a checkbox is placed next to the “Strings” entry. Outline icons with a minus sign will pop up on the left.

If you click on it, the data will be hidden. But an advanced Excel user can easily open the correct answers. To do this, they need to be password protected.

You need to find the “Review” tab, where to find the “Changes” group. There will be a “Protect Sheet” button. It needs to be pressed. Next, a dialog box will appear in which you need to write down the password. Everything, now a third-party person who does not know him will not be able to find the correct answer. If he tries to do this, Excel will warn him that the worksheet is protected and the command is not allowed.

That’s it, the crossword is ready. It can then be styled using standard Excel methods.

How to make an effective educational crossword puzzle?

A crossword puzzle is an effective method that allows you to increase the degree of independence of students in the learning process, as well as increase motivation for this process. In addition, it allows a good understanding of the terms of the subject being studied.

To create an effective crossword puzzle for learning, you must follow these recommendations:

1. You must not allow the presence of empty cells inside the crossword puzzle.
2. All intersections must be thought out in advance.
3. Words that are not nouns in the nominative case cannot be used as answers.
4. Answers must be formulated in the singular.
5. If the words consist of two letters, then two intersections are required. In general, it is desirable to minimize the frequency of two-letter words. 
6. Do not use short words (orphanage) or abbreviations (ZiL).

How can you use the crossword in Excel while learning?

The use of modern technologies during training can not only help students become more actively involved in the process, study the subject, but also improve their computer literacy. Recently, a very popular direction in education is STEM, which provides for the integration of science, technology, engineering and mathematics in one course.

How might this look in practice? For example, some subject is studied, for example, astronomy (science). Students learn new terminology, which they then repeat using an Excel (technology) crossword puzzle. Here you can then tell the students how to create such a crossword puzzle. Then try to create a telescope using mathematical formulas.

In general, terminology is one of the most difficult aspects of studying any discipline. Some of them are very difficult to learn, and the game element creates additional motivation, which contributes to the emergence of new neural connections in the brain. This mechanism in psychology is called positive reinforcement. If the child is interested, he will be more willing to be involved in the material being studied.

The older the child, the more diverse the tasks should be, the terminological apparatus may shift more towards abstract concepts, and the differentiation of tasks in terms of complexity may be more pronounced.

But this is only one of the many methods of using crosswords in teaching. More specifically, it can be used for:

1. Homework for students. Students develop the ability to independently understand the educational material, formulate questions, and develop the creative abilities of students.
2. Work during class. Crossword puzzles are a very convenient method of repeating the material of the last lesson. It allows you to quickly systematize the information received, on the foundation of which new material will be built.

Creating a crossword puzzle in Excel in a lesson or as homework has another important advantage – it makes it much easier to learn certain material. When a student independently comes up with questions for a specific term, neural connections are built in his brain that help him understand the topic and use the knowledge gained in the future.

Stages of compiling an educational crossword puzzle in Excel

1. First you need to decide on the type of crossword puzzle. It is recommended to use non-standard forms. Fortunately, Excel has enough tools to develop any design. Importantly, the words should be located freely from each other.
2. Then you need to write a list of terms and definitions for them. It is advisable to use both simple and compound words.
3. The stage of field design, numbering.
4. Crossword programming (if necessary).

Methods for programming the evaluation of the result

In addition to the method described above (total number of correct answers), weighted scores can also be used. In this case, you need to draw another column, where the weighting coefficients are written next to each question, as well. You also need to add a column with the overall result. In this case, the total cell should be the sum of the weighted scores.

This method of calculating the score is more suitable if there are several fields of different complexity. Naturally, the number of correct answers here will not be an objective indicator.

Each point given in the column “?” it is necessary to multiply by the weighting factor, which is in the next column, and then display the weighted value.

You can make an assessment in the form of an individual rating. Then the percentage of guessed words is used as an estimate.

Pros and Cons of Compiling Crosswords in Excel

The main advantage is that you do not need to master additional programs. However, this method has a number of serious drawbacks. Excel was created for other tasks. Therefore, to compile crossword puzzles in spreadsheets, you will have to perform more unnecessary actions than if you use special programs. Some allow you to do this online, and then share the result with other people.

Creating crossword puzzles in Excel is a laborious and long process. Using other programs can significantly speed up this process. However, only basic spreadsheet skills are enough for him.

Using a crossword puzzle in Excel in business

Entrepreneurial activity requires some ingenuity. For example, you can invite the client to complete a crossword puzzle, and if he succeeds in doing this, give him a prize. In turn, this gift can be a great element of the sales funnel. When he receives it, you can offer him an extended or improved version of a certain product, but already for money.

However, in business, the use of Excel crossword puzzles is not so widespread. The main disadvantage of this approach is that the same crossword puzzle can be implemented using standard HTML and Javascript tools. And using special applications, you can easily create such a tool in a visual editor and you do not need to download a separate document to your computer.

Conclusions

Thus, creating a crossword puzzle in Excel does not require special skills and abilities. You just need to style it in a special way, and also enter a few formulas so that the table automatically checks the correctness of the answers.

It can be used both in business and during the educational process. In the latter case, the space for using crossword puzzles is much larger. They can be used to test the knowledge of students, and to teach computer literacy, and to study the terminological apparatus of a particular discipline.