I started the first article in this series, with a surprising piece of trivia – that Wordle was the most searched word or phrase on Google in 2022. I can’t predict the future (sadly), but I’d wager a few notes that ChatGPT is the most searched term in 2023. If you haven’t heard of ChatGPT, don’t worry, you will, and soon!
In the previous article, we had some fun making a very simple version of Wordle in Excel and without writing any code. If you’re here after reading that article, welcome back! If not, I suggest you have a quick look now.
In this follow-up, we will add a few more features to our Wordle app to actually make it playable. Plus, I thought it would be interesting to see if and how ChatGPT might help us in developing this solution.
Remember that the goal isn’t to make an app to rival the New York Times version, but rather to use this mini-project to introduce some techniques in Excel that you mightn’t be familiar with. And also it’s a little experiment to see how Artificial Intelligence tools such as ChatGPT can help us out with writing formulae and other tasks.
Where we left off, we had the Wordle playing grid with the cell background colours updating with each guess.
Now, to make the game playable, I aim now to introduce the following elements:
- A dictionary of 5 letter words
- A new Wordle each day
- A check for invalid words
The list of 5 letter words seems like a great first task for ChatGPT, so I went to openai.com and started a new chat.
My first attempt looked pretty good, but the selection of words it gave me was alphabetical running only from A through to C whereas I need a random selection. So I added a little more detail to my request.
A couple of points to note here. Firstly, I’m being very conversational in my requests, and that’s absolutely not a problem. However, it is important to be precise in the requests, and to check the results that come back. Even a precise request can yield false results. For example, I was genuinely surprised when one of my requests also included some four-letter words!
Secondly, note that you don’t have to ask the entire question each time. You can add supplementary information until the results obtained correspond to what you’re looking for.
For example, as a final refinement, I added a precision that would make the list easier for me to use instantly in Excel.
You will probably have to go back and forth before getting precisely the result you need, and of course where I have asked for 100 words, you may wish to ask for many more. You could even ask for all valid 5 letter words in English (or another language!), but it would take some time to generate, and to subsequently check.
Once I’m happy with the results, I simply copy and paste them to a new sheet I’ve called Dictionary in our Workbook, starting in Cell A1. Also from Cell A1, press Ctrl-T to convert this data into a table without headers, called Wordlist. (You can name the table by selecting it and going to the Table Design menu.)
I then asked ChatGPT to provide me with a formula to count the number of words in our table (as I want something dynamic that allows us to add to take away rows.) We’ll need this formula in a little while.
Before coming up with that response, however, he first proposed =ROWS(Wordlist)-1 which is actually incorrect, as the ROWS function doesn’t count the header row of a table.
The lesson here is that if you feel ChatGPT is not giving you a correct answer, don’t be afraid to challenge it. (Unbelievably, Google’s parent company Alphabet recently had 100 billion dollars wiped off its stock price, after failing to fact check its own AI tool at its first product demo!)
Generating the Wordle of the Day
Next we will replace the word in cell A1 of our main worksheet with a formula to pick a word from the word list which updates with a different word each day.
To achieve this, the first thing to understand is that Excel stores dates as numbers. Thus, the function =TODAY() will return as I write 16/02/2023 or 44973 if I format the cell as a number. Tomorrow will be 44974 etc. We can use this fact to construct the following formula to return a different word each day.
I know the basic approach I want to use for this, but instead of trying to work this out myself, I thought I would delegate to ChatGPT to see if it could come close.
From these fairly specific (yet informal) instructions, ChatGPT was ALMOST able to generate the formula I needed out of the box.
In order to have it function correctly I had to add a “+1” to the formula, so that the result of the MOD function was never zero. I could have debated with ChatGPT until it gave me the right answer. Instead, I used some good old-fashioned debugging.
The corrected version of the formula to be entered in cell A1 is:
=SUM(INDEX(Wordlist[Words], MOD(TODAY(), ROWS(Wordlist))+1, 1))
Around about this time, I realise 2 things; firstly, that I should have asked ChatGPT for words in capital letters (although it doesn’t make any real difference), and secondly, that I should have specified that I didn’t want any words to appear more than once in the list (there are several duplicate entries.) I decide to correct these myself rather than bothering ChatGPT. Sometimes if you want a job done properly, you’re better off doing it yourself 😉
The next step is to hide the first two rows, so that the Wordle is not visible to the player. I do this by selecting the rows, and right click -> Hide. At this point, I also decide to clear my guesses and save the file, so that upon opening each day, the grid is empty (generously assuming the player doesn’t save it after playing!) I even remember to turn off AutoSave and feel quite pleased with myself.
Flagging invalid words
From our initial set of objectives, we now just need to implement functionality to highlight an invalid word i.e. a word that is not in our table Wordlist. This functionality is likely to be above all frustrating unless you generate a large list of valid words. Otherwise perfectly good words will be highlighted as they are not present in our list.
For this task, we’re going to use conditional formatting, as we did in the first article. We want to change the font colour of guesses which are not in the WordList. Let’s see what ChatGPT suggests.
There are several functions that could be used to achieve a valid result so I’m not even surprised when ChatGPT rapidly proposes one that looks like it should work.
Unfortunately, Excel doesn’t appear to allow references to a table column within a Conditional Formatting formula. I try explaining this to ChatGPT but it doesn’t believe me ☹. As I don’t want to hard code the range (A2:A101), instead I create a named range which points to the table. This is done from the Formula menu / Define Name.
I then modify the formula from ChatGPT to read: =COUNTIF(WordRange, H4)=0 and this works perfectly.
Conclusion
Collaborating with ChatGPT on this project is a bit like having a stagiaire / intern straight out of college working for me. I know the approach I want to take, but am glad to delegate those tasks that could be done more quickly by a younger, fresher version of me. On the downside, this particular intern showing their inexperience and probably keen to impress, quickly comes back confidently with solutions which are not sufficiently tested, and which are sometimes downright incorrect.
So I can’t put my feet up just yet. I have to check all their work. But who knows what my little helper will be able to do with a couple of years more experience! They could be coming after my job!
Please get in touch if you have any questions or comments. I’ll be very glad to hear from you.
David McKinney, Senior Business Analyst at Taleo Consulting.