FEN notation provides the necessary info to restart a chess game from a given position. See how I created a FEN viewer in Excel!
Excel Files
1)Kevin’s FEN viewer basic, 2)Kevins-FEN-viewer-plus-v3 (database, FEN explanation).
How Does FEN Work?
Given this FEN text…
r6k/2R5/6R1/pp1Ppp2/8/Pn2B1Pr/4KP2/8 w – – 0 1
…we can create this chess position:
A FEN is split into 8 parts separated by “/”. Each part is a row on a chess board.
“r6k” describes row 8 at the top. Let’s examine each item:
- lowercase “r” = black rook (top left a8),
- number “6” = 6 consecutive blanks squares
- lowercase “k” = black King (top right h8)
“2R5” describes row 7.
- number “2” = blank squares (a7 & b7)
- uppercase “R” = white rook
- number “5” = 5 blank spaces
Near the end the “w” indicates that it’s white’s move.
Here we see each row’s FEN code:
How Does FEN Viewer work?
Robert Gascon’s ‘Excel Chess Games Viewer‘ inspired me to create this FEN viewer two weeks ago. I used 6 steps spread across 42 columns.
Unhide columns to see the formulas:
- on the ribbon select View and check Headings
- select columns N to BI
- right click & unhide
Highlevel Explanation
Steps start in column P and move to the right:
Step 1 splits FEN r6k/2R5/6R1/pp1Ppp2/8/Pn2B1Pr/4KP2/8 out per row in column P
Note: each number = consecutive blank squares. Step 6 has 8 cells representing each square in a chess row. A FEN is compact. My idea? Spread the FEN over 8 squares of a chess row.
“r6k” becomes “r666666k”. “2R5″ becomes “22R55555” (step 6 in column AX). Audit formulas in all steps to understand fully.
Final Formula
On the chess board look at cell C7 array formula:
=IFERROR(INDEX($BH$7:$BH$19,MATCH(TRUE,EXACT(AX7,$BG$7:$BG$19),0)),””)
Cell C7 looks for AX7 value “r” in column BG. The answer is a chess icon from column BH.
A couple of important parts of the formula:
- “r” is different from “R” so I used MATCH(TRUE,EXACT(
- I used IFERROR (Numbers are blank squares. There’s nothing to display.)
More About FEN
A good summary from Wikipedia:
FEN is based on a system developed by Scottish newspaper journalist David Forsyth. Forsyth’s system became popular in the 19th century; Steven J. Edwards extended it to support use by computers. FEN is an integral part of the Portable Game Notation for chess games, since FEN is used to define initial positions other than the standard one. FEN does not provide sufficient information to decide whether a draw by threefold repetition may be legally claimed or a draw offer may be accepted; for that, a different format such as Extended Position Description is needed.
More Chess in Excel!
- Excel formula calculates value of Chess pieces
- Robert Gascon’s Chess game viewer
- Diarmuid Early’s Chess game viewer
- Daniel Ferry’s Chess game viewer
- Pedro Wave’s Chess board PGN viewer
Learn Chess!
- Amazing Videos from Agadmator YouTube Chess Channel
- Play and learn chess: www.chess.com
- Study chess tactics (online or via book)
- Chess phone apps (i.e. ‘Chess Time’, ‘Shredder Chess’, ‘Chess Tactics Pro’)
Collect FENs!
FEN examples from ‘Kevin’s FEN viewer plus v3’:
- Row 16 agadmator: find the next move (Carlsen vs Anand)
Agadmator sometimes asks us to pause the video and find the best next move.
Subscribe to his YouTube channel! You will learn a lot.
- Row 17 my game: find the mate combo! (black’s move)
Use my Excel file to collect your FENs.
About Me
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
I know….two chess related Excel posts in a week is a bit too much for most. But some of us love both so why not? 🙂
Hello Kevin,
I love your ingenious creation! This is my first time to know of FEN.
Cheers,
Robert
Hi Robert,
Well…your Chessviewer inspired me to attempt this so thanks to you!
Kevin
Pingback: review of 2019 posts | My Spreadsheet Lab
Hey Kevin – here’s my solution – created the string in P1 – and the board starting at C17
Thanks – never knew about FEN
Cell P1 is =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(C1,FIND(” “,C1)-1),1,”+”),2,”++”),3,”+++”),4,”++++”),5,”+++++”),6,”++++++”),7,”+++++++”),8,”++++++++”)
Then create the board by copying:
=MID($P$1,COLUMN()-2 + (ROW()-17)*9,1)
But my objective is to create a heat map of which squares are supported and which attacked.
There must be a way without resorting to code… thanks for the inspiration.