Five rules for better sheets

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!

1. Make a data tab

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.

2. Use data validation

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.

3. Create a legend

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.

4. Use INDEX/MATCH for lookups

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.¹

5. Delete empty rows and columns

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 ;) ↩