Are there any data points for how many transactions and balance history records start to cause problems? I’m at 24k transactions and 32k balance history records. Is there a known limit other than Google’s 10m total cells?
I think the total number of cells is less important than what you are doing with them. Having conditional formatting on an increasingly large number of rows will cause problems. Having formulas that do lookups on an increasingly large number of rows will become problematic as well. 1 way to deal with this is to ‘archive’ transactions and histories to separate sheets, so formulas aren’t acting on them anymore, but you can always get access to the data if needed.
2 Likes
There is no hard and fast rule @lions04 … having a lot of items to load in a web platform will cause it to slow. Internet speed and computer processor speed will obviously be a variable. I proudly have pushed many boundaries in Sheets and Slides to cause them to slow down.
Do you start a new spreadsheet each year or are you rolling over? There are benefits to both methods. However, at some point you’re going to want to archive past transactions. Make a copy of your spreadsheet and name the copy archive. Then can delete the transactions and balance history records that you no longer need in your spreadsheet.
-Alice
Tiller Evangelist
This is generally when you start to see issues. Installing a lot of complex dashboards/templates/visualizations shared here in the community can cause the large dataset to run into issues, most specifically with Tiller Money Feeds filling the data. Note that the templates does not have to look complicated for the formulas to be complicated. A really simple template could have some really complex formulas that are querying the entire large dataset and that will slow things down.
Here’s our guide on how to archive: Accessing Tiller Google Sheets on Your Google Drive | Tiller Help Center
Here’s our guide on getting your sheet ready for the new year, which is a good time to think about archiving data: How do I get my budget ready for the new year? | Tiller Help Center
I would recommend only keeping data back through the previous year so you can do YOY analysis. Archive any years before that. Also, make sure you don’t have any blank rows at the bottom of your Transactions sheet. Discovered early on this will bog your workbook down big time. Scroll all the way to the bottom and delete all empty rows.
3 Likes