Tutorial: Excel Basics

Before going into my advanced construction tutorials, I want to cover a handful of Excel functions I find the most useful for these exercises. 

Learning Python has long been on my list of things I should do that I never get around to. However, while coding would certainly simplify some things, you can still accomplish most wordlist manipulation with some basic Excel skills. Here I'll cover the various functions I use most, since they will appear repeatedly in my tutorials.

LEN

This is a very simple function that returns the number of characters in any given cell. So if I enter =LEN(A1) it will tell me the length of the word in cell A1. While this can be somewhat useful for sorting potential theme answers for symmetric options, it can also be leveraged in other nested calculations.

LEFT/RIGHT

These functions returns a string of X number of characters starting from either the beginning or end of a given cell. So if I wanted to see the first three letters of cell A1 I would enter =LEFT(A1,3). And if I wanted to see the last four letters I would enter =RIGHT(A1,4).  These functions can also be used together to return a specific character in a given word. So if I wanted to return the 5th character in cell A1 I could use the formula =RIGHT(LEFT(A1,5),1).

I don't usually need these for wordlist encoding, but I've periodically found them useful when mining for certain themes. For example, you could search for answers that, say, begin and end with the same three characters using a formula with the logic RIGHT(A1,3)=LEFT(A1,3).

SUBSTITUTE

This function replaces one string of characters with another. So if I wanted to replace the X's in cell A1 with Y's I would enter =SUBSTITUTE(A1,"X","Y"). This basic swap can be also be done using the Find/Replace feature, but the formula also has additional functionality. 

1) You can optionally specify which instance to substitute, for cases where there are multiple letters being replaced. For example, =SUBSTITUTE(A1,"I","X",3) would change the 3rd "I" in a cell to an "X" (MISSISSIPPI → MISSISSXPPI)

2) You can nest it with other functions -- combining it with the LEN formula can get you the count of any character within a word. For example, =LEN(A1)-LEN(SUBSTITUTE(A1,"X","")) would return how many X's are in A1 by looking at the length after replacing the X's with nothing.

VLOOKUP

This is probably the most useful formula for manipulating wordlists in Excel. What it does is check one list of cells against another to see if there are any matches. If a match is found, you can return either that matched cell OR another value associated with that matched cell.

For example, let's say we had a list of celebrities and we wanted to populate a column with their favorite sandwiches according to different table:

In the formula above (=VLOOKUP(A2,$E$1:$F$5,2,FALSE) the blue cell is the value we want to look up and the red cells are the table we are looking in. If it finds the blue cell in the first column of our red table, then it will return the value in the 2nd column of the table. If not it will return a "#N/A". 

Note: this formula is case-sensitive, so watch your capitalization. Also, the "FALSE" string in our formula indicates that we only want an exact match).

This formula is extremely useful since we will use it often to check if our wordlist manipulations are yielding valid results.

Locking Cell References

By default, when you copy and paste a formula, any cell references adjust as you move the formula. So when I pasted the above formula down to the following row, the A2 cell reference would change to A3. This is the desired behavior because on the next row we want it to look up "Tom Hanks" and not "Meryl Streep" again.

However, we still want to look it up in the same red table. So when copying that formula down, we do not want it to change E1:F5 to E2:F6. To prevent that we can lock those cells in place using $'s. On a PC the F4 key is a shortcut to lock the current cells in place.

Adding/Using Filters

Adding a filter at the top of a table can be useful to narrow down your results. In the above example I might want to filter out the results without matches. To do this I would highlight the table headers and select the "Filter" button:

Then, using the drop down, I could unselect the "#N/A" results to only show rows where we had a match:

Pasting Values

Wordlists can get pretty large, so creating formulas that span an entire wordlist can be a little taxing on a program like Excel. To ease that strain and minimize the risk of the program crashing, it's a good idea to copy and paste values after running a large calculation.

What that means is replacing thousands of formulas being calculated with just the text results of those calculations. To do that you would select your long column of calculations, hit "copy", and then select 'paste values' under the list of paste options:

This isn't always necessary, but if you notice your computer struggling a bit it can be very helpful.