Learn Blockchains using Spreadsheets

Get the gist of crypto without going through code

The best way to learn how blockchains work is to play with them. This often requires tons of coding, but I’m hoping to spare you from all that and explain this stuff using Google Sheets.

👉 I’ve shared the Google Sheet. You’ll need it to follow along, and when you’re done you’ll have fun explaining it to your coworkers and friends.

You’ll need to click on File > Create a Copy before you can play with it.


🐓 Hashing

The first and most important thing to understand is hashing. If Blockchains contain bricks, hashing is the mortar — the magic sauce that holds everything together.

Hashing is the act of identifying data. More specifically, it’s a way to map any data to a unique, random string of digits.

Recognize this: e2762516d0decb18525bb635d3d19abf
Probably not. It’s the hash of “A few sandwiches short of a picnic”.

Let’s try this in Google Sheets. Changing a single character results in a completely arbitrary new hash:

This property makes hashing useful in a plethora of places. How can you check if a spreadsheet was modified by a coworker? Simple, hash it and compare the historical and current hashes.

“Don’t trust, verify.” — Bitcoin Proverb

There are many kinds of hashing functions. In this example we’re using the MD5 hashing function. Let’s try hash some more inputs in Google Sheets:

Hashing some input data in Google Sheets

Why are hashes so special?

Given a chicken nugget, it’s hard to recreate the chicken. Cryptographic hashes are so named because they’re infeasibly difficult (practically impossible) to reverse: you can’t reverse e2762516d0decb18525bb635d3d19abf to A few sandwiches short of a picnic.

Take-away #1: Hashes are irreversible

Anybody using the MD5 algorithm and hashing A few sandwiches short of a picnic will always get the same output of e2762516d0decb18525bb635d3d19abf

Take-away #2: Hashing functions are deterministic

We’ll soon see how the act of mining relies on finding special hashes, allowing bitcoins to be created.


🔐 What does a block look like?

A Blockchain is a ledger of transactions. These transactions are grouped into Blocks and incrementally added to the chain as time goes on. Here’s what a block looks like, give or take a few fields:

A block in our blockchain

I’ve hashed the block using the formula =MD5(CONCATENATE(A1:C13))

🤚 Take a moment to study the fields and think about why they’re necessary. The presence of the Junk field and the Miner transaction may confuse you, but they’re super special and we’ll discuss them shortly.

Notice that changing a single transaction by even a penny causes the hash to change completely:

Changing anything in our block causes our hash to change, invalidating the block!

The hash gives the block fraud protection—if a malicious actor had to modify any field within the block, the whole network would know because the hash would’ve changed.

💡Bitcoin is a peer-to-peer (p2p) network — anyone running the Bitcoin software has an up-to-date copy of this ledger on their computer. Bitcoin is just a collection of rules. Anyone can write their own Bitcoin software and participate in the network as long as they play by the rules.
When people talk about the Bitcoin software, they’re likely referring to the reference implementation of these rules — the code hosted at https://github.com/bitcoin/bitcoin.

⛏ Mining

If you studied the valid block carefully you’d notice something strange about the hash: it starts with six zeros! Which is weird because the first thing we learnt was that hashes are random. So what’s the probability of hashing something that starts with 6 zeros? About 1 in 2²⁴ = 16,777,216 or extremely unlikely.

This is the rule for determining if blocks are valid—if they start with a certain number of zeros (I chose 6 for this example).

To drive this concept home, try getting the block’s hash to begin with a single 0. It should take you on average 16 attempts.

Finding a hash beginning with a single 0 takes on average 16 attempts

💼 The job of a miner

A miner is someone on the network interested in creating new bitcoins by forming a new block.

This is where the Junk field comes in. A miner sets a random Junk value, hashes the block and checks if it begins with zeros. If it doesn’t, they insert another Junk value and try again, and again, until they’re successful.

💡 Heard of mining hardware? This is what mining hardware does. Because it’s specialized, it can perform the hashing faster than a regular computer.

As a reward for doing this work the miner receives some bitcoin as the first transaction in a block. This reward is how new bitcoins are created and it decreases over time.

💡 The reward halves every 210,000 blocks. At time of writing it is 12.5 bitcoins but will halve to 6.25 bitcoins in 2020, and eventually become zero in 2140. This is how the Bitcoin supply is controlled—there will only ever be 21 million bitcoin. Here’s the Bitcoin wiki describing the supply.

The number of zeros the hash begins with is called the difficulty and it changes frequently because computers get faster each year. Bitcoin aims to create a new block roughly every 10 minutes, after every 2016 blocks the difficulty is re-evaluated.

🏋️‍♀️ Proof of Work

The algorithm described above—the act of setting a junk value and determining if the block hash begins with zeros is called Proof of Work: the junk value is the proof that the miner did the work!

Given a block, it’s trivial for anyone on the network to compute the hash and verify that it begins with zeros. The genius here is that Proof of Work is something that is extremely difficult to compute but easy to verify.

Once a miner has found a valid junk value they broadcast the block, causing everyone to check the block does indeed begin with the required number of zeroes. If everyone adds the block to their blockchain. We say that the network has now reached consensus on the new block.

💡 In Bitcoin, the junk value is called a nonce (think of nonsense) and when miners have guessed every nonce without success they permute transactions and add additional data to the block to continue guessing.

💎 The immutability of the blockchain

As we’ve learnt, Bitcoin is just a distributed ledger with a procedure by which people on the network decide if a new block is valid by verifying the hash.

When a block is hashed, it contains the previous block’s hash in the Previous Hash field. This is why it’s called a “chain”. This may seem superficial but it’s what gives the blockchain immutability—a single change anywhere invalidates the hashes of all the subsequent blocks:

A single change anywhere invalidates the subsequent blocks

This is what makes Bitcoin so resilient. If a malicious actor fraudulently created a transaction to himself he’d have to re-calculate all the subsequent hashes (extremely hard) and convince the network to accept these new blocks.

At any given time there are many valid chains on the network. Eventually clients reach consensus and all share the same one. Bitcoin’s rule is that clients accept the chain with the most amount of work (think of those zeros) which is usually the longest chain they can see. This is why it’s recommended to wait for a few blocks to pass before you consider your transaction “buried” under enough blocks to call it “committed”.

💡 Curious to see what the actual Bitcoin blockchain looks like? Take a look at BTC.com’s Block Explorer.

👨‍🏫 What I didn’t explain

Most of what I didn’t explain has to do with transactions. How they‘re created and transmitted requires an understanding of cryptography. I’ll try paint with broad strokes in the hope you can fill in the missing pieces with your own research:

  • Transactions—they’re digitally signed by the person that created them. Understanding this process involves a fair amount of cryptography knowledge.
  • Wallets—software that keeps track of transactions sent to your public key (think address). Most wallets are Bitcoin nodes themselves.

🙏 Special thanks to Justin Moon for proof-reading this. Follow Justin on Twitter: @_justinmoon_