A Little Background
Summer is upon us and like many families, we plan to take a good, old fashioned family road trip to arrive at our vacation destination. I still enjoy road trips. The games, mixtapes (well, playlists now), looking at the scenery throughout different parts of the country. I guess that makes me old school. I’m not necessarily opposed to putting on a movie for the kids on a long car trip, but I do think there’s a unique opportunity for quality family connect time over goofy car games that screens can’t replicate.
A couple of years ago our family planned a beach trip. I wanted to give the kids a couple of games to play to help pass the time so I started looking around the internet for an existing road trip bingo to print out. I was left wanting. One was a bit too elementary for our kids’ age and stage. Another was so small that it would have been completed before we even got out of Nashville. One was specific to the west coast. After a dozen strikeouts, my daughter suggested, “Daddy, let’s make our own!” So we did. And it’s since become a road trip tradition since.
As we prepare for the trip, we put our heads together to list out everything we want to include on the bingo cards. Just about anything goes. A brand. A tire on the side of the road. A bumper sticker. A certain type of car. Really whatever we think would be fun to search for. Once we settle on a list of items, I find an image online for each item and set up bingo “cards” in Excel.
Here’s an example of what our finished bingo cards look like. Feel free to download them and customize them for your family’s taste.
Note: The images used in this sample fall under the creative commons license. These samples are not intended for sale in any way, but are intended to be used for good, old-fashioned family fun and for educational purposes.
The Nerdy How-To Part
There are 3 sections to the Excel workbook: The cards themselves (the sample has 5 cards prepared), pic links, and selections.
Pic Links
The Pic_Links tab is setup as a staging and preview tab for the images.
Column A (Pic Number) serves as a unique identifier for each picture.
Column B (Description) is simply for organization. Frankly, I sometimes forget details about solutions I build after the project ends and I move on to another one. I need a little documentation as a reminder. Also, the IMAGE() function works by rendering an image from a URL. It’s possible that, over time, an image may removed or renamed breaking the URL. When this happens, the Description column can help to remind what image we originally intended to display.
Column C (Pic Preview) is where the IMAGE() function renders the image from the URL in Column D (Pic URL). Column C is set to the same row height and column width as each cell on the cards so we can see how the image will look when rendered in a cell. Then, if any adjustments need to be made using the optional parameters in the IMAGE() function we can do so to make the pic as clear as possible.
Column E (IMAGE() Sizing Parameter) houses the optional third parameter value for the IMAGE() function.
The syntax for IMAGE() is:
=IMAGE(source, [alt_text], [sizing], [height], [width])Source requires an image URL or a reference to a cell the contains an image URL.
Alt text is an optional place to specify text related to the image for accessibility.
Sizing is an optional property and can be set with these values:
- 0 – (Default) Excel sizes the image to fit inside of the cell at the original aspect ratio. There may be whitespace visible in the cell.
- 1 – Excel resizes the image to fill up the entire cell. The image will be stretched to fill up all available whitespace in the cell.
- 2 – The image maintains the original size and aspect ratio. Usually, this means that only a portion of the image will be visible in the cell
- 3 – Use the third (height) and/or fourth (width) parameters to specify a custom size and aspect ratio for the image.
When 3 is entered as a sizing property, a Height or Width is required. The formula will throw an error if omitted. The Height and Width parameters take the number of pixels to resize the image to. In the sample, I defined named formulas called RowHeight and Width to use for the Height and Width parameters set to a scale that works well for the sample file.
Selections
The purpose of the Selections tab is simple: generate a list of numbers in random order between 1 and the number of pics used (in the sample, 30). One list is generated per bingo card. These numbers will ultimately correspond to the unique picture number in Column A on the Pic_Links tab to determine what pic should be used in each cell of the bingo card. I adapted the formula below from the Excel Campus blog to generate each list:
=SORTBY(SEQUENCE(30,,1,1), RANDARRAY(30,,1,30))This is a good spot to mention that I like to grab more images than available spaces on the bingo card. The sample file contains 30 images but each card only needs 24 images. Grabbing more images means more variance between each card.
The Cards
Everything comes together on the Cards tabs. Each cell containing an image uses XLOOKUP to grab a number from the Selections list, matches that number to corresponding Pic Number, and finally returns the image.
=XLOOKUP(Selections!$A2,Pic_Links!$A:$A,Pic_Links!$C:$C,,0)Since all of the image formatting is done on the Pic_Links tab, no further action is required. Each Card tab grabs a value from a different list of numbers on the Selections tab.
Notes
The workbook is set to recalculate formulas whenever a change is made to a formula, value, or named range. This means that as changes are made around the workbook, the list of randomized numbers on the Selections tab changes.
The workbook is saved as an .xlsm – macro enabled Excel workbook. One of the named ranges I defined, RowHeight to be specific, uses the GET.CELL() function and requires that macros are enabled. There is not any other VBA script or macro running in the background.
The IMAGE() function was released in November 2022 and is available in Microsoft 365 versions of Excel including the versions for Mac, iOS, and Android.
In Closing
I hope you enjoy this game as much as our family does!