Play-It-Through Chess Game in a Single Google Sheets Formula

In 1956, a 13-year old Bobby Fischer announced his chess genius to the world, winning one of the finest games in chess history. In fact, Chess Review called it “The Game of the Century“.

In a game full of beauty and surprise, Fischer overcame his much older, more experienced opponent, the International Master Donald Byrne.

In this video and blog post, we create a single formula in Google Sheets that plays through this game!

Backstory

Earlier this month, doing research for my video about conditional formatting, I came across this comment on my blog post on this topic:

This comment really piqued my interest.

I experimented with it, then remembered that I can generate chess pieces with the CHAR function.

So after an hour of messing around with formulas, I had a single formula that created a chessboard. I shared it on LinkedIn and Twitter, where it was well received.

But me being me, I wasn’t quite done yet. I wondered if I could make the pieces move.🤔

Here’s how it went…

Creating the Chessboard Background

Use one of these conditional formatting rule to create the background board:

or

depending on which cell you start your 8 by 8 board from.

If the row + column number of this top left cell add to an even number, use the ISODD rule (e.g. A1 is 1st row, 1st column, which add to 2, which is even). However, if the row number + column number add to an odd number, use the ISEVEN rule.

Note: you can of course manually change the background colors 😉

Add the Chess Pieces

The first thing we need is the CHAR function, which generates the chess pieces.

E.g. this CHAR formula generates the white king:

In our Google Sheet, it looks like this:

Further, all the pieces can be created with the numbers 9812 through 9823:

The next function we need is the MAKEARRAY function, which generates an array r rows by c columns, with a custom function applied to each element of the array.

Here’s a simple example, which creates an 8 by 8 board with a white rook on each square (generated by CHAR(9814)):

In our Sheet, the output is:

Before we get carried away, we use a LET function to give each of the chess piece numbers with a label.

For example, the number 9812 is labeled “wk” to indicate the White King. We do this because it makes it much easier to understand the setup and moves of the pieces within the formula.

Thus, our formula starts:

To this, we add the MAKEARRAY function as the formula expression to complete the LET formula:

Note, how we write “wr” for the white rook in the MAKEARRAY instead of the number 9814. This gives the same output as above, an 8 by 8 board with a white rook on each square.

Different Pieces for Different Squares

Within each square of the MAREARRAY, we have access to the row number and the column number.

We use these to specify different pieces using an IFS function to work through each row. Nested CHOOSE functions handle the column variations.

This outputs a grid of chess piece numbers. The last thing we need to do is convert them into chess pieces again with the CHAR function. We wrap it with an IFERROR function to handle the 0 cases too:

Note, I also snuck a comment into the formula, using the N function.

N(“some text”) equals 0, so we can happily include this without affecting the number values. 9812 + 0 is still 9812!

At this stage, we have a static chessboard set up:

Now, shall we play a game? White or black?

Moving the Pieces

The key to making the pieces move is to remember two things:

1. We have access to the row and column number of every grid square, and
2. The pieces are encoded by numbers, so we can add and subtract them.

We designate a cell outside the chessboard to be the move counter, in my case I moved the board down a few rows and use E3. Assume it’s zero in the starting position.

When we change the value of this cell to 1, we can use an IF function inside the MAKEARRAY to update the board:

If A1 has a value 1, then the IF conditional is TRUE. So the IFS statement is in play (no pun intended).

For row 3, column 1, we add a “bp” (with value 9823) to the existing 0 value from the MAKEARRAY. Then for row 2, column 1, we subtract a “bp”, i.e. subtract 9823 from the old value of 9823, so it becomes a 0. All the other grid squares are left alone.

We add subsequent moves in the same way, with the IF test checking if the move counter has incremented by 1:

Finally, we use the N trick to add comments to these formulas.

So the 2-move game formula becomes:

The following image helps illustrate these two moves:

Full Play-It-Through Chess Formula

From here, we simply add each move with a new IF statement.

Not going to lie, this took me rather a long time and was somewhat tedious. Feel free to copy the formula below or grab the template at the end of this post instead!

Adding Controls to the Chess Game

We’re into the endgame now…

All that remains is to add controls to advance the move counter or automatically advance it every minute.

The first step is to update the spreadsheet settings to make this possible:

Spreadsheet Settings

For this formula to work, you must have iterative calculations enabled and recalculation set to every minute.

Go to: File > Settings > Calculation

Make sure Recalculation is set to “On change and every minute”.

Make sure Iterative Calculations are ON and set to 1 iteration.

Automatic Play: One Move per Minute

We set the Google Sheet to recalculate every minute, and we can take advantage of this to move the game move counter along every minute.

Let’s put a checkbox in cell A3.

And this formula in cell B3:

I’m going to save the full explanation of this for a future post, but essentially, when the checkbox is unchecked, it puts the current hour and minute values into adjacent cells. The key thing is that they are then static, i.e. they don’t change.

Using a regular NOW function, which changes every minute because of the spreadsheet settings, we can calculate the difference and watch the game move number (green cell) increment each minute (GIF is sped up):

Player Controls with Checkbox

Using Tyler Robertson’s ingenious checkbox-as-a-button technique, we can increment the counter using a checkbox:

Notice the green game move number increment by 1 each time I click the checkbox.

The full formulas for both of these techniques are in the template, which can be copied below.

And that’s it!

Now we combine one of these control systems with the full LET formula above to enjoy “The Game of the Century” play out in our Google Sheet:

Play-It-Through Chess Template

🔗 Click here to open a view-only copy >>

Make your own copy: File > Make a copy

Note: If you are unable to open this file, it’s probably because it’s from an outside organization and my Google Workspace domain is not whitelisted at your organization. Ask your Google Workspace administrator about this. In the meantime, feel free to open it in an incognito window and you should be able to view it.

Also, be sure to check the spreadsheet settings as detailed in the previous section of this post above.

Other Google Sheets Projects

Check out these other wacky projects:

Google Sheets Formula Clock

Etch A Sheet In Google Sheets

How To Draw The MandelBrot Set In Google Sheets

Bullet Chart in Google Sheets