Excel wordsearch creator

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.

This entry was posted in Tools. Bookmark the permalink.

5 Responses to Excel wordsearch creator

  1. Pingback: Excel Roundup 20170601 – Contextures Blog

  2. SHRIKANT VIRUPAKSHA KARPUR says:

    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

  3. Matti Parviainen says:

    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!

    • Dan says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.