The Nonlinear Library LW - Level up your spreadsheeting by angelinahli
May 25, 2024
06:03
Welcome to The Nonlinear Library, where we use Text-to-Speech software to convert the best writing from the Rationalist and EA communities into audio. This is: Level up your spreadsheeting, published by angelinahli on May 25, 2024 on LessWrong.
Epistemic status: Passion project / domain I'm pretty opinionated about, just for fun.
In this post, I walk through some principles I think good spreadsheets abide by, and then in the companion piece, I walk through a whole bunch of tricks I've found valuable.
Who am I?
I've spent a big chunk of my (short) professional career so far getting good at Excel and Google Sheets.[1] As such, I've accumulated a bunch of opinions on this topic.
Who should read this?
This is not a guide to learning how to start using spreadsheets at all. I think you will get more out of this post if you use spreadsheets at least somewhat frequently, e.g.
Have made 20+ spreadsheets
Know how to use basic formulas like sum, if, countif, round
Know some fancier formulas like left/mid/right, concatenate, hyperlink
Have used some things like filters, conditional formatting, data validation
Principles of good spreadsheets
Broadly speaking, I think good spreadsheets follow some core principles (non-exhaustive list).
I think the below is a combination of good data visualization (or just communication) advice, systems design, and programming design (spreadsheets combine the code and the output).
It should be easy for you to extract insights from your data
1. A core goal you might have with spreadsheets is quickly calculating something based on your data. A bunch of tools below are aimed at improving functionality, allowing you to more quickly grab the data you want.
Your spreadsheet should be beautiful and easy to read
1. Sometimes, spreadsheets look like the following example.
2. I claim that this is not beautiful or easy for your users to follow what is going on. I think there are cheap techniques you can use to improve the readability of your data.
There should be one source of truth for your data
1. One common pitfall when designing spreadsheet-based trackers is hard copy and pasting data from one sheet to another, such that when your source data changes, the sheets you use for analyses no longer reflect "fresh" data. This is a big way in which your spreadsheet systems can break down.
2. A bunch of tools below are designed to improve data portability - i.e. remove the need for copy and pasting.
Your spreadsheet should be easy to audit
1. One major downside of spreadsheets as compared to most coding languages, is that it's often easy for relatively simple spreadsheets to contain silent bugs in them.[2]
2. Some features of spreadsheets that contribute to this problem:
1. Spreadsheets hide the code and show you only the output by default.
1. When you use formulas, once you hit enter, the user doesn't by default get to read what's going on. So if the output looks plausible, you might not notice your formula has a bug in it.
2. It's harder to break up your work into chunks.
1. When you're coding, most people will break up a complicated formula into several lines of code, using intermediate variables and comments to make things more readable. E.g.:
2.
3. By default, some Sheets formulas get really unwieldy, and you need to work a bit harder to recover readability.
3. Spreadsheets contain more individual calculations.
1. When you're coding and you want to perform the same calculation on 100 rows of data, you'd probably use a single line of code to iterate over your data (e.g. a for loop).
2. In Google Sheets, you're more likely to drag your formula down across all of your rows. But this means that if you accidentally change the formula for one cell and not the others, or if your data has now changed and it turns out you need to drag your formulas down more, things can break in annoying ways.
3. Because of this, I consider auditability one of the key qualities of a well designed spreadsheet. Some of the tools below will rec...
Epistemic status: Passion project / domain I'm pretty opinionated about, just for fun.
In this post, I walk through some principles I think good spreadsheets abide by, and then in the companion piece, I walk through a whole bunch of tricks I've found valuable.
Who am I?
I've spent a big chunk of my (short) professional career so far getting good at Excel and Google Sheets.[1] As such, I've accumulated a bunch of opinions on this topic.
Who should read this?
This is not a guide to learning how to start using spreadsheets at all. I think you will get more out of this post if you use spreadsheets at least somewhat frequently, e.g.
Have made 20+ spreadsheets
Know how to use basic formulas like sum, if, countif, round
Know some fancier formulas like left/mid/right, concatenate, hyperlink
Have used some things like filters, conditional formatting, data validation
Principles of good spreadsheets
Broadly speaking, I think good spreadsheets follow some core principles (non-exhaustive list).
I think the below is a combination of good data visualization (or just communication) advice, systems design, and programming design (spreadsheets combine the code and the output).
It should be easy for you to extract insights from your data
1. A core goal you might have with spreadsheets is quickly calculating something based on your data. A bunch of tools below are aimed at improving functionality, allowing you to more quickly grab the data you want.
Your spreadsheet should be beautiful and easy to read
1. Sometimes, spreadsheets look like the following example.
2. I claim that this is not beautiful or easy for your users to follow what is going on. I think there are cheap techniques you can use to improve the readability of your data.
There should be one source of truth for your data
1. One common pitfall when designing spreadsheet-based trackers is hard copy and pasting data from one sheet to another, such that when your source data changes, the sheets you use for analyses no longer reflect "fresh" data. This is a big way in which your spreadsheet systems can break down.
2. A bunch of tools below are designed to improve data portability - i.e. remove the need for copy and pasting.
Your spreadsheet should be easy to audit
1. One major downside of spreadsheets as compared to most coding languages, is that it's often easy for relatively simple spreadsheets to contain silent bugs in them.[2]
2. Some features of spreadsheets that contribute to this problem:
1. Spreadsheets hide the code and show you only the output by default.
1. When you use formulas, once you hit enter, the user doesn't by default get to read what's going on. So if the output looks plausible, you might not notice your formula has a bug in it.
2. It's harder to break up your work into chunks.
1. When you're coding, most people will break up a complicated formula into several lines of code, using intermediate variables and comments to make things more readable. E.g.:
2.
3. By default, some Sheets formulas get really unwieldy, and you need to work a bit harder to recover readability.
3. Spreadsheets contain more individual calculations.
1. When you're coding and you want to perform the same calculation on 100 rows of data, you'd probably use a single line of code to iterate over your data (e.g. a for loop).
2. In Google Sheets, you're more likely to drag your formula down across all of your rows. But this means that if you accidentally change the formula for one cell and not the others, or if your data has now changed and it turns out you need to drag your formulas down more, things can break in annoying ways.
3. Because of this, I consider auditability one of the key qualities of a well designed spreadsheet. Some of the tools below will rec...