This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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

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,

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,

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.

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

Some other pages relevant to the above blog include:

Microsoft Excel training

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Building a word search in Excel without macros

Head office

London

Manchester