WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 520 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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:

One suggested solution for a set of related words.

To this:

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.