will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Create Bingo Cards in Excel
It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the lunch hour.
In this example, there are three cards, each with a set of random numbers. You’ll need one of those numbered ball popper machines though, or create a number selector in Excel.
Make Lists of Random Numbers
To generate the random numbers, there are three sets of lists on the Numbers sheet. In each set, there are typed numbers 1-75, with the formula =RAND() in the adjacent column. Numbers 1 to 15 will appear in the B column, 16 to 30 in the I column, and so on.
Add Random Numbers On Cards
On the three bingo cards, an INDEX/MATCH formula pulls the numbers from the random number tables. For example, this formula is used in cell E2:
=INDEX(Numbers!$M$1:$M$15,MATCH(LARGE(
Numbers!$N$1:$N$15,ROW()-1),Numbers!$N$1:$N$15,0))
The same number might appear on all three cards, but each card uses a separate lookup table of random numbers.
Print the Cards
The cards are formatted to print three per sheet, on letter sized paper. After you print, press the F9 key to generate a new set of numbered cards.
Download the Sample File
You can download the zipped Bingo Card Excel file, from my web site. Enjoy your lunch hour, and if you create a random number selector, let me know.
Bingo Number Generators
Update: We’ve had a few creative solutions to the Bingo Number Generator challenge.
1) Dick has created an Excel Bingo Number Selector file. You can watch his video demo and download the sample file. Below is a screen shot with a couple of numbers selected.
2) JP created an Excel Bingo Number Generator with a UserForm, that you can download from his blog.
3) Jim Cone has updated his Bingo Card file, and added a link in the comments below.
4) In the true Bingo Hall style, Chip Gorman has contributed an Excel Bingo Number generator with a voice that calls the numbers (if that feature is enabled on your computer), and a board that lights up the called numbers — with blinking. Awesome!
_____________________
March 13th, 2009 at 7:38 am
Here’s my random bingo selector:
=CHOOSE(RANDBETWEEN(1,5),”B”,”I”,”N”,”G”,”O”)&RANDBETWEEN(1,75)
March 13th, 2009 at 9:57 am
JP, the B numbers are 1-15, I numbers are 16-30, etc.
Also, we’ll need a way to keep track of what’s been called, so we can verify the million dollar prize.
March 13th, 2009 at 3:13 pm
I had no idea so much was at stake. I’ll rework the formula.
March 15th, 2009 at 7:03 am
[...] Create Bingo Cards in Excel, Excel MVP Debra Dalgleish posted a bingo card generator. Perfect for those slow work days (does [...]
March 15th, 2009 at 7:12 am
OK Debra, I created a userform to generate the selections. It’s not as cool as Dick’s version, though.
March 15th, 2009 at 8:31 am
Thanks JP, I’ve added a link in the blog post.
March 15th, 2009 at 1:57 pm
You all inspired me to contribute to the sorely neglected field of Bingo card number generation.
My Bingo Card Variety workbook now has a “Draw Numbers” option. Download from… http://excelusergroup.org/media/
Jim Cone
Portland, Oregon USA
March 15th, 2009 at 2:18 pm
Thanks Jim! This is more fun than real work.
March 16th, 2009 at 6:45 am
Jim’s version also has Buzzword Bingo (hilarious), but Chip’s workbook looks more like a Keno board than Bingo.
March 16th, 2009 at 7:40 am
JP, I’ve never seen a Keno board, but here’s a picture of the type of Bingo board that Chip was following ( I think!):
March 16th, 2009 at 7:42 pm
I know, but that board has the letters running down the left side, the workbook doesn’t
March 16th, 2009 at 11:58 pm
Ah, well maybe Chip will add the letters in version 2.0.
March 17th, 2009 at 8:55 am
The one I followed actually had letters down the side, but oddly they said “BONANZA” and didn’t line up with the numbers (obviously). So I left them off. It’s been about 30 years since I’ve seen a bingo board in person (at the church fair when I was a kid) so you’ll have to forgive my ignorance on the details of the board.
I do like how that one shows the game you;re playing too since it’s not always a straight roww/column/diagonal. Hmm. Great, another upgrade needed.
May 6th, 2009 at 9:13 am
No one has step up to the challenge to make the “FREE” randomly show up in the table everytime?
July 7th, 2009 at 7:11 pm
Thank you guys!
I’ve been working a bingo game for an upcoming family trip.
I modified your bingo a little because I’m doing a word bingo. It works great. I have modified the bingo card creator for it to show the words and modified Chip’s bingo call file to show the names.
However, since some of my words are long, it looks too small when I draw. How can i edit the size of the ball?
Hope someone can help me with that.
Thanks!
July 7th, 2009 at 9:00 pm
Noukie, the ball and text are on the worksheet (hidden), and you can resize them there. If you don’t have Excel 2007, you can use code to make them visible.
‘====================
Sub ShowShapes()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Name = “BingoBall” Or _
sh.Name = “BallNumber” Then
sh.Visible = True
End If
Next sh
End Sub
July 7th, 2009 at 11:29 pm
Debra, I knew about the hidden sheet. That’s where I changed the numbers to the words I wanted.
I had already resized the board showing the words that had already been called.
What I need to resize is the blue ball where the new number/word is shown after you click on “DRAW.”
I don’t see the ball you said are on that sheet.
I do have Excel 2007.
Also…I’m not used to working on Visual Basic so not sure where to put those codes you gave me.
Hope you can help.
July 7th, 2009 at 11:41 pm
Noukie, sorry for the confusing instructions. The ball and text on on the Bingo sheet, but they’re hidden.
Since you have Excel 2007, you won’t need to use the code.
Select the Bingo sheet, then on the Ribbon, click the Home tab.
At the far right of the Ribbon, in the Editing group, click Find & Select
Click Selection Pane
Click in the boxes for BallNumber and BingoBall, to make them visible
Now you can see the shapes, and resize them.
When you’re finished, uncheck the boxes and close the Selection and Visibility pane.
July 7th, 2009 at 11:55 pm
Perfect! Perfect!
Thank you Debra!
The whole family will be impressed come bingo night…
October 1st, 2009 at 11:12 am
This is great! Thank you for putting this together and sharing it with everyone. I was looking at a number of software packages that do the same thing. This is better because I can modify it: color, font, size, etc to suit my needs.
I need to use it with words, and I figured out how to change the numbers to words. What I don’t know how to do is put all 24/25 words in one list and have them randomly across the card, instead of having it all in one column.
December 5th, 2009 at 1:24 am
[...] P.S. There’s no Connect 4 game on Contextures Blog, but you can download an Excel Bingo game. [...]
January 26th, 2010 at 12:23 am
[...] you’d like to play games in Excel you can try these Excel bingo cards or an Excel Concentration [...]