Rubenerd

Back in February I talked about how I mentally map things, and admitted I was using spreadsheets more. A few of you asked if I could provide some examples, so that’s what I’m (finally!) getting to here. I use LibreOffice, but other graphical spreadsheets have equivalent functionality.

Blog post: how do you mentally map things

In summary, I tend to think of things in terms of matrices. Turns out, a spreadsheet is a giant, beautiful, flexible canvas upon which to build them! I can can fill out, sort, filter, and search for things using this glorified database, and change things easily.

Step 1: What do I want to track?

I always start with a list of things and write them down, which tends to uncover what attributes I care about. Subheadings, or “collections” of things become the first column.

For example, say I’m making a sheet to track various computer builds. In a text file or outline, I might have created a subheading for “Game Machine” or “Commodore 128”. I move these subheadings into the first column. These may be duplicated as I add more parts to each build, such as RAM and an SSD for a “Game Machine”.

In database terms, I tend to think of the first two columns together as the primary key, but its not strictly necessary. The point here is to have a flexible list of attributes, not to build a normalised database.

I used to track URLs in a separate column, but thesedays I hit CTRL+K (or CMD+K on Mac) which lets me define a link that will open in my default browser. You can see these blue links in the parts column.

Step 2: Make it pretty

Pretty is a feature,™ so the next step is to make the first row bold and set a colour. My personal database workbook uses colours to quickly distinguish the type of stuff I’m tracking: travel is yellow, tech is green, and mental stuff is blue. You might have better taste.

I’ll also freeze the first row by going to View → Freeze Cells → Freeze First Row. This means your row of attributes will always be visible, even if your sheet starts getting long.

Step 3: Set an AutoFilter

The final step is to select the first row, then choose Data → AutoFilter. This is where a silly spreadsheet (that’s barely more functional than a text file) suddenly becomes a powerful tool of wonderfulness.

AutoFilter adds a dropdown box to every attribute, which you can click and use to sort the table, or hide things you’re not interested in. For example, if I’m only after parts for my Commodore 128, I can click the Build column, and untick everything else.

From one sheet, I can now sort by price across different builds, which might help with budgeting. I can sort by store, so I can buy parts from multiple builds in the one transaction to save shipping costs. This is the inherent power of matrices, and something an outline or text file can’t do easily.

Conclusion

In the words of IshKebab on Reddit, there’s nothing special or even notable here really. Regular spreadsheet users likely do this all the time, and find my joy at using this system suspect.

But I’ve found this basic combination of frozen rows, AutoFilters, and tabs for tracking things to be a huge life upgrade from the text files I used before. It’s become a knowledge base for everything I care about, and it’s awesome.