Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.I needed to create some wordsearches recently. So I turned to Excel.
I created two identically-sized grids: a blank one in which I could type the words I wanted to appear in their rightful positions; and another one next to it, which would surround the keywords with randomised letters.
And here's the result. I've shaded the keywords on the right for visual effect. The shading isn't there in the finished product, because that would make the wordsearch a bit rubbish.
The formula of choice is as follows:
=IF(ISBLANK(B4),VLOOKUP(ROUNDDOWN((RAND()*1012)+1,0),Lookups!$F:$G,2,0),B4)
The range in the Lookups sheet lists the letters of the alphabet a variable number of times depending on their propensity to appear in the English language. (E.g. A appears roughly 82 times in 1012, or 8.1% of the time.
Here's the spreadsheet should you want to use it.
Pingback: Excel Roundup 20170601 – Contextures Blog
great
hear is also the solver to your puzzle just create conditional formatting on the puzzle grid to highlight the cell matching all the cells in the grid.
Do this for 6 consecutive cells. so whenever you type some characters on each cell the relevant grid cells get highlighted
Amazing!
I needed a word search puzzle for my baby boys name party and found this one. Awesome!
Few things still bothers me (though I got it working).
1. Why 1012?
2. Did you have some premade list of the proportions for the letters?
3. I'm Finnish (Finland, EU), so I needed to add Å, Ä and Ö in the list. I just added them after Z (which is the correct place for them in our alphabets) and then just modified the amounts with figurative numbers for our letters and copied the missing cells from previous row, so that the sum for relative frequency was eventually 1012 as you had. Everything seems to work just fine, but I'd like to get more information about the logic behind the formula.
Once again, thanks a LOT for this. Totally saved my day!
Hi Matti,
If I remember, I think I used 1012 as that was a rough number into which all of the percentages would divide. That was all. 🙂
Glad it helped.
Dan.
How can I add under each puzzle a solved puzzle (solution) in this excel file, any help? https://social.technet.microsoft.com/wiki/contents/articles/52660.excel-vba-wordsearch-creator.aspx
This is super useful! Thank you for sharing.