We’re going to create a custom word search puzzle and you’ll see how easy this is to create. All you need is a section designated in your Excel. So you see here how I have already added borders to this 7 x 20 section. And then this particular word search is going to be, um, for a bridal shower. So, I already have 10 words here related to weddings and things. Um, obviously, you can do this for whatever theme you’re, you know, you’re wanting to do this for. But this particular one is going to just have some wedding words. So, to do this, we need to do 2 different things. We need, one, to add random letters to the cells in the area within our border and then, we’re going to have to separate all of these words so that each letter of the word is in its own cell.
The first formula we’re going to use is a version of the RAND(), um, Random formula. So, which involves characters. So this is =CHAR and we’re going to use RANDBETWEEN. Once you have your parentheses, then parentheses again, your bottom number is going to be 65, your top number is 90. Now, the reason we do this is is, on the backend, Excel assigns a numerical value to each letter of the alphabet and the first letter, A, starts with number 65. Now, you don’t really need to remember this if you don’t want to. What I’ll do is, because this is kind of a weird one, I’ll make sure to add it to the description of this video. So when you’re done here, just make sure to read the description and then you can just copy and paste is formula and use it on your own, um, spreadsheet.
So, we hit Enter and so we have a letter. And then we want to just copy and this formula in our entire bordered area. So, I’m going to right click and I’m going to, on the Paste options, we’re going to Paste as Formulas and so now everything’s full and it’s formatted centered, the way that I prefer. So now that we’ve filled everything, we’re going to copy it yet again. And we’ve copied. And we want to Paste, but this time as values. The reason for that is is because the RAND formulas, they refresh any time you do anything in your worksheet.
And I just want them to stay static. So now they’re just random letters. The next thing we’re going to do is separate these words into their own cells. So we’re just going to copy them over here – this’ll just be our working area for now. And, so, I’m going to line them all up so that they’re in one column; This’ll make it easier for us to use this formula. Now, this is kind of a long formula, so bear with me. So we’re going to use the MID formula and then, for the text, we’re going to choose, um, the word to the left and we’re going to make that column static.
And then after that, we need to add the COLUMNS function. Now, this is a reference function in Excel, so it just lets it know, um, you know which column you’re referencing and it’ll help you split out the letters. So, we’re going to, again, choose this and we’re going to, again, make the column static, so, the $ sign helps to do that. It’ll make you do, if you add a $ sign to the left of the K, it’ll make that column, um, so it doesn’t change as you copy the formula over in a moment here. So… that and then we just want to type that same number again and then close that parentheses. You’re going to add another comma and then now this takes you back to the original MID formula. And for the number of characters, we’re going to type 1 – because we only want one letter per cell. So any more than that and you get multiples. So now you see G for GROOM.
And now we’re just going to copy this over so that we can capture all the letters. And now we did that, and everything is broken out into its own cell. And now we’re just going to copy and make this into values as well. That’ll make it easier when we go to a paste it into our area. And I’m going to format this the same way as the wordsearch, so it’s a little bit easier. So then, all I do from here is just copy and paste.
Um, for me, I’m going to initially highlight these in yellow; it just makes it easier for me to know where it is. So we pasted this one in. Then let’s say we want to do one horizontally, um, I’m sorry, vertically. So we’re going to just go into this…any cell and I’m going to Paste Special – and I’m going to do this as Values because if not it’ll actually erase the borders. So I’m going to do this as Values and Transpose – now, Transpose takes whatever you’re copying and either goes from vertical to horizontal or horizontal to vertically. So, we’re going to Transpose that and you see how BRIDE is now vertically in our word search. So, I’m going to do this to all of the words, so hang on, will be right back.
I’ve now added all of the words to our puzzle and everything is highlighted so the next thing we want to do is just clean it up. We’re going to delete all our working area. We will unhighlight this. This how you create a custom word search.