Games for Education - Making BINGO cards using a spreadsheet

I teach English in China to seven year old students. We have been learning phonics and if effort to help my students pay attention to phonetic words, I decided to create some random BINGO cards. It is quite simple to do.

I used Libreoffice to create my spreadsheet, but any spreadsheet program should work fine (Excel, Openoffice, Google Docs, etc).

The first thing we will want to do is create our word list. This can be as many or as few words as we want. You should leave a few rows empty before the start of your list. Write all of your words in a column (see above screenshot).

Now  that we have a word list, we will need to have the spreadsheet count the number of words (this is so we can snatch a random word from the word list). This is the code to use:
=COUNTIF(B5:B999,">.")
I used the COUNTIF command to count from the B5:B999 (the last number should be larger than the number of items you plan on having in your word list). Now we have the number of words in our list.

Now we will display a random word from the list. See code below:
=INDEX(B5:B999,((RAND()*B4)+1))
From the same group of numbers (B5:B999) we will pick a random number. The *B4)+1 means that it will only look as far as the number of items that we have (otherwise we would most likely end up with nothing because our search field is 994 cells, but only 57 of them actually have content).

In a new sheet we can create our BINGO template. This you can do the formatting to your choosing so that it looks nice (I promise the only reason I use the Comic Sans font is not because it looks nice, but because it is closest to how I expect my students to write letters).

Here is the code we will put in every BINGO cell:
=INDEX(\$'Word List and Instructions'.\$B\$5:\$B\$999,((RAND()*\$'Word List and Instructions'.\$B\$4)+1))
We are extracting the data from B4 in the previous sheet. Make sure to include the dollar signs. This makes sure that when you copy and paste, it will still reference B4.

Our last step is to print or export to PDF (make sure you only do one sheet/page).

Once this has been done, you can press the F9 key to generate a new random sheet.

Here is a link to the file on Google Docs if you would like to see how it works.
Here is a link to the original Libreoffice file.

After having lived in Oregon, Alaska, and China for the past 10 years doing landscape design and English teaching, I have returned to my home state of Utah and currently work for a growing tech company.

In my free time I enjoy working on my TutorialGeek.net website where I post tutorials and reviews (and anything else I think is geeky) and I also like to write songs for my Super English Kid Youtube channel.

My favorite things to do include anything with my wife and son. Hiking, camping, and photography (or anything else outdoors). Playing Ultimate Frisbee or Ping Pong. Listening to 60s, 70s, or 80s, music.

Feel free to contact me using my contact page. I would love to hear from any of you!