Word Jumbles with Google Sheets and Docs
Introduction
Vocabulary is such an important part of the development of language. It is important for the development of comprehension in reading. It facilitates language development. Increases communication skills. Facilitates the communication of ideas. Increases writing skills. I have a couple of links below with information on the importance of vocabulary development.
https://www.scholastic.com/teachers/articles/teaching-content/understanding-vocabulary/
https://infercabulary.com/top-5-reasons-why-vocabulary-matters/
Learning vocabulary doesn't have to be the tedious process of memorizing the spelling and definition of words. Vocabulary games like crosswords, word searches, and word jumbles provide fun ways for students to apply vocabulary skills. With these tools, students are not asked to memorize. They are applying the use of vocabulary in fun ways.
I created a set of instructions for using Google Sheets and Docs to create word search puzzles. The link is available below.
https://digitalmaestro.org/articles/word-search-puzzles-with-google-docs
In this lesson, I want to show you how to create word jumble puzzles. This lesson builds on the skills from the word search lesson. I will review the basics here for you.
A link to the final product is available below.
Click the Use Template button to get a copy.Google Docs word jumble preview and copy
This lesson is available in a printable PDF version.
Preparation
Use the link above to get a copy of the vocabulary Sheet. The sheet has three columns of vocabulary words. Teachers like to present the vocabulary in one of two ways. Some teachers like to use the words will all uppercase letters. Other teachers prefer all lowercase letters. This first step demonstrates how to covert the case of your words using Google Sheets functions.
The Google Sheet has a list of vocabulary words. This word search reviews mammals covered in the lesson. In the sheet, I have the same list repeated three times. One column has the names with the first letter capitalized. The second has the letter in all upper case. The last has them all in lower case.
You can format the word search using all uppercase or all lowercase letters. The choice is yours. I want to show you how to format the words without having to retype them.
Google Sheets has plenty of useful formatting tools. I begin with Sheets when I have to deal with complex products.
Lowercase
Each word begins with a capital letter in the first column. I want all the letters to be lowercase.
Click on cell B2 and type =LOWER(A1). Press the Return key to apply the formula. This converts all the letters in the word to lower case.
Select cell B1 again. Click the blue square in the lower right corner and drag it down.
This copies the formula down the column. Stop when you reach the end of the word list.
All the letters in each word are now lowercase.
Uppercase
The next column has words that are all lowercase.
Click in cell E1 and type =UPPER(D1). Press the Return key.
Return to cell E1. Click the blue square and drag it down the column.
The letters in each word are transformed to uppercase.
Proper Case
Converting letters to upper or lower case it not all we can do. There is a function for converting the first letter in each word to upper case. It also changes the letters after the first letter to lowercase.
Click in cell H1 and type =PROPER(G1). Copy the function down the column.
Selecting an option
We have three options for the word search lettering. We only need one. The other options need to be removed. I am using the uppercase option.
This is how to remove the unwanted word list. Click on the column header with the word list to be removed.
Click Edit and select Delete column. The column you are deleting is identified by the column letter.
The column that used the formula to convert the letters is filled with error messages. Click the column header and delete the column.
Keep deleting columns with the word lists you don’t want to use. Your list needs to be in the first or second column. You cannot have any content to the right of the column with your words.
Segment the letters
The letters for each word need to be in separate cells. Again, we are using Google Sheets to help with this process. Sheets has a split function. We are using this function in combination with Regular Expressions. Regular expressions are a type of code used to manipulate text. It is used very often by programmers.
Click in the cell to the right of the first word. Type or paste the formula below. Replace the B1 with A1 if your words are in column A.
=SPLIT(REGEXREPLACE("" & B1,"(w)", "$1,"), ",")
The regular expression finds each letter in the word. It adds a comma after each letter. The Split formula uses the comma to split each letter and place it on a different column.
Click back on cell C1. Use the blue square to copy the formula down the column.
Random letters
Jumbled words need jumbled letters. Google Sheets has a tool to let us jumble the letters. The tool only works with words or letters listed in a column. We need to transpose the letters from rows to columns.
Create a new sheet. Click the Plus button.
Rename the sheet Jumbles.
Return to the Words sheet. Select the columns with the word and the letters of the word. Make sure you select all the letters.
Go back to the Jumbles sheet. Click on cell A10.
Click Edit and go to the Paste Special option. Select the option to paste the values only.
The contents are pasted and selected. Make sure the contents remain selected for the next step. Copy the pasted contents again.
Select cell A1. Click Edit and go to the Paste Special option. Select the option to paste transposed.
The contents are pasted so the letters go down the column.
Select the letters in the column for squirrel.
Go to the menu and click Data. Select the Randomize range option.
The letters are rearranged randomly.
We need to repeat this process for all the words. The process is easy. It can get tedious if you have lots of words. I like to use a special tool in Google Sheets to perform routine tedious tasks.
We are going to create a Macro to handle the tedious repetitive task for us. A macro records a set of steps. It then replays those steps whenever we need them.
Click Tools in the menu. Go to the Macros option. Select the option to record a macro.
A macro recording box opens. This is not recording your screen. It is recording the selections, mouse clicks, and keystrokes. It is only recording actions taken on the Google Sheet.
Select the option to “use relative references”. This means we will be able to use the macro on any selected cells in the sheet.
Select the letter for dog. Select all the rows down to row 13. The word dog is one of the shortest words we have on the list. We want this macro to work on longer words. The longest word length goes to row 13. The letters pushed the words down from the 10th row. Click Data in the menu and select Randomize range.
Those are the only tasks we want to record. Click the Save button to stop recording.
Set the name of the macro to random letters.
There is an option to create a shortcut key combination. The combination begins with three keys. They include Command, Option, and Shift on Mac. These keys on Windows or Chromebook are Alt, Option, and Shift.
There is a blank for a number of your choosing. I like using shortcuts. They help save lots of time. I’m entering the number 0 into the number field.
Click the Save button.
Click on the letter D in dog.
Click Tools in the menu. Go to Macros and selected the random letter macro.
The macro is a program script. The script is going to make changes to the sheet. We need to authorize the script to make changes. Click the Continue button.
Select your account when prompted. Click the Allow button.
Click on the letter D in dog again. Run the macro if the letters didn’t scramble.
Go to the next word and repeat the process. Use the shortcut key combination to go faster. Use the right arrow key to select the beginning of the next word.
Use this process to quickly jumble the letters for each word.
We can scramble the letters again. Click on the first letter of a scrambled list of letters and use the macro. This is helpful when creating more than one jumble exercise.
Range names
Ranges are a selection of cells. The selection of all the cells with letters is a range. Range names help quickly call up these cells with letters. We need to call the letters to form our word jumble puzzles.
Select the letters in the word squirrel.
Click Data and select Named Ranges.
A Named ranges panel opens. Change the named range name to squirrel. Click the Done button. Keep the panel open.
Select the next word. Go to the Named ranges panel. Click the Add a range button.
Set the name of the range to that of the word it represents. Click the Done button. Repeat this process for all the words.
The puzzle
Create a new sheet. Name the sheet puzzle.
Click the and drag along the column headers. This selects the columns.
Hover over one of the columns to display a selection arrow.
Click the selector. Choose the Resize columns option. The columns A - M should be shown.
Enter 35 for the column size. Click the OK button.
Click on cell A1. Type =transpose followed by an open parenthesis. Transpose is a function that changes the order of a range of cells. The cells in the word ranges are vertical. We need to convert them to horizontal ranges.
Type the word squirrel. Google Sheets provides recommendations. One of the recommendations is the named range we created. This is exactly what we want.
Finish typing squirrel. Finish it with a closing parenthesis. Press the Return key to see the result.
The jumbled word is placed in the row with each letter in a separate column.
Skip two rows and type =transpose(monkey). The row below the jumbled letters is used by students to spell out the word.
Repeat the process eight more times. Choose any set of words you like.
We need to prepare the table for transfer to a Google Doc. We need to number the word list. Click on cell A1. Click Insert and select Column left. Repeat the process one more time to insert two columns.
Number each word from 1 to 10.
Google Doc preparation
In the next step, we are transferring the word jumbles to a Google Doc. This document will be distributed to students.
Open a new tab and create a new document. Here is an easy way to create a new document. Type docs.new.
Change the name of the document. Name it Mammals word jumble number 1. I am assuming you will be creating additional word jumble puzzles.
Return to the Google Sheets tab. Select all the word jumbles. Click Edit and select Copy.
Go to the Google Docs tab. Press the Return key three times. This space will be used for our title and instructions later.
Paste the contents. A paste format option appears. Choose the option to paste the contents unlinked.
We need to format the contents before it is ready to distribute.
Select all the table cells.
Select a font for the word jumbles. I like Nunito normal. Change the font size to 14 points. Center align the text.
Right-click over the table to get the contextual menu. Select the Table properties option.
Set the table border at 1 point. Select middle for the vertical cell alignment. Click the OK button.
Deselect the table cells. Drag the left border of the first letter toward the numbers column. Move it as far as it will go.
Highlight all the cells in the first row beginning with the first cell with a letter.
Go to the menu and click Format. Go to the Table option. Select the option to Distribute columns.
Select all the cells in the table again. Right-click and go to the Table properties option. Change the border width to 0 points. Click the OK button.
Select the cells with the letters for the first word. Include the cells in the row below each letter.
Click the border selector. Choose the all borders option. It is the first tile on the top left.
Go to the button bar. Click the border thickness selector. Choose the 1 point option.
Repeat the process with the next word. Place the border around the word only. Repeat this process with all the words.
The last word does not have a row below. We need to add a row for the students to unscramble the word.
Click inside one of the cells in the last row. Go to the menu and click Format. Go to the Table option. Click Insert row below.
The last two word goes off the first page and into the next. My document has the default margins of 1-inch all the way around.
Go to the menu and click File. Go to the page setup option. Change the paper size to Legal. Click the Ok button.
Return to the top of the page. Provide an assignment title. Include some instructions.
Use the Title paragraph style for the title.
This is the basic word jumble. There are modifications we can make to the assignment if we need to provide differentiation. This is useful for struggling learners or second language learners.
Modifications
Word clues
On the second page, I often include a small table with the words. The words are not in the same order as the jumbled versions.
Go to the bottom of the page. In the menu, click Insert and go to the Break option. Insert a Section break. Use the next page option.
Type Word Clues at the top of the page. Use the Heading 1 style for the title.
To create the table, go the menu and click Insert. Go to the Table option and select a 5 by 2 table.
Type the vocabulary words in each cell. Place them in random locations. Center align the words in the table.
Sentence clues
Another modification option is sentence clues. The sentences provide context clues. The sentences can serve as definitions for the word.
Document outline
Using paragraph styles facilitates the use of the outline feature. Click the Outline icon.
Click one of the heading titles to jump to that section. This provides a way to quickly jump from one section to the next.
Student option
I like to include one more option for students. Select the cells below the word jumble.
Use the font color picker. Select a dark color. I’ll select blue. Repeat this for each set of cells below the word jumbles.
How it works
The puzzle is easy to use. Students type the letters into each cell in the correct order to unscramble the words. Being in the first cell. Type the first letter. Press the Tab key to go to the next cell. Type the next letter and repeat the process.
No tiles
You might not like the idea of entering letters into each tile or cell. You can merge the cells into one.
Select the cells in the answer row. Go to the menu and select Format. Go to the Table option. Select Merge cells.
The words will be entered normally.
The choice is yours.
This is the student master. Use it to create versions with a word list or sentence clues. Erase any answers from this version.
Teacher master
We need to create a teacher master. This master contains the answer key. It is also the version to be used when reviewing the solution with students. This makes it ideal for guided practice or review.
Click File and select Make a copy.
Update the name. Erase the words copy of. Append Teacher Master to the name. Click the OK button.
Click File and go to the Version History option. Select Name current version.
Type Original for the version name. Click Save.
Fill out the answers to the word jumbles. I like to use red to help the letters stand out. Format the answer key according to your preferences.
We are going to save this version with a version name too. Go to the menu and select File. Go to the Version history option. Select Name current version. Use "Answer key" for the version name.
Original and answer key
This is how you switch between the versions. Go back to the Version history option. Select See version history.
Enable the option to Only show named versions. You will see the versions we created.
Click the Original version name.
Click the Restore this version button. Click the Restore button when prompted to confirm.
This version is saved and the document can be restored to this format at any time. Go ahead and solve the word jumbles on your own. Repeat the process above to restore the original version. Your changes will be removed and the document restored without any answers.
I like using this process when doing a guided practice with students.
Use the answer key to show students the answers. Use it to quickly check student work.
