This rather "techy" blog entry is likely to interest only people who enjoy playing the New York Times game "Spelling Bee", AND who are familiar with, and can run, Microsoft Excel.

What follows is a tutorial on how to use the Excel spreadsheet to track the words you have found in Spelling Bee, and to track how many words you are still missing. It tracks words by starting-letter/length, and by 2-letter initial pairs.

Quick jumps to sections below

"Spelling Bee" is an addictive word game that is part of the New York Times Games page, which is at https://www.nytimes.com/crosswords. If you scroll down from the daily Crossword at the top, or click the three-line menu icon ("≡") at the top left, you can find "Spelling Bee". Or go to: https://www.nytimes.com/puzzles/spelling-bee.

You need to pay for a subscription to New York Times games to be able to play Spelling Bee without a limit on the number of points you can earn. A subscription to the newspaper itself doesn't get you the games.

← Example. Your objective is to make as many words as you can out of the seven letters given, but you must use the center letter. Words must contain four or more letters, and letters can be used more than once. You get one point for each four-letter word, one point per letter for longer words, and seven extra points for a "pangram" (a word containing all seven letters).

That's all there is to it! There's a new puzzle every day, and for a word maven like me, it's incredibly addictive. The letters are not randomly selected. They are carefully chosen to make the puzzle interesting. There is always at least one "pangram", a word that contains all the letters. If a pangram contains all the letters exactly once, it is called a "perfect pangram".

The particular puzzle shown above appeared on November 11, 2021, and I'll use it as an example throughout this blog entry. Below is how it actually appeared on the web, slightly compressed to keep it within the 600-pixel width I use for my blog entries. I'm showing how it looked after I had found 23 words. These included the pangram "Locomotion". My score at that point, 85, is shown in the yellow circle above the word list. When actually playing (not in this screen-capture), you can click on that yellow circle to see your ranking, based on the percentage of possible points that you have obtained. ^Note 1

I should note that the screen images I show in this blog entry, and the instructions which follow on how to use my Excel spreadsheet, assume you are using a desktop or laptop computer. The game can also be played on a tablet computer or a smartphone. But it will look different, and I'm not sure if the spreadsheet described below can be used on such devices.

Although I always start playing without reference to "Today's Hints", I eventually click on "Today's Hints" (above the word list and the Rankings) when it starts to take me longer and longer to find additional words. It opens in a separate window - here's what it showed me for the 11/11/21 puzzle:

Recall above that I had found 23 words; the Hints show me that these are 23 out of 30, so there are only 7 words left to find. But as I search for these increasingly-hard-to-find words, the Hints keep me from wasting potentially enormous amounts of time searching dead ends. For example, I might spend some time looking for a word starting with the letter "I". A quick glance at the Hints tells me there are no such words!

Similarly, the Hints tell me that there are only 5 words starting with "L". Looking at my list, I've found them all (Limit, Limn, Limo, Locomotion, and Loom), and there's no need to search for more. There are only 1 each of words starting with N, O, and T. I've found the N-word (Noncom) and the O-word (Omit), but it's worth trying to find the T-word. As you can see, I don't look upon using the Hints as cheating. I consider it saving me from banging my head against a brick wall.

Hence the spreadsheet! It keeps me informed as to what remaining words are worth looking for! And if I can write a program I need using a spreadsheet, it's easier than other forms of programming. Anyone reading this is free to use my spreadsheet as well, but you need to already be familiar with Microsoft Excel in order to do so. I don't have the time to teach people Excel. The spreadsheet is best run on a desktop or laptop computer, with a browser running Spelling Bee in one window on the desktop, and the spreadsheet running in another window.

Note that most of the spreadsheet is "protected", meaning you are not allowed to make changes to the cells. All the unprotected cells, the ones you can change, have a light-salmon background, so you can see where they are.

The upper-left corner of the "SpellingBee stats TEMPLATE" spreadsheet initially looks as shown below:

For the November 28, 2021 Spelling Bee that I'm using as an example, I performed the following steps:

First, I saved the spreadsheet under a new name, to preserve the TEMPLATE version for future use. I used "SpellingBee stats 2011-11-11.xslx".

Next, I typed "11/11" into cell A2. When I hit "Enter", the spreadsheet reformatted that date into "2021-11-11" (adding the current year, which was 2021 at the time).

Then, I copied the seven letters of the day from the HINTS window, "M C I L N O T", into cells F3:L3 (not case-sensitive). The result of these operations, just showing the "Length" array, were as follows:

Note that the spreadsheet has alphabetized the letters of the day, and used them to label the rows of the "Length" array. The date of the puzzle being solved is shown in cell A2.

That completes the basic setup.

As you work on the puzzle, keep Spelling Bee and the spreadsheet open in 2 separate windows. Periodically copy the word list into the spreadsheet, starting at cell A26. This is done as follows:

First, click in the Spelling Bee window. Then, carefully wipe your cursor over the word list to select it. Finally, right-click and select "Copy" (preferred), or type <Ctrl-c>, to copy the list. ^Note 2 

Then click in the spreadsheet window. Select cell A26 (the first cell of the spreadsheet’s word list) by clicking in that cell:

Next, up at the top, click the down-triangle under "Paste". In the sub-menu that drops down, under "Paste Options", click on “Match Destination Formatting” (the right-hand selection):

(<Ctrl-v> or “Paste” work, but mess up the formatting in the list.)

Once your list of words found has been pasted into the spreadsheet, the spreadsheet does its thing. Using formulas to the right of the word list, it calculates how many words have been found of each length, and displays this in the "Lengths of found words" table. It also calculates the number of words starting with each two-letter combination, and shows these in the "Two-letter list, found words" table. Any pangram is highlighted in bold in the word list:

It also displays, on the second line of the spreadsheet, the number of words in the found list, the number of points these are worth, the number of pangrams in the list, and the number of these that are "perfect pangrams". As a check on the copying operation, it's worth comparing the number of words and points with the number shown on the Spelling Bee page:

I used an earlier version of the spreadsheet, as described above, for a while, and found myself constantly subtracting the words of various lengths I had found so far from the TOTALS in the hints. Obviously, I decided that THE SPREADSHEET OUGHT TO DO THAT!

So I added a couple of spreadsheet sections to the right to perform these calculations. Step 1: there has to be a place to enter the TOTALS given to you on the "Today's Hints" Spelling Bee page. Scroll way to the right - these are below and to the right of cell AC1.

First, copy the maximum possible ("TOTAL") number of words, points, and pangrams from the Hints into the spreadsheet, as shown below. If any "perfect" pangrams are possible, that will be shown in the hints - copy that as well. If no perfect pangrams are shown, you can enter "0" in cell AL3 (to the left of "perfect"), or just leave it blank - it will be taken as a zero:

Next, enter the TOTAL number of possible words of various lengths in the table a bit lower down. The copying of the first line is illustrated below. Be careful: the "Today's Hints" page often omits columns and rows that are all zero, but these are never omitted from the spreadsheet's table - you just have to leave them blank. In the example I'm using in this blog entry, the puzzle of November 11, 2021, there were no words of length 8, so that column is omitted in the Hints, and there were no words starting with I, so that row is omitted in the Hints. But that column and that row ARE included in the spreadsheet's table.

I've only shown below, in red, how to copy the first line. Still, I think the correspondence is obvious - you can easily see how to do all the lines beyond the first. The spreadsheet will calculate the row and column sums, labeled Σ ("sigma"), which include the grand total at the lower-right corner of the table. Compare them between the Hints and the spreadsheet, to verify that you haven't made any mistakes:

Finally, set up the "Two-letter List". Again, I think the correspondence is obvious - you can easily see how to do all the lines beyond the first two that I've highlighted in red:

Save the spreadsheet once this section has been set up. Then, as long as you have updated the word list, the section of the spreadsheet below and to the right of cell O1 will show you the lengths of the words you are missing (by starting letter), and the number of words starting with each letter pair. At this point in my example puzzle, you can see that I've found 23 words, worth 85 points, including one pangram. The leaves 7 missing words, worth 39 points. Zero pangrams are missing, so there are no more to be found:

In particular, at this point in my solution, I can see from the "Lengths" table (see above) that only seven words remain to be found. Six start with "M" (the required letter): one each of lengths 4, 6, 7, and 9, and two of length 5. Of these, the "Two-letter list" reveals that three start with "MI", and three start with "MO".

In addition to the six missing words that start with "M", I'm missing a 6-letter word starting with "TO".

The next day, November 12, 2021, I clicked "Yesterday's Answers". If you're curious about the seven words that I missed, click Note 3.

On the left side of your Excel window, just a bit down from the top, is what is called Excel's "Name Box". The upper-left corner of an Excel window is shown on the left below, and the Name Box contains "A14", which happened to be the cell selected when I did this screen capture. If you click on the down-triangle that I circled in red, you get what you see on the right: a menu drops down with four selections, "Found", "Instructions", "Missing", and "Total". Try it - each will select one of the major areas of the spreadsheet.

Should you want to print any of these selections (or any selection, for that matter), you can select FILE > Print, and then under "Settings", pull down the sub-menu and choose "Print Selection".

However, I don't usually get around the spreadsheet that way. I've found that if I size my Excel window to be just a little bit wider than each of these selections, I can move left and right by clicking to the left and right, respectively, of the horizontal scroll bar. Try it!

Cells E4:K4 are not protected, to allow you to write yourself a very short note.

The spreadsheet allows for up to 100 words of up to 14 letters each. I've been playing Spelling Bee for over a month, and this has always been sufficient. Longer words can be entered into the word list, and they will be scored properly. But they won't show up in the Length table.

Since I assume the word list will be copied from the Spelling Bee list, the spreadsheet does no checking to see if a word entered is legal (meaning 4 or more letters, containing the required letter, and containing only the allowed letters). Spelling Bee will check that for you. But it is possible to type your own words into the list, if you want. And you are allowed to use Excel's "Sort" function to sort the list if you want to.

A day after the puzzle is first posted, when the answers are available, I usually type the words I missed into the list. But I leave a couple of blank lines above these missed words, to make it clear which ones I got and which ones I didn't. I can then save the puzzle for future reference. It doesn't matter to the spreadsheet if there are gaps in the list. The words in the list don't have to be contiguous, and entries are not case sensitive.

The copying operation can be a bit tricky, and I've seen it result in a blank line at the top of the list (cell A26). This is usually not a problem. But if there is a discrepancy between the number of words and points reported by Spelling Bee and the number of words and points reported by the spreadsheet, check that everything is OK at the bottom of the list.

You need to also be sure that you've copied the ENTIRE list. If your list spills over into another column, and your Spelling Bee window is too narrow, Spelling Bee may not display the all the columns at once, and you might not pick up all the words.

There's one final quirk, not of the spreadsheet, but of Spelling Bee itself. If I copy the Spelling Bee word list with a <Ctrl-c>, Spelling Bee acts as if I had typed a "C" into the word above the allowed letters (remember, you can enter letters either by typing them, or clicking on the letters in the "beehive"). I consider this to be a Spelling Bee bug, albeit a minor one. I didn't type a "C", I typed a <Ctrl-c>, and in the computer world, those are two distinct beasts. But this is not a major problem; I just delete the "C", either with a Backspace, or by clicking on "Delete".

Some of you might want to poke around the spreadsheet and see how it does its magic. The spreadsheet's operation should mostly be fairly obvious, but there are a few tricks that might be hard to find. These primarily involve what is called "Conditional Formatting":

In many cells, the cell appears empty if the value it contains is zero. This is done by using Conditional Formatting to cause the cell's contents to be displayed in the same color as the cell's background if the value in the cell is zero. This makes the cell appear empty.

A similar Conditional Formatting trick is used to cause pangrams to be shown in bold in the word list. If a pangram is found, putting a "1" in column F, the word in column A is formatted in bold.

Phew - this took a LOT longer than I expected. I sure hope that at least one person other than me uses this thing.
 

Note 1:   The ranking displayed is based on a percentage of the total possible points. When you have earned less than 2% of the possible points, you are a "Beginner". As you start accumulating points, your ranking increases as follows:

Note 2:   If you copy with <Ctrl-c>, Spelling Bee acts as if you had typed the letter "C" into its left-hand pane, and you will have to delete it. I'd call this a bug, although it's not very important. But you might prefer to use <right-click, Copy> to copy the list, which doesn't have that side-effect.  [return to text]

Note 3:   The words that I missed in the Spelling Bee puzzle of November 11, 2021 were Mimic, Minim, Minion, Mono, Monotonic, Moonlit, and Tomtit. I had to look up "Minim", a musical term. In British English, it's the equivalent of what's called a "half note" in American English.  [return to text]