Learn Blockchains using Spreadsheets
Understand the concepts behind crypto with zero 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—you don’t need to be a programmer to understand how Blockchains work after all.
👉 I’ve shared a special 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 Copybefore you can play with it.
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 really just the act of identifying data. It’s a way to assign a unique, random string of digitals to any data—a sentence, photograph, spreadsheet, or installed program.
Let’s drive this home with a quick example. Here’s an image of Cape Town:
The hash of this image is
Now, I decide to modify the image—an extremely small modification—can you spot the difference? Maybe not, but a computer definitely can.
The hash of this image is
f4c56f530133b8de6b3b0b39a610be32. It’s completely different to the hash of the original, letting us know that the image has been modified. In fact, even if we changed a single pixel out of trillions of pixels, the hash would be completely different.
PS. I removed a small wisp of cloud.
Let’s focus on doing hashing in Google Sheets. The Google Sheet I’ve shared with you is special—it contains the MD5 hashing algorithm built in. Open it up and let’s try hashing some data.
Remember, changing a single character anywhere will result 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:
✋ Does this make sense? It’s worth pausing here to make sure you get the gist of this—take some time to play around with different inputs in the spreadsheet I’ve created until you get the point.
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
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
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:
I’ve hashed the block using the formula
🤚 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:
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.
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.
💼 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:
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.