Browse 549 attributed reviews, viewable separately for our classroom and online training
How to build an Excel word search without even using VBA
Details of an impressive new Excel word search system which allows you to recreate a randomised grid every time you recalculate your formulae!

Posted by Andy Brown on 23 February 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Building a word search in Excel without macros

So last month I shared WOWSER (the Wise Owl Word Search Excel Replicator) using VBA macros.  Andrew Howe of Transformaction asked about a non-macro version of a word search in Excel, and I - foolishly - expressed my opinion that it couldn't be done. 

I reckoned without Excel formulae like this:

=IF(

target_spills,

0,

LET(

target,

INDEX(

grid_prev,

target_top_left_cellno + AbsOffsets0Arr

),

word,

IF(

K$4>0,

WordArr,

WordArrRev

),

IF(

SUM((LEN(target)>0)*(target<>word))>0,

0,

1 + SUM((LEN(target)>0)*(target=word))

)

)

)

This would be impressive enough, but many of the range names (shown in red above) are formulae themselves.  For example, here's the formula for the range that the target_spills range above references:

=AND(Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)>=1,Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)<=Rows,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)>=1,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)<=Cols)

In addition to all of this, Andrew has included every Excel trick in the book, including the ones shown in the table below.

Trick Notes
The INDIRECT function This can always be relied upon to do complicated things in Excel (the downside is that the resulting formulae can be very difficult to understand and test).
The LET function A new Excel feature allowing you to use variables in Excel (I blogged about it recently).
Lambda functions An even newer Excel feature which allows you to create your own functions in Excel without using VBA, like this.
The FILTER function The new Excel dynamic array functions allow you to do all sorts of clever filtering, avoiding messy (and slow) SUMPRODUCT functions.
Relative ranges Most people don't even realise that a range reference is stored as a formula rather than as an absolute reference (it's what allows you to create fancy things like dynamic range names in Excel).

The results are impressive - there's something very satisfying about being able to press F9 to recalculate a workbook and seeing this:

A suggested word

One suggested solution for a set of related words.

To this:

Another solution

Same grid size, same words ... totally different solution!

You can see how the algorithm works, how the formulae were built and other (much-needed!) documentation at Andrew's word search Excel lab page.

A word of advice: make yourself a strong cup of coffee before visiting this page!

This blog has 0 threads Add post