It’s not exactly the same as Wordle but close enough 🙂
Excel File
I added additional conditional formatting and data validation in version 5:
If you have issues with the macros watch this video from MrExcel.
Challenge
I kept thinking: Can I recreate Wordle in Excel? It was easier to build this than play it! I guess I’m not good with words.
How to Play
1. In the top left click the ‘New Game’ button to start the timer.
2. One letter at a time type in a 5 letter word starting at the top (Try1).
- Green letter = the letter is in the answer word and is in the correct position.
- Orange letter = the letter is in the answer word but not in the correct position.
- Dark grey letter = the letter is not in the word.
3. Add words until you guess the answer.
4. In the top left click the ‘End Game’ button to stop the timer.
A few details:
- Letters at the bottom aren’t buttons (used to display each letter’s status).
- Conditional formatting rules may take time to update. Note sure why yet.
How Does It Work?
It’s a mix of formulas, vba, and conditional formatting. Unhide sheets words and letters to see more.
Sheet words
- The 5 letter words are in column B.
- vba takes random cell E1 word and pastes it as a value in cell E3.
I created two areas of formulas that the conditional formatting rules use:
…and this one:
Sheet letters
I thought of using buttons or text boxes but it’s easier to use ordinary cells.
Here’s a look at the conditional formatting set-up:
…and the conditional formatting formulas:
=AND(MATCH(B2,words!$I$10:$M$10,0)=MATCH(B2,words!$I$3:$M$3,0),B2<>"") =AND(ISNUMBER(MATCH(B2,words!$I$10:$M$10,0)),ISNUMBER(MATCH(B2,words!$I$3:$M$3,0)),B2<>"") =AND(B2<>"",INDEX(words!$H$14:$H$39,MATCH(B2,words!$G$14:$G$39,0)))
I then created three picture links and pasted them into sheet Play.
The vba is nothing fancy but needed for a few tasks:
- setting up a new game (clearing board, starting timer, etc)
- a 2nd button for stopping the timer
Sub Button1_Click()
Application.ScreenUpdating = False
Call CopyPasteTimeStart
Call ClearBoard
Range("B5").ClearContents
Call AddNewWord
Sheet2.Activate
Application.ScreenUpdating = True
End Sub
Sub CopyPasteTimeStart()
Range("A1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
End Sub
Sub ClearBoard()
Range("All").ClearContents
Range("StartLetter").Activate
End Sub
Sub Button2_Click()
Range("A1").Select
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
End Sub
Sub AddNewWord()
Sheet1.Activate
Range("E1").Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
End Sub
MrExcel’s Helper File
MrExcel created an Excel spreadsheet that provides hints for the game:
Solve WORDLE Faster with our FREE Wordle Helper in Excel – MrExcel News – MrExcel Publishing
I enjoyed auditing his formulas. Some thoughts:
- Dynamic arrays are amazing and have changed Excel forever!
- Xlookup & Sign: =1-SIGN(SUM(XLOOKUP(B8:F8,$Q$9:$Q$34,$R$9:$R$34)))
- Why have I never used the Sign function?
- Formula =FILTER($Q$9:$Q$34,S9:S34,”*”) uses wildcard!
My Best Wordle Today!
Early this morning I had my best wordle game ever solving it in 3 steps!
Here’s another video from MrExcel: World Best Starting Words
About MrExcel (aka Bill Jelen)
Bill Jelen created his help forum over 20 years ago. It’s an incredible free source to answer Excel related questions. Bill also writes/publishes books, offers consulting services, and has a popular YouTube channel.
I attended one of his Excel seminars in 2015:
About Me
I can’t count the number of times I’ve thought “Could I make this in Excel?“
My name is Kevin Lehrbass. I’m a data analyst.
My curiosity has kept me learning for many years now. I also have to keep up with all the changes in Excel. So many new features and functions.
Pingback: Security Risk Microsoft Has Blocked Macros From Running - 2485
Thanks for sharing Kevin.
I’m not trying to criticise below, please don’t take it as such. This game just has so much more potential.
Some recommendations for version 6:
– How about locking down all cells so the user can’t click or move stuff around and you can just keep the current guess’s cells unlocked. For the sake of keeping it “open source” you don’t even have to lock it with a password. When the current guess is finalised (USER presses or clicks on Enter / Line break) it locks those cells and unlocks the next row’s cells. You can use the Worksheet_SelectionChange and Application.Onkey properties and methods to achieve this.
– You can also take keyboard inputs and UPPERCASE it automatically and auto advance the cell selected so it works like Wordle.
– It currently accepts words that are not words so that can be improved.
– It currently incorrectly highlights letters that are already highlighted. If the word is TOUGH for instance and I guess GOUGH then the first G will be highlighted yellow which should indicate that G is found in the word twice which is not the case.
– You can use the Camera feature instead of a linked picture. That might improve on the conditional formatting update speed because I don’t think the Camera needs a calculation to update. I’m guessing the formatting formula takes a while to calculate if typing quickly.
– When I start with “STARE” on guess 1 the “R” formats before the word is completed.
– You could also add empty shapes on top of the letters so the user could click then for corresponding letters. This would make it function like the Wordle keyboard. This can be managed with the Protect Sheet properties as well.
– The timer can also be automated to start when the new game button is pressed and ends when a game is won or when the guesses are finished. The less buttons there are the better.
– I would also reduce selection of sheets and objects. Instead of
Range(“A1”).Select
Selection.Copy
Range(“B4”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
you could use:
Range(“A1”).Copy
Range(“B4”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
and:
Sheet1.Activate
Range(“E1”).Select
Selection.Copy
Range(“E3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
can become:
With Sheet1
.Range(“E1”).Copy
.Range(“E3”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
End With
Then you also don’t really need to change the ScreenUpdating property on the Application object. You can also then remove all .Select and all .Activates in the workbook.
Happy to help you with these if you want. Keep up the good work and keep sharing please!
Hi Renier,
Yes, there are a lot of ways to improve it. At some point I hope to have a quiet afternoon and make more changes. Thanks for the suggestions and for visiting my blog!
Cheers,
Kevin
Hi Kevin,
This was the first time ever I played Wordle. I’m not sure if this is just something you shouldn’t do but I tried a word with 2 of the same letter and I assumed the correct word had them both (as both went orange). Turned out that wasn’t the case so not sure how it’s meant to handle these situations. Here is the game I played:
1 2 3 4 5
Try 1 a p p l e
Try 2 g r a c e
Try 3 t a r r y
Try 4 r a y o r
Try 5 r a y o n You win!
Try 6
By the way thank you and best wishes,
John
Thanks John.
I’ll try to review this on the weekend.
Thanks for visiting my blog.
Cheers,
Kevin
Hi John,
You’re right about the double letter guess…when both are in wrong location and answer word only has one. Only one of the should highlight. Good catch!
I think I’ll wait a few more weeks and keep adding these things to a list and finally/hopefully make another version.
The stats part interests me…that would involve keeping track of all games. Definitely possible but would take a vba mortal like me many hours. Best for a cold winter day but now it’s been really warm here in the Toronto area so I’ve been outside a lot.
Once again, thanks for visiting my blog John!
Cheers,
Kevin