I’m exploring switching to Excel from Google Sheets.
I was surprised to find that my data loaded into the Tiller-Money-Tracker excel template resulted in a very slow spreadsheet. Is anybody using this template for every day use?
It takes roughly 14 seconds to recalculate my spreadsheet. I’ve only got about 5000 rows in my Balance History and 2500 transactions representing two years of data across about six accounts.
I decided to look into this and found it pretty easy to find some easy optimizations.
I found that about half of the computing time was coming from the TillerBalTable sheet (which is normally hidden). I found that if I modified the formula in column B from this:
=IFERROR(INDEX(INDIRECT(‘Tiller Helper’!$J$23), MATCH(0, IF(INDIRECT(‘Tiller Helper’!$E$23)<>"",COUNTIF($B$3:B3, INDIRECT(‘Tiller Helper’!$J$23)),""), 0)),"")
=IF(LEN(B3)>0, IFERROR(INDEX(INDIRECT(‘Tiller Helper’!$J$23), MATCH(0, IF(INDIRECT(‘Tiller Helper’!$E$23)<>"",COUNTIF($B$3:B3, INDIRECT(‘Tiller Helper’!$J$23)),""), 0)),""), “”)
The idea being: skip the very expensive COUNTIF call if the prior row is empty – in other words, when there are no more account IDs to find. This row is copied down for about 200 rows, so this reduced the time by a lot. The table now computes in about 0.15 seconds on my machine, down from just over 6.
The approach used across the sheets in this template have more room for improvement, I think. I see things like:
- Heavy use of INDIRECT(), which is a volatile function in Excel and so causes recomputation of essentially everything in the sheet when anything changes.
- The COUNTIF() trick used above could possibly be much faster if newer Excel features are used, like UNIQUE() and perhaps XLOOKUP().
- Using Excel tables and structured references could make the formulas more readable and faster by eliminating the use of INDIRECT().
Taking the last example, the final formula I came up with in the TillerBalTable sheet was actually this:
=IF(LEN(B3)>0, IFERROR(INDEX(BalanceHistory[Account ID], MATCH(0, COUNTIF(B$3:B3, BalanceHistory[Account ID]), 0)),""), “”)
To do this, I had to make the data in the Balance History sheet a table, which let me reference the data in it with a readable name.