Tidy formula bar, tidy mind.
10 May 2023
I use Google Sheets a lot. Here are five rules I like to follow to make my spreadsheets extra awesome. They should serve you pretty well in Microsoft Excel too!
I keep any raw data I’m using on a separate sheet in my workbook, and refer to it from other sheets as required. This means I don’t accidentally overwrite the original data when trying to manipulate it. It also gives me one place to drop in any new data I get.
This is super helpful for constraining cells to certain values and for catching typos. For easier maintenance I put any lists I’m using for drop-downs on a separate sheet and reference them, rather than hardcoding them into the validation rule.
I use colours as a quick way to flag different types of cells in my workbook, e.g. black border and bright yellow background for places to enter input variables. This makes my models more user-friendly, both for me and for anyone else who comes along after me.
I’m as fond of the VLOOKUP function as the next person, but the INDEX/MATCH combo is more powerful and more robust to changes in your data structure. I’ve found it a good habit to get into, it just takes a little practice.1
I do this pretty early on, and then add them back in as required. Having acres of empty cells below and / or to the right of your work is practically an invitation to leave ad-hoc calculations lying around to cause trouble later…
Good news, XLOOKUP has you covered now! But rockstars still use INDEX/MATCH ;) ↩