Thank you! Awesome step by step and easy to understand instructions. I used this to create a bingo generator for my wife to use. Just recorded a simple macro to resort the lists and linked it to a refresh button for her. Now she can create as many unique cards as and when she needs it. Would have taken me much longer and it probably wouldn't have been so nice if not for this. Thanks again!
This was great! loved the simple solution! For my needs, I wanted a simple way to sort across all sheets & have the front sheet update with a new set of tickets. For this I used a simple macro (copy pasted) & it worked like a charm. Thanks so much for this tutorial!
This tutorial was awesome. I'm making custom bingo boards for 400. Not sure how many words I need or how to do that many but I feel this tutorial will get me there. Would love to learn how to add icons too. Not sure if there is a way to do that.
Hi, Demetria. I’m sure you could! You could consider converting them to pdf before sending. That way each is saved with your header showing (if you used one). You’ll probably only send 1 bingo board per recipient, so you’d need to refresh each before saving (so each person gets a unique board)
This is very helpful indeed. Thank you very much. I would like to ask... can this method, or a similar one, be used to insert images, too? If it can, it may save me many, many hours each year. Perhaps hundreds. I have been doing this job in Word and manually inserting and "randomizing" the words/images. So it takes me about 3 hours to make 9 bingo cards each using a selection 16 images out of a total of 24 images. That way I am sure I have used each image on six of the nine bingo cards. If anyone could give me hints regarding the above, I would be grateful. Thank you.
Hi. A bingo sheet with images has been on my queue forever now! You can do it by using the Camera tool feature to insert images. So, list your images next to random values so you can sort them. Then link each image using the Camera feature - make sure your shapes are sized accordingly for your sheet. You can then create more sheets and use the same method (as in this video) to randomize the images that populate. HOWEVER, you will likely NOT see the images update. At least they don't on my very slow computer. The Camera feature is a bit of a hog. Save your work, close the file, and, when you reopen it, the images should be in different locations in each sheet. Here's a video on how the Camear tool feature works: ua-cam.com/video/5XULAGj4OZA/v-deo.html Hope this helps! And thank you!
@@YAcostaSolutions I looked up your suggestion, thank you. It seems that this tool is for highlighting or reporting briefly upon a large range of data within a worksheet. I can not find references to anyone having used it for the purpose I asked about, however. That means I can not read about the specific commands / procedures necessary to do what I want. If you can be more specific, that would be great. Thank you again.
great tutorial. I did a total of 50 sheets, I am worried and concerned that when all 50 people play there will be multiple people wining. its for a baby shower and need to get gifts for the games. how many people do you think will win if all 50 cards are played and there is a total of 50 words? thank you.
Hi, great step by step guide thanks. Do you copy the formula over the 'blank' square so it hides one of the words and this bingo card would never be able to have that word? I did and the formula just overwrote the square - should I then black it out again?
Hi. I have used your method for making bingo cards and it works. My question is, can I use the same method to import pictures in a bingo card, or is there another method. I am using excel 2007 and basically going nuts in my attempt to import images into the cells and use them as I would with a rand formula for words. Thank you in advance
First time i was trying creating a BINGO game on excel. Thank you so much! it's really interesting and helpful. However, i want to put phrases in the bingo boxes but these are not fitting in properly in square boxes. Kindly guide me. Thanks!
Hi, Anita. Have you tried applying Text Wrap formatting to them? This can help by wrapping only those terms that are too long. You'll find the Text Wrap option in the Home tab, in the Alignment section. If that still doesn't work, you may need to do the Text Wrapping and begin gradually reducing the font size. If so, I recommend reducing only 1 point at a time and stopping once they all fit. In addition to those steps, you could also make all the squares a little bigger. I hope this helps. Please let me know if you have other questions. Thanks!
So where I keep having problems is making the squares on the board an absolute reference because F2 and F4 do nothing for me accept brighten my screen and bring up my widgets on my Mac. Can you actually say what F2 and F4 are like a copy-paste or is there a way to spell it out what you are actually typing and doing it long way at least in the first square?
Hi, Penny. On some keyboards, laptops especially, you sometimes have to press the fn key along with any of the F1-12 keys. What you're doing here is creating a link to the smaller board in each of the sheets with the word lists. Actually, if you are using a newer version of Excel (2013 or newer, I think???), you can copy your smaller board and, on the sheet with the ones that will be printed, paste it on the corresponding spot using Paste Special / Paste Link feature. The reason for this is so that, when it's time to print them, having them all on the same sheet, with the same formatting, headers, margins, etc, it makes it easier to ensure they all print uniformly.
Hi, I am planning a baby shower and think is a great game to incorporate. I don't know excel too well, just the very basic so please be patient with my questions. So far I was able to put in the rand formula next to my list, create the 5X5 board and insert the first word, but I do not know how you are copying and paste the words into the columns. I cannot get that done can you tell me how you are doing that. I am working on my laptop
Hi, Carnetta. For the very first word, you'll manually reference the cell. Do this by typing = and select the cell with first word on your list and press Enter. What I did next was placed my cursor on the cell with the first word you just copied and pressed Ctrl+C to copy the formula. Then, holding down the Shift key, use your down arrow to select the next 4 cells below it and press Ctrl+V to paste to those cells. I hope this answered your question. Please feel free to send me other questions you may have as you work through this. Will do our best to make sure you can create your bingo boards in time for the shower 🙂
Hi, Chalondra. Which part are you at? If you've only just made a copy of your first sheet, once you rename it, the links on the little board that's in each sheet, will update. If you're at the point where you're making the sheets to be printed, you need to make each link on the first sheet an absolute value, then use find & replace to update any subsequent sheets. This is at roughly 4:40 in the tutorial. Hope this helps. And thanks!
Hi, Carly. I'm not able to recreate the error on my side. Let's try something else. I think it's Excel 2013 and newer that has the Paste Link feature. You can tell if you have it by copying something, right-clicking and, under the Paste Options, you should see a clipboard with 2 small chains on it. That's the Paste Link button. You can also click on Paste Special and you'll see a button for it on the bottom left of the pop-up window. If you have this, you can select each of your mock boards, one at a time, copy each and use the Paste Link feature to paste them to your final boards. That will create a link, so you can just skip the Find & Replace step. This link will take you directly to this section the article for this tutorial: yacostasolutions.com/make-bingo-game-in-excel/#link-boards-using-paste-link If you don't have it, can you provide me more detail? For example, how are your tabs labeled, what cell reference is in the part that's giving you the error, etc. Thanks
@@YAcostaSolutions thanks have tried this as I do have the link paste but all it's doing is copying sheet 2 and sheet 3 to board exact with no variation in the words!
Hi, Carly. If you like, I can take a look at your spreadsheet? If so, when you get a chance, can you email it to me at yil at yacostasolutions dot com I can take a look at it tomorrow. Thanks
Happy Holidays! I've had much success in creating my bingo boards - the latest is for Christmas. My question is do I have to recreate spreadsheets and boards whenever I want a different game theme? If not, how to I change the names on sheet 1, etc, and create new boards? Do I have to type the formulas all over again? Thank you.
Hi, d marie. Happy holidays to you too! Let’s try this, instead of recreating the boards, you can create a copy of your existing file. To do this, you can copy, paste it, and rename it from your file folders or use Save As to save a new copy. I’m guessing that all your sheets that contain list of words are formatted the same? As an example, say, cell B2 always contains the first word in the list. If so, you can group the sheets together to make bulk updates. To do this, select all the tabs that contain the list of words. You can either press the Control key as you select each tab to group them together or press the Shift key, select the first sheet and, while still holding down the Shift key, select the last sheet that you want to group. With them still grouped together, you can take a new list of words, copy it (a good idea to have this on a spreadsheet) and paste it over the existing list to replace the old words. Or you can just type over the existing words. As long as each of your tabs is selected, you only need to do this to one sheet. This will replace the contents in all your selected sheets. However, you will need to re-sort each of the word lists. There should be no need to update the formulas on your boards, they should now be referencing the new words. Give this a try and let me know if you come across any hiccups. Thanks and hope you have a wonderful Christmas!
How did you make the “boards” tab? I moved the columns to fit the size I want, but mine aren’t bold square like yours. Can you make a video just on how you made the “boards” tab?
Hi, Cerissa. My squares aren't bolded. I wonder if it's just a difference in screens? You could always select the entire sheet and click on Bold or press Ctrl+B to bold all the text. If you want to make your borders thicker, you can select all the cells on the board, in the Borders drop down, select a different "Line Style", then, back in the Borders drop down, click on "All Borders" to apply the style. I hope this helps. Please let me know if you have more questions. Thanks
Hi, Suzanne. Yes, you could do this! What you would do is, instead of 1 words list, create a list of words for each category you want to do. They could all still be sorted by the same list of random numbers, since the numbers will change each time. Then, to create your board, you would reference a different words list in each column. I hope this makes sense. Please let me know if it doesn't or if you have other questions. Thanks 🙂
@@YAcostaSolutions hi! thanks for the video. I am trying to do categories. I have my word lists in separate columns. I have tried using a single column for the random numbers, (also tried doing separate Random Number Formula columns for each category). When I do the DATA sort, I get a lot of blanks on my cards. Is this because I have 10 items in Category A, 15 items in Category B, 18 items Category C, etc? Would each list have to be the same amount of words to ignore blanks during the sort? Or is there another step I'm missing? Hope this makes sense and thanks again!
Hi, Mandy. This sounds complex! Did you want, on your bingo boards, to have 1 category per column? If so, then you would want to randomize each column and then reference each list by the column. It would look almost like a Jeopardy board, but would be for bingo. If not, it might be easiest to combine all your categories into 1 master list and randomly sort that - adding a 3rd column to your word list with the category name for reference. Please, do let me know if this is what you were looking for or if I misunderstood your layout? Also, just a thought - not sure how many boards you are looking to create, but it sounds like you have a LOT of words! Just make sure to not overdo it so that you're not playing for hours before someone gets bingo...
Hi, Opie Yosam. This sounds like you’re planning a fun event 😊. To make 35 cards, on the tab labeled “boards,” you’ll have 35 boards formatted to print 1 per page and linked to the corresponding tab that contains the words. You’ll need a total of 35 of the tabs with the words. As an alternative, (and I wish I’d thought of this when I created the tutorial…) is size and format the boards included with the list of words for printing - you could create 1 with the header and correct formatting, set the print area for only the board part (Page Layout / Page Setup / Print Area), and, once you’re happy with it, copy it 34 times. Just make sure to resort each one so the order of the words changes and you don’t end up with a duplicate. I hope this helps. Please let me know if you have more questions.
Hi! Omgz, you probably think I am nuts, but am totally loving your explanations and that you respond to questions! So, took a break from working my schedules and thought ooh this would be a fun game to make for my team! I have my Sheets 1-3 done like you stated, and am now working on the board. I have the words moved over and am validating. Here is where mine is different. When I am validating by hitting F2, then F4 then Enter, it pulls up the Save File and I have to click on the file name. Is this supposed to happen on the newer editions of Excel? Just curious :) Thanks again for all that you do! You are my Rock Star!!
Hi, Michi. Are you using a laptop? Some laptops require you to also press the fn key along with the F shortcuts. Please let me know if it still gives you trouble. Thanks
How can I substitute pictures for words, I inserted pictures instead of words in column C as you did with words, however when I went to create the board and use the formula as you said it will not insert to picture when I enter. Thanks for a responds.
Hi, Tammy. This is actually something I've been pondering doing a tutorial on and I really think it's past due. The process is kind of clunky, but here's what you could try: You'll still use the RAND function, but, replace the words with the pictures. Make sure all the pictures are the same size - 1" x 1" is a good fit. Use the Page Layout View to make it easier to set the column and row sizes using common measurements. Also, go to your View tab and uncheck Gridlines so that they do not show. Make sure the squares in your Bingo Cards are a notch bigger than the pictures - something like 1.1" each. Don't forget to set the print area and page breaks for each card. Use the Camera Tool to create linked pictures of each image. You'll do 1 image per bingo square. You'll need to do them one at a time. This will create a link to that image. You can read about how to use the Camera Tool on my website by clicking here: yacostasolutions.com/camera-tool-in-excel-tutorial/ Or watch the tutorial on UA-cam by clicking here: ua-cam.com/video/5XULAGj4OZA/v-deo.html It's time-consuming, but once you do 1 board, you can use Find & Replace to replace the sheet name in the cell references card by card. Using the RAND function, make sure that each list of pictures has randomized - so you don't end up with them all being the same. This is where it gets clunky. Excel freaks out when you use more than a few linked images in any one workbook. So, as long as the RAND function executed in each, you'll see the list of pictures has changed, but your Bingo Cards will all look the same. Depending on how powerful your computer is, you may want to limit the number of cards that you try to do at any one time. Save your workbook. Close it and open it. They should now all be different. If you need more cards, execute all the RAND functions again, save, and reopen. They should all look different again. I know this is quite lengthy. Let me know if you come across any issues or if it feels like I left something out. I'll also ping you once we have a tutorial ready on this. I think a visual would come in handy. Thanks
@@YAcostaSolutions Thanks for this video! I am also trying to make picture bingo. However, I don't see how Find & Replace is relevant to generating additional cards. The camera technique essentially creates another free floating image (not contained within any cell, it only references a cell for its content) whose formula only appears to be accessible if clicked on itself, not where it happens to be floating above the spreadsheet. Therefore, you can't just highlight the 5x5 or (w/e size card) beneath where the images are and do a "Find & Replace" because they're nothing more than empty cells with graphical borders. God I wish it were that easy, then we could use the workflow in this video! If you use the setup like you see here, with all visual formatting done in an initial boards tab, you have to click on each individual picture in the 2nd-nth card and change the sheet it is referencing. I found that I could just do the layout next to the randomized data in a sheet and copy the whole sheet. So now I've got a sheet for every board, instead of them all being in the first 'boards' tab. Like you say, you have to close and reopen every time for the resorting to take effect and generate your new cards. Let me know if you think I'm confused or you've since found a better way to do it. We're cooking up an activity for an online pre-K class of 35 kids! Thanks!
Hi, Michel’le. Thanks for watching my tutorial 🙂 There were a few steps to creating this: Start by creating the contents that will be in Sheet1:· * Make the list of words that you want to use and place a rand() formula next to each word * Create a 5x5 template next to it and, in each cell, reference words in your list You can now create copies of Sheet1: * You should create 1 copy for each bingo board you want to make. So if you want to create 10 bingo boards, you would make 10 copies (Sheet1, Sheet2, Sheet3,…) * In each of these sheets, Sort by the column with the RAND() formula (because the numbers keep changing, each time you sort, the order of the words will be different) Create and format the bingo boards for printing: * Make however many 5x5 boards you want to print. Make sure to make the cells bigger as these will be for printing. * On the first board, create a link to each cell in the 5x5 template (the one made in Sheet1) and make them absolute (add the $ signs to the formula - ex $A$1) * Once done, copy all the formulas to the 2nd board. Highlight the cells only on the second board and, using Ctr+H, replace “Sheet1” with “Sheet2” (the find & replace will apply to all the highlighted areas). * Continue doing this for the other boards · Make sure each board is formatted to print only 1 board per page. I hope this helps. Please let me know if you have questions or want me to clarify something. Thanks!
@@YAcostaSolutions my question is in the video at 4:23 you copy your board to the actual bingo board but when I try to copy it it doesn't pop up like yours do in the video, so what do I press to get the whole board to copy because the 1st word copies but not the board
Hi, Michel’le. I think the issue might be that the formulas aren’t calculating. If after you dragged over, the formulas in the cells are correct, but only the first word is showing, you can try 1 of 2 things to fix: In the Formulas tab / Calculation / Calculation Options, check to see that your spreadsheet is set to calculate automatically. And, either way, click on Calculate Sheet to see if that does the trick. If this doesn’t fix it, then it’s likely that the cells were formatted to text before you started (when this happens you see the formula but won’t be able to get it to work). You can also test if this is the case by double-clicking in one of the cells and see if it updates. If it looks like this is the problem, use Ctrl+A to select the entire spreadsheet and change the format of all cells to General. Then use Ctr+F (the find and replace) and change = to =. It seems weird but can fix. I’m crossing my fingers this does the trick! Please keep me posted. Thanks
Hi, Lori. If your version of Excel has Page Layout view (you’ll see it in your View tab), switch to that. In that view, you can adjust the rows and columns using inches. Please let me know if you need more help or have other questions. Thanks!
im having trouble with my bingo cards! every time i go to to move my one of my sheets into my bingo layout the words are not copying. instead i am getting a value of 0 !! help please! i need these cards by friday!!
Hi, Feliciamarie. I hope I'm not too late in answering your question! Make sure the cells in your bingo layouts are locked (using the $ signs). If the issue is what I'm thinking it is, take a look at roughly 4:45 mins into the tutorial. So, create the layout for the first board, then use F4 to "lock" the cell references (it should look something like =Sheet1!$A$1). Do this to each cell of the board layout. Then, when you copy this to the second board, select the cells in the second board and use Ctrl+H to "Find: Sheet1" and "Replace with: Sheet2" (or whatever sheet names you've used). This will update your cell references on the second board layout to point to the list on the second sheet. I know it's a bit confusing, but I hope this helps. Let me know if you have other questions. Thanks
Hi, Sonia. Sorry it took me a bit to get to your question. I typically enjoy family time on the weekends 😊 Typically this happens if the cell references aren't pointing to the right cells. In the quotes below is what I recommended before. Have you checked this? For reference, you can also find this at around 4:45 in the tutorial. Please let me know if you continue to have issues. Thanks "Create the layout for the first board, then use F4 to "lock" the cell references (it should look something like =Sheet1!$A$1). Do this to each cell of the board layout for this first board. Then, when you copy this to the second board, select the cells in the second board and use Ctrl+H to "Find: Sheet1" and "Replace with: Sheet2" (or whatever sheet names you've used). This will update your cell references on the second board layout to point to the list on the second sheet. Continue doing this for each board (Replace with: Sheet3, Sheet4, so on)."
Has anyone tried to use this to develop a table that is 6x6 squares or 7 x 7 squares? I tried but the extra rows of squares simply register a "0" value in each box.
Hi, Jack. Thanks for watching 🙂 No one has mentioned to me if they've tried a board bigger than 5x5, but, remember that each board is linked to reflect what's in a "mock" board. That, in turn, is linked to the word list being randomized by the RAND function. You'll want to make that the mock board also 7x7 and link all the cells in the boards to be printed so that they mirror each other. I hope this makes sense. Please let me know if you have more questions. Thanks!
Thanks for the reply. I did get a 7X7 board laid out. I'm working at creating a larger game for big family reunion. I haven't finished everything yet, but with your video, I'm hopeful.
Hi, Anna. Thanks for watching. This was tricky! I had to think about it for a while, but here’s an idea of what you can do: •Add a 3rd column next to the words (or replace the words, if you don’t plan to use them for anything). •In this new column, one cell at a time, and next to the random numbers and/or words, make each cell big enough to contain your entire image (a good idea is to shape each cell and image to 1”). •Use the Camera Tool (here’s a link to a tutorial I did on the camera tool: goo.gl/GYK81m) and use it to “take a picture” of each image. This new camera tool image will be linked to the contents in that cell (so it'll change as the cell contents change). Repeat for each cell on your board. •Then, as you sort your list using the random numbers, the pictures will also sort and update on our bingo board. And this will also be printable. Please let me know if you still have questions and thanks for asking this question - I’ll be adding this to my calendar for a future tutorial 😉 Happy holidays!
Once you create the sheet1 that we made in the video, you can make as many copies as you want. You'll do this by right clicking on the tab at the bottom of your screen, choose "move or copy", then check off the box for "Create a copy." You can do this however many times you need by repeating the steps. I only did the 3 so that the video wouldn't be super long. Hope this helps.
How many words do you need to create on sheet 1 (one) e.g. 75 or does it matter? How many cards/boards should you create to assure you've captured all words for the created cards/boards?
Hi, d marie. In hindsight, I used waaaayyyy more words than was necessary in this example. 25 to 30 words will get you thousands of different combinations. If you want to be sure that each word is present, you can limit your list to 25 words and your board to 25 squares. In fact, we've been creating themed word lists on our website. Each list is only 30 words long. You just copy and paste what you need. Take a look: yacostasolutions.com/topics/word-lists/ As a side note, if you don't see the topic that you need, please let me know so we can add. Thanks
@@YAcostaSolutions Thank you so much for your quick response. I'm responsible for games for a baby shower in a couple of months, and your response is just what I needed. Again, thank you!!!
Great! Please let me know if you need anything else. Also, if you need help with the words, here's a link to our word list for baby showers: yacostasolutions.com/baby-shower-themed-word-list/ Hope the baby shower is a success! 🙂
Hi Opie Yasm, I ran into an issue. For the most part, my boards turned out perfectly; however, of the 25 boards, one board is the same as the first board (Sheet 1 and Sheet 15 are the same). How do I rectify this? Do I have to start from scratch? Thanks.
Hi, d marie. You shouldn’t have to start from scratch. The RAND() function refreshes itself with any action on the sheet, thus updating the order of the words. I wonder if one of the formulas didn’t have a chance to refresh before you printed or the formulas got lost? In any case, you could just refresh the RAND function in one of the sheets (you could do something as simple as type a letter somewhere on the sheet to refresh it) and then print just that one.
Thanks!
Thanks so much, Vicki 🙂 I’m happy this video helped you. Best of luck with your bingo game and thanks again!
OMG thank you so much!!! This was exactly what I needed. You are a gift.
Thanks so much, Misty! I'm happy this was helpful for you 🙂
This was the best tutorial for excel! Thank you!!
Oh wow! Thanks so much for the sweet compliment 🙂
Omg this was super helpful!!!!
I'm so glad! Happy you liked this so much 🙂
Thank you! Awesome step by step and easy to understand instructions. I used this to create a bingo generator for my wife to use. Just recorded a simple macro to resort the lists and linked it to a refresh button for her. Now she can create as many unique cards as and when she needs it. Would have taken me much longer and it probably wouldn't have been so nice if not for this. Thanks again!
That's great, Petrus! Thanks so much for sharing this with me. I hope the games are a hit 🙂
Thank you. Just what I needed!
Awesome! I'm so happy you liked this 🙂
Thanks for making this simple. We used this for our Vacation Bible School
That's wonderful, Frederick! I'm happy this helped you with the kiddos 🙂
Thanks for your excellent instructions!
Thanks for stopping by, Michele! I'm happy you liked this 🙂
Excellent tutorial
Thanks so much, Marcie! I'm happy you liked this 🙂
Thank you for these very clear instructions.
Thank you. Happy you liked this. Hope your bingo game is a big hit 🙂
That was brilliant. Thank you, thank you.🌹🌹🌹🌹
I’m so glad you liked this! Thanks so much 🙂
Amazing! Thank you for this!
Thanks, Rheya! I'm happy you liked this 🙂
This was great! loved the simple solution! For my needs, I wanted a simple way to sort across all sheets & have the front sheet update with a new set of tickets. For this I used a simple macro (copy pasted) & it worked like a charm. Thanks so much for this tutorial!
Awesome, HRayan! I'm happy you liked this and made it work for you even better! 🙂
So awesome. Going to use it for my classroom revision!
Hi, Courtney, that's awesome! I hope your students enjoy the bingo 😀 and thank you!
Thank you so much for this🙏
You’re welcome 😊 Hope your game is a success!
This tutorial was awesome. I'm making custom bingo boards for 400. Not sure how many words I need or how to do that many but I feel this tutorial will get me there. Would love to learn how to add icons too. Not sure if there is a way to do that.
Great step by step video. I wonder if I can send these virtually instead of printing them.
Hi, Demetria. I’m sure you could! You could consider converting them to pdf before sending. That way each is saved with your header showing (if you used one). You’ll probably only send 1 bingo board per recipient, so you’d need to refresh each before saving (so each person gets a unique board)
Please make next step thanks
Can you do a video on how to do the Bingo boards on Google Sheets
This is very helpful indeed.
Thank you very much.
I would like to ask... can this method, or a similar one, be used to insert images, too?
If it can, it may save me many, many hours each year. Perhaps hundreds.
I have been doing this job in Word and manually inserting and "randomizing" the words/images.
So it takes me about 3 hours to make 9 bingo cards each using a selection 16 images out of a total of 24 images.
That way I am sure I have used each image on six of the nine bingo cards.
If anyone could give me hints regarding the above, I would be grateful.
Thank you.
Liked. Subscribed.
Hi. A bingo sheet with images has been on my queue forever now! You can do it by using the Camera tool feature to insert images. So, list your images next to random values so you can sort them. Then link each image using the Camera feature - make sure your shapes are sized accordingly for your sheet. You can then create more sheets and use the same method (as in this video) to randomize the images that populate. HOWEVER, you will likely NOT see the images update. At least they don't on my very slow computer. The Camera feature is a bit of a hog. Save your work, close the file, and, when you reopen it, the images should be in different locations in each sheet.
Here's a video on how the Camear tool feature works: ua-cam.com/video/5XULAGj4OZA/v-deo.html
Hope this helps! And thank you!
@@YAcostaSolutions Thank you very much. I will try it, and whether I can / cannot do it, I will leave notes here for the next person trying it.
@@YAcostaSolutions I looked up your suggestion, thank you. It seems that this tool is for highlighting or reporting briefly upon a large range of data within a worksheet. I can not find references to anyone having used it for the purpose I asked about, however. That means I can not read about the specific commands / procedures necessary to do what I want.
If you can be more specific, that would be great.
Thank you again.
I'm trying to copy the sheet on to the board. I get the first box done no problem but can't fill the rest of the square
great tutorial. I did a total of 50 sheets, I am worried and concerned that when all 50 people play there will be multiple people wining. its for a baby shower and need to get gifts for the games. how many people do you think will win if all 50 cards are played and there is a total of 50 words? thank you.
Hello ! At the 4:22 how to copy ? I can’t do it 😢
Hi, great step by step guide thanks. Do you copy the formula over the 'blank' square so it hides one of the words and this bingo card would never be able to have that word? I did and the formula just overwrote the square - should I then black it out again?
Hi. I have used your method for making bingo cards and it works. My question is, can I use the same method to import pictures in a bingo card, or is there another method. I am using excel 2007 and basically going nuts in my attempt to import images into the cells and use them as I would with a rand formula for words. Thank you in advance
First time i was trying creating a BINGO game on excel. Thank you so much! it's really interesting and helpful. However, i want to put phrases in the bingo boxes but these are not fitting in properly in square boxes. Kindly guide me. Thanks!
Hi, Anita. Have you tried applying Text Wrap formatting to them? This can help by wrapping only those terms that are too long. You'll find the Text Wrap option in the Home tab, in the Alignment section.
If that still doesn't work, you may need to do the Text Wrapping and begin gradually reducing the font size. If so, I recommend reducing only 1 point at a time and stopping once they all fit.
In addition to those steps, you could also make all the squares a little bigger.
I hope this helps. Please let me know if you have other questions. Thanks!
So where I keep having problems is making the squares on the board an absolute reference because F2 and F4 do nothing for me accept brighten my screen and bring up my widgets on my Mac. Can you actually say what F2 and F4 are like a copy-paste or is there a way to spell it out what you are actually typing and doing it long way at least in the first square?
Hi, Penny. On some keyboards, laptops especially, you sometimes have to press the fn key along with any of the F1-12 keys.
What you're doing here is creating a link to the smaller board in each of the sheets with the word lists.
Actually, if you are using a newer version of Excel (2013 or newer, I think???), you can copy your smaller board and, on the sheet with the ones that will be printed, paste it on the corresponding spot using Paste Special / Paste Link feature.
The reason for this is so that, when it's time to print them, having them all on the same sheet, with the same formatting, headers, margins, etc, it makes it easier to ensure they all print uniformly.
Like this tutorial? Make sure to grab your FREE pdf list of top Excel keyboard shortcuts: yacostasolutions.com/newsletter/
Hi, I am planning a baby shower and think is a great game to incorporate. I don't know excel too well, just the very basic so please be patient with my questions. So far I was able to put in the rand formula next to my list, create the 5X5 board and insert the first word, but I do not know how you are copying and paste the words into the columns. I cannot get that done can you tell me how you are doing that. I am working on my laptop
Hi, Carnetta. For the very first word, you'll manually reference the cell. Do this by typing = and select the cell with first word on your list and press Enter.
What I did next was placed my cursor on the cell with the first word you just copied and pressed Ctrl+C to copy the formula.
Then, holding down the Shift key, use your down arrow to select the next 4 cells below it and press Ctrl+V to paste to those cells.
I hope this answered your question. Please feel free to send me other questions you may have as you work through this. Will do our best to make sure you can create your bingo boards in time for the shower 🙂
@@YAcostaSolutions
Okay, thank you so much for your quick reply.
I need help, every time I copy and paste the sheet from sheet1 keeps apearing
Hi, Chalondra. Which part are you at?
If you've only just made a copy of your first sheet, once you rename it, the links on the little board that's in each sheet, will update.
If you're at the point where you're making the sheets to be printed, you need to make each link on the first sheet an absolute value, then use find & replace to update any subsequent sheets. This is at roughly 4:40 in the tutorial.
Hope this helps. And thanks!
Hi I am unable to do the find and replace when I follow your instructions it opens a box that says update values: sheet2 any ideas? Thanks
Hi, Carly. I'm not able to recreate the error on my side.
Let's try something else. I think it's Excel 2013 and newer that has the Paste Link feature. You can tell if you have it by copying something, right-clicking and, under the Paste Options, you should see a clipboard with 2 small chains on it. That's the Paste Link button. You can also click on Paste Special and you'll see a button for it on the bottom left of the pop-up window.
If you have this, you can select each of your mock boards, one at a time, copy each and use the Paste Link feature to paste them to your final boards. That will create a link, so you can just skip the Find & Replace step.
This link will take you directly to this section the article for this tutorial: yacostasolutions.com/make-bingo-game-in-excel/#link-boards-using-paste-link
If you don't have it, can you provide me more detail? For example, how are your tabs labeled, what cell reference is in the part that's giving you the error, etc.
Thanks
@@YAcostaSolutions thanks have tried this as I do have the link paste but all it's doing is copying sheet 2 and sheet 3 to board exact with no variation in the words!
Hi, Carly. If you like, I can take a look at your spreadsheet? If so, when you get a chance, can you email it to me at yil at yacostasolutions dot com I can take a look at it tomorrow. Thanks
Thank you for the very useful video, I started to create a gender reveal bingo game. I was wondering how many calling cards should we print ?
Happy Holidays! I've had much success in creating my bingo boards - the latest is for Christmas. My question is do I have to recreate spreadsheets and boards whenever I want a different game theme? If not, how to I change the names on sheet 1, etc, and create new boards? Do I have to type the formulas all over again? Thank you.
Hi, d marie. Happy holidays to you too! Let’s try this, instead of recreating the boards, you can create a copy of your existing file. To do this, you can copy, paste it, and rename it from your file folders or use Save As to save a new copy.
I’m guessing that all your sheets that contain list of words are formatted the same? As an example, say, cell B2 always contains the first word in the list. If so, you can group the sheets together to make bulk updates.
To do this, select all the tabs that contain the list of words. You can either press the Control key as you select each tab to group them together or press the Shift key, select the first sheet and, while still holding down the Shift key, select the last sheet that you want to group.
With them still grouped together, you can take a new list of words, copy it (a good idea to have this on a spreadsheet) and paste it over the existing list to replace the old words. Or you can just type over the existing words. As long as each of your tabs is selected, you only need to do this to one sheet. This will replace the contents in all your selected sheets.
However, you will need to re-sort each of the word lists.
There should be no need to update the formulas on your boards, they should now be referencing the new words.
Give this a try and let me know if you come across any hiccups. Thanks and hope you have a wonderful Christmas!
Thank you so much! I’ll try this after the holidays when everything has settled down.
Ok. Please let me know how it goes.
What about with pictures?
What is next how to share and use it .
Each person will have one board and how they will strike out the names
How did you make the “boards” tab? I moved the columns to fit the size I want, but mine aren’t bold square like yours. Can you make a video just on how you made the “boards” tab?
Hi, Cerissa. My squares aren't bolded. I wonder if it's just a difference in screens? You could always select the entire sheet and click on Bold or press Ctrl+B to bold all the text. If you want to make your borders thicker, you can select all the cells on the board, in the Borders drop down, select a different "Line Style", then, back in the Borders drop down, click on "All Borders" to apply the style. I hope this helps. Please let me know if you have more questions. Thanks
Is there a way to randomize the words for each column seperately instead of the entire card (i.e. if you want to have categories in each column)?
Hi, Suzanne. Yes, you could do this! What you would do is, instead of 1 words list, create a list of words for each category you want to do. They could all still be sorted by the same list of random numbers, since the numbers will change each time. Then, to create your board, you would reference a different words list in each column. I hope this makes sense. Please let me know if it doesn't or if you have other questions. Thanks 🙂
@@YAcostaSolutions Thanks!
@@YAcostaSolutions hi! thanks for the video. I am trying to do categories. I have my word lists in separate columns. I have tried using a single column for the random numbers, (also tried doing separate Random Number Formula columns for each category).
When I do the DATA sort, I get a lot of blanks on my cards.
Is this because I have 10 items in Category A, 15 items in Category B, 18 items Category C, etc? Would each list have to be the same amount of words to ignore blanks during the sort?
Or is there another step I'm missing? Hope this makes sense and thanks again!
Hi, Mandy. This sounds complex! Did you want, on your bingo boards, to have 1 category per column? If so, then you would want to randomize each column and then reference each list by the column. It would look almost like a Jeopardy board, but would be for bingo.
If not, it might be easiest to combine all your categories into 1 master list and randomly sort that - adding a 3rd column to your word list with the category name for reference.
Please, do let me know if this is what you were looking for or if I misunderstood your layout?
Also, just a thought - not sure how many boards you are looking to create, but it sounds like you have a LOT of words! Just make sure to not overdo it so that you're not playing for hours before someone gets bingo...
Hi and thank you for such a great tutorial. I need to make 35 cards so that means I have to copy and paste sheet 1 34 more times correct?
Hi, Opie Yosam. This sounds like you’re planning a fun event 😊. To make 35 cards, on the tab labeled “boards,” you’ll have 35 boards formatted to print 1 per page and linked to the corresponding tab that contains the words. You’ll need a total of 35 of the tabs with the words.
As an alternative, (and I wish I’d thought of this when I created the tutorial…) is size and format the boards included with the list of words for printing - you could create 1 with the header and correct formatting, set the print area for only the board part (Page Layout / Page Setup / Print Area), and, once you’re happy with it, copy it 34 times. Just make sure to resort each one so the order of the words changes and you don’t end up with a duplicate.
I hope this helps. Please let me know if you have more questions.
Hi! Omgz, you probably think I am nuts, but am totally loving your explanations and that you respond to questions! So, took a break from working my schedules and thought ooh this would be a fun game to make for my team! I have my Sheets 1-3 done like you stated, and am now working on the board. I have the words moved over and am validating. Here is where mine is different. When I am validating by hitting F2, then F4 then Enter, it pulls up the Save File and I have to click on the file name. Is this supposed to happen on the newer editions of Excel? Just curious :) Thanks again for all that you do! You are my Rock Star!!
Hi, Michi. Are you using a laptop? Some laptops require you to also press the fn key along with the F shortcuts. Please let me know if it still gives you trouble. Thanks
How can I substitute pictures for words, I inserted pictures instead of words in column C as you did with words, however when I went to create the board and use the formula as you said it will not insert to picture when I enter. Thanks for a responds.
Hi, Tammy. This is actually something I've been pondering doing a tutorial on and I really think it's past due. The process is kind of clunky, but here's what you could try:
You'll still use the RAND function, but, replace the words with the pictures.
Make sure all the pictures are the same size - 1" x 1" is a good fit. Use the Page Layout View to make it easier to set the column and row sizes using common measurements.
Also, go to your View tab and uncheck Gridlines so that they do not show.
Make sure the squares in your Bingo Cards are a notch bigger than the pictures - something like 1.1" each. Don't forget to set the print area and page breaks for each card.
Use the Camera Tool to create linked pictures of each image. You'll do 1 image per bingo square. You'll need to do them one at a time. This will create a link to that image.
You can read about how to use the Camera Tool on my website by clicking here: yacostasolutions.com/camera-tool-in-excel-tutorial/
Or watch the tutorial on UA-cam by clicking here: ua-cam.com/video/5XULAGj4OZA/v-deo.html
It's time-consuming, but once you do 1 board, you can use Find & Replace to replace the sheet name in the cell references card by card.
Using the RAND function, make sure that each list of pictures has randomized - so you don't end up with them all being the same.
This is where it gets clunky. Excel freaks out when you use more than a few linked images in any one workbook. So, as long as the RAND function executed in each, you'll see the list of pictures has changed, but your Bingo Cards will all look the same.
Depending on how powerful your computer is, you may want to limit the number of cards that you try to do at any one time.
Save your workbook. Close it and open it. They should now all be different.
If you need more cards, execute all the RAND functions again, save, and reopen. They should all look different again.
I know this is quite lengthy. Let me know if you come across any issues or if it feels like I left something out.
I'll also ping you once we have a tutorial ready on this. I think a visual would come in handy.
Thanks
@@YAcostaSolutions Thanks for this video! I am also trying to make picture bingo. However, I don't see how Find & Replace is relevant to generating additional cards. The camera technique essentially creates another free floating image (not contained within any cell, it only references a cell for its content) whose formula only appears to be accessible if clicked on itself, not where it happens to be floating above the spreadsheet. Therefore, you can't just highlight the 5x5 or (w/e size card) beneath where the images are and do a "Find & Replace" because they're nothing more than empty cells with graphical borders. God I wish it were that easy, then we could use the workflow in this video! If you use the setup like you see here, with all visual formatting done in an initial boards tab, you have to click on each individual picture in the 2nd-nth card and change the sheet it is referencing. I found that I could just do the layout next to the randomized data in a sheet and copy the whole sheet. So now I've got a sheet for every board, instead of them all being in the first 'boards' tab. Like you say, you have to close and reopen every time for the resorting to take effect and generate your new cards. Let me know if you think I'm confused or you've since found a better way to do it. We're cooking up an activity for an online pre-K class of 35 kids! Thanks!
Hello - how can I do this with images?
what did you do to start this bingo board? do we just start making a list in section 3?
Hi, Michel’le. Thanks for watching my tutorial 🙂 There were a few steps to creating this:
Start by creating the contents that will be in Sheet1:·
* Make the list of words that you want to use and place a rand() formula next to each word
* Create a 5x5 template next to it and, in each cell, reference words in your list
You can now create copies of Sheet1:
* You should create 1 copy for each bingo board you want to make. So if you want to create 10 bingo boards, you would make 10 copies (Sheet1, Sheet2, Sheet3,…) * In each of these sheets, Sort by the column with the RAND() formula (because the numbers keep changing, each time you sort, the order of the words will be different)
Create and format the bingo boards for printing:
* Make however many 5x5 boards you want to print. Make sure to make the cells bigger as these will be for printing. * On the first board, create a link to each cell in the 5x5 template (the one made in Sheet1) and make them absolute (add the $ signs to the formula - ex $A$1)
* Once done, copy all the formulas to the 2nd board. Highlight the cells only on the second board and, using Ctr+H, replace “Sheet1” with “Sheet2” (the find & replace will apply to all the highlighted areas).
* Continue doing this for the other boards
· Make sure each board is formatted to print only 1 board per page.
I hope this helps. Please let me know if you have questions or want me to clarify something. Thanks!
@@YAcostaSolutions my question is in the video at 4:23 you copy your board to the actual bingo board but when I try to copy it it doesn't pop up like yours do in the video, so what do I press to get the whole board to copy because the 1st word copies but not the board
Hi, Michel’le. I think the issue might be that the formulas aren’t calculating. If after you dragged over, the formulas in the cells are correct, but only the first word is showing, you can try 1 of 2 things to fix:
In the Formulas tab / Calculation / Calculation Options, check to see that your spreadsheet is set to calculate automatically. And, either way, click on Calculate Sheet to see if that does the trick.
If this doesn’t fix it, then it’s likely that the cells were formatted to text before you started (when this happens you see the formula but won’t be able to get it to work). You can also test if this is the case by double-clicking in one of the cells and see if it updates.
If it looks like this is the problem, use Ctrl+A to select the entire spreadsheet and change the format of all cells to General. Then use Ctr+F (the find and replace) and change = to =. It seems weird but can fix.
I’m crossing my fingers this does the trick! Please keep me posted. Thanks
Ctrl+H, not Ctrl+F to find & replace. Sorry for that!
How do I create the board to have 1" squares?
Hi, Lori. If your version of Excel has Page Layout view (you’ll see it in your View tab), switch to that. In that view, you can adjust the rows and columns using inches. Please let me know if you need more help or have other questions. Thanks!
Hi want to make 100 tickets how i can
im having trouble with my bingo cards! every time i go to to move my one of my sheets into my bingo layout the words are not copying. instead i am getting a value of 0 !! help please! i need these cards by friday!!
Hi, Feliciamarie. I hope I'm not too late in answering your question! Make sure the cells in your bingo layouts are locked (using the $ signs). If the issue is what I'm thinking it is, take a look at roughly 4:45 mins into the tutorial.
So, create the layout for the first board, then use F4 to "lock" the cell references (it should look something like =Sheet1!$A$1). Do this to each cell of the board layout.
Then, when you copy this to the second board, select the cells in the second board and use Ctrl+H to "Find: Sheet1" and "Replace with: Sheet2" (or whatever sheet names you've used). This will update your cell references on the second board layout to point to the list on the second sheet.
I know it's a bit confusing, but I hope this helps. Let me know if you have other questions. Thanks
@@YAcostaSolutions This is happening to me as well. It's driving me crazy! I followed your directions and I keep getting 0 instead of the words
Did you figure this out? I can't!
Hi, Sonia. Sorry it took me a bit to get to your question. I typically enjoy family time on the weekends 😊
Typically this happens if the cell references aren't pointing to the right cells. In the quotes below is what I recommended before. Have you checked this? For reference, you can also find this at around 4:45 in the tutorial. Please let me know if you continue to have issues. Thanks
"Create the layout for the first board, then use F4 to "lock" the cell references (it should look something like =Sheet1!$A$1). Do this to each cell of the board layout for this first board.
Then, when you copy this to the second board, select the cells in the second board and use Ctrl+H to "Find: Sheet1" and "Replace with: Sheet2" (or whatever sheet names you've used). This will update your cell references on the second board layout to point to the list on the second sheet.
Continue doing this for each board (Replace with: Sheet3, Sheet4, so on)."
Has anyone tried to use this to develop a table that is 6x6 squares or 7 x 7 squares? I tried but the extra rows of squares simply register a "0" value in each box.
Hi, Jack. Thanks for watching 🙂 No one has mentioned to me if they've tried a board bigger than 5x5, but, remember that each board is linked to reflect what's in a "mock" board. That, in turn, is linked to the word list being randomized by the RAND function. You'll want to make that the mock board also 7x7 and link all the cells in the boards to be printed so that they mirror each other. I hope this makes sense. Please let me know if you have more questions. Thanks!
Thanks for the reply. I did get a 7X7 board laid out. I'm working at creating a larger game for big family reunion. I haven't finished everything yet, but with your video, I'm hopeful.
How fun! Let me know if there’s anything I can do to help. Hope your event is a success that leaves everyone with amazing memories!
Can I insert pictures onto the board instead of words? How?
Hi, Anna. Thanks for watching. This was tricky! I had to think about it for a while, but here’s an idea of what you can do:
•Add a 3rd column next to the words (or replace the words, if you don’t plan to use them for anything).
•In this new column, one cell at a time, and next to the random numbers and/or words, make each cell big enough to contain your entire image (a good idea is to shape each cell and image to 1”).
•Use the Camera Tool (here’s a link to a tutorial I did on the camera tool: goo.gl/GYK81m) and use it to “take a picture” of each image. This new camera tool image will be linked to the contents in that cell (so it'll change as the cell contents change). Repeat for each cell on your board.
•Then, as you sort your list using the random numbers, the pictures will also sort and update on our bingo board. And this will also be printable.
Please let me know if you still have questions and thanks for asking this question - I’ll be adding this to my calendar for a future tutorial 😉
Happy holidays!
Who said that Excel is no fun?
Right?! I’m happy you liked this. Thanks 🙂
How do you make more than 3 types of bingo cards?
Once you create the sheet1 that we made in the video, you can make as many copies as you want. You'll do this by right clicking on the tab at the bottom of your screen, choose "move or copy", then check off the box for "Create a copy." You can do this however many times you need by repeating the steps. I only did the 3 so that the video wouldn't be super long. Hope this helps.
This was mind numbing
☹️
How many words do you need to create on sheet 1 (one) e.g. 75 or does it matter? How many cards/boards should you create to assure you've captured all words for the created cards/boards?
Hi, d marie. In hindsight, I used waaaayyyy more words than was necessary in this example. 25 to 30 words will get you thousands of different combinations. If you want to be sure that each word is present, you can limit your list to 25 words and your board to 25 squares.
In fact, we've been creating themed word lists on our website. Each list is only 30 words long. You just copy and paste what you need. Take a look: yacostasolutions.com/topics/word-lists/
As a side note, if you don't see the topic that you need, please let me know so we can add.
Thanks
@@YAcostaSolutions Thank you so much for your quick response. I'm responsible for games for a baby shower in a couple of months, and your response is just what I needed. Again, thank you!!!
Great! Please let me know if you need anything else. Also, if you need help with the words, here's a link to our word list for baby showers: yacostasolutions.com/baby-shower-themed-word-list/
Hope the baby shower is a success! 🙂
Hi Opie Yasm, I ran into an issue. For the most part, my boards turned out perfectly; however, of the 25 boards, one board is the same as the first board (Sheet 1 and Sheet 15 are the same). How do I rectify this? Do I have to start from scratch? Thanks.
Hi, d marie. You shouldn’t have to start from scratch. The RAND() function refreshes itself with any action on the sheet, thus updating the order of the words. I wonder if one of the formulas didn’t have a chance to refresh before you printed or the formulas got lost? In any case, you could just refresh the RAND function in one of the sheets (you could do something as simple as type a letter somewhere on the sheet to refresh it) and then print just that one.
i literrally can NOT follow this !!!!!
i am so lost and so flipping hopeless now =(
Hi, Emily. Sorry you’re having troubles with this. At which part did you start feeling lost?