Make a copy of your “Transactions” sheet, called e.g. “Transactions.old”.
Remove older transactions from “Transactions” and newer transactions from “Transactions.old” in such a way that the union of the rows in the two sheets is all transactions without duplicates. For me, I kept all transaction through 2023-12-31 (73K rows) in “Transactions.old” and all 2024 transactions in “Transactions”.
Add a new sheet called e.g. “Transactions.all” and in cell A1 insert this formula:
={ Transactions!A1.Q ; Transactions.old!A1.Q }
This will show all rows of “Transactions” followed by all rows of “Transactions.old”.
If you have any analysis that relies on the “Transactions” tab contents, change all instances of “Transactions” with “Transactions.old”. Usually there are only a handful of changes to make (I use “Category Tracker”, “Categories by Month”, and “Spending Trends”, each of each is easy to adjust).
Now you have a slim and fast “Transactions” tab, a place to see all of your transactions going back years, and analyses that work correctly. Hope this helps.
New transactions always get added to the bottom, that option is just for sorting afterwards, which will either leave them at the bottom, or sort them to the top.
Auto Sort Override
When new transactions are added to your Transactions sheet by the Tiller Money Feeds add-on they are added to the bottom and then sorted to the top automatically. This override setting, when it’s turned on, will leave these transactions at the bottom. This is a great feature for folks who want to use Tiller with another service like Zapier and/or Airtable where the tool expects the new rows to be added to the bottom and stay at the bottom.
I’m not sure whether inserting at the top or bottom is faster, but one thing I noticed is adding a computed column to the Transactions tab can make fills dramatically slower. I’ve added a column that computes for each row the average dollars spent per day per category up until that day (quadratic scaling) and it makes fills time out even if there aren’t very many rows.
Ah, okay, interesting. I wondered if there might be some calculation needed to determine the bottom transaction row before appending and how fast that might be with tens of thousands of transactions
I wonder if it’s really the overall spreadsheet calculation load that matters and not that it occurs specifically on the Transaction sheet? Like do that calculation on a “TransactionAll” sheet that will change with a fill and remove/disable it on the Transaction sheet.
From what I can tell it’s a combined effect. Calculations on other sheets that depend on “Transactions” also impact update speed, but for some reason calculations done on the “Transactions” sheet itself have a greater impact. But… I speculate.
